Connect to Azure SQL DB & Managed Instance with AAD managed identity from ADO.NET app

In the past, the main namespace for connecting SQL server is System.Data.Sqlclient for both .NET framework app and.NET Core app. However, when it comes to AAD authentication, it makes the thing different., in which we need to specify the authentication mode in the connection string as there are few AAD authentication options. In order to achieve the goal above, a new namespace, Microsoft.Data.SqlClient, is introduced when using over .NET Framework 4.7.2 and over .NET Core 2.2.

Below is the sample connection string that shows the key difference:

Below is the difference in details:

	.NET framework	NET Core
System.Data.Sqlclient
Microsoft.Data.Sqlclient

Please check the link for more details:

https://docs.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver15

 

Mainly there are two approaches to connect to Azure SQL/SQL managed instance:

1. Specify the authentication with SqlAuthenticationMethod.ActiveDirectoryMSI/SqlAuthenticationMethod.ActiveDirectoryManagedIdentiy
2. Explicitly call Azure Instance Metadata Service (IMDS) using http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https://database.windows.net/

Important: no matter which approach is used, AAD MSI authentication always requests that the authentication request is sent out from Azure services where managed identity is enabled. It is not supported to use AAD MSI from local environment directly. Below is the error you will get if you try with AAD MSI in local environment:

 

Approach #1 (system-assigned identity)

==========================

1. make sure the identity of the Azure VM is enable

   

2. Once it is on, you need to create the user for this VM in the Azure SQL database that the app needs to access to and grant the proper permission for the user.

   

Attention: If you are using user-assigned identity, it is required to specify user ID in the connection string. Also, please make sure that client ID of the managed identity is used, not object ID when Microsoft.Data.SqlClient v3.0 onwards is used.

 

Approach #1 (user-assigned identity)

==========================

1. create a managed identity on portal

   

2. Assign an user-assigned managed identity to the VM

   

3. Create user in Azure SQL for user-assigned managed identity, you may get the following error when creating the user in normal way

   

   To work around this issue, you may try with the following statement

   

4. Below is the connection string for user-assigned managed identity

   

Approach #2

=============

$response = Invoke-WebRequest -Uri 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fdatabase.windows.net%2F' -Method GET -Headers @{Metadata="true"}

$content = $response.Content | ConvertFrom-Json

$AccessToken = $content.access_token

 

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = "Data Source = lancersqldb-ea.database.windows.net; Initial Catalog = RocketProgramFY21"

$SqlConnection.AccessToken = $AccessToken

$SqlConnection.Open()

 

$sqlcmd = $SqlConnection.CreateCommand()

$query = "SELECT name, collation_name FROM sys.databases"

$sqlcmd.CommandText = $query

$adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd

$data = New-Object System.Data.DataSet

$adp.Fill($data) | out-null

$data.Tables

 

Have fun!

Posted at https://sl.advdat.com/3JjZzehhttps://sl.advdat.com/3JjZzeh