Azure-docs: Managed Identity with Azure SQL Managed Instance ?

[bertrandpons]

Hi,

I want to setup managed identity for my azure web app with an azure sql managed instance to avoid using credentials in my connection string.

I have 2 questions:

• Does managed identity work with Azure SQL Managed Instance ? I followed this tutorial and it doesn't seem to work (I get this error: "Can not perform requested operation on nested resource. Parent resource not found.") where as with an Azure SQL Database, it works.

• As I use .NET EntityFramework for the connection string, how can I combine it with managed identity ?

Thank you!

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 726adac9-1848-01a6-9586-842c81f820f7
• Version Independent ID: 249db438-8c95-8e69-f69e-8e48fcd0b359
• Content: Azure Services that support managed identities for Azure resources
• Content Source: articles/active-directory/managed-identities-azure-resources/services-support-msi.md
• Service: active-directory
• GitHub Login: @priyamohanram
• Microsoft Alias: priyamo

[Mike-Ubezzi-MSFT]

@bertrandpons This is unsupported, as the SQL instance needs to be deployed to an Azure VM: Tutorial: Use a Windows VM system-assigned managed identity to access Azure SQL

Please see this document that provides an overview of Managed Identity and the services that are currently supported: What is managed identities for Azure resources?

[Mike-Ubezzi-MSFT]

This document details the supported authentication methods: Access management

[bertrandpons]

@Mike-Ubezzi-MSFT Thank you for your replies. Do you mean that connection string formatted by Entity Framework is no supported for Managed Identity ? (as an example: metadata=res:///Models.FooBarContext.csdl|res:///Models.FooBarContext.ssdl|res://*/Models.FooBarContext.msl;provider=System.Data.SqlClient;provider connection string="Server=tcp:fooserver.database.windows.net,1433;Database=foobar;User ID=myname@fooserver;Password=xxxxxxxxxx;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")

[Mike-Ubezzi-MSFT]

@bertrandpons It is supported. The challenge is getting communication to the Managed Instance through all the virtual networking infrastructure that Managed Instance is deployed with. Here is a great blog/tutorial to show you how Entity Framework with a Managed Identity is configured: Azure AD Authentication with Azure SQL, Entity Framework and Dependency Injection. In this case the Managed Identity is ADKApp.

[bertrandpons]

@Mike-Ubezzi-MSFT Thank you for the reply. I finally succeeded in combining Entity Framework with Managed Identity.
I have another question about Managed Identity, but not on the same topic: I have opened another ticket here

[CarlRabeler]

@VanMSFT for awareness and evaluation re: doc updates

[Mike-Ubezzi-MSFT]

Customer is currently stuck with this scenario:

I am using one single common database for the 2 web apps. I just want that the managed identity for each web app be approved by the SQL server to allow the web apps to connect to the database in the same time with their own managed identity.
Currently if I link the database server to one web app’s managed identity, the access to the DB from the second web app is dropped

[Mike-Ubezzi-MSFT]

@bertrandpons The App Service Environment does not have access to a .DLL that is necessary for AD Identities to work. In the following doc, it details how to set-up the client computers and in an App Service Environment, you don't have the ability to install additional or separate library items outside the target framework version runtime.

The following are required:

• .NET Framework 4.6 or later from https://msdn.microsoft.com/library/5a4x27ek.aspx.
• Azure Active Directory Authentication Library for SQL Server (ADALSQL.DLL) is available in multiple languages (both x86 and amd64) from the download center at Microsoft Active Directory Authentication Library for Microsoft SQL Server.

[Mike-Ubezzi-MSFT]

Additionally, it appears you might be using a Managed Identity as the SQL AD Admin...is this the case? Can you follow this section of the documentation to create a Managed Identity as a USER to the Application Database. You should provision a specific Azure AD Admin identity as the SQL AD Admin for your instance. My previouse comment is with regard to the basic requirements for the client to connect. If the App Service Environment allows access to these libraries, and you have established the Managed Identities as contained database users (without a login in master) then you will be able to run both App Service services concurrently.

[Mike-Ubezzi-MSFT]

Here is a tutorial that walks you through the process from an App Service perspective. Managed Identities need to be enabled within the App Service instance: Tutorial: Secure Azure SQL Database connection from App Service using a managed identity (link).
So yes, Managed Identities are supported in App Service but you need to add the identities as contained users scoped to a specific database. These would be added while logged in as the provisioned Azure AD SQL Admin for the instance.

[molinch]

@Mike-Ubezzi-MSFT From what I understand it is possible to use managed identities and Azure Sql Managed Instance. Right?
If so, could you guide us through the required steps?

[Mike-Ubezzi-MSFT]

@molinch This is correct, by way of of AAD Server Principles (logins) and Users. Please see the main Azure SQL Database + Azure AD Authentication overview: Use Azure Active Directory authentication with a specific section for SQL Managed Instance. The process starts with creating an SQL Database AD Admin for your Managed Instance. (Note: This task can be completed either through the portal or via PowerShell).

After provisioning an Azure AD admin for your SQL Managed Instance, you can begin to create Azure AD server principals (logins) with the CREATE LOGIN syntax. For more information, see SQL Managed Instance overview.

More information on managed identities and to view the service principal of a managed identity in the Azure portal (link). Once you set-up you service principle and can connect with it via SSMS, you can set-up the Azure App Service to use the Managed Identity connected to the service principle(s) needed to run your web application.

Finally, here is an Azure AD Service Principal authentication to SQL DB - Code Sample (TechCommunity Blog Link).