Thursday, March 17, 2022

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:

Lancer_Xue_0-1647515783468.png

Below is the difference in details:

 

.NET framework

NET Core

System.Data.Sqlclient

Lancer_Xue_0-1647515833743.png

 

 

Lancer_Xue_1-1647515833745.png

 

 

Microsoft.Data.Sqlclient

Lancer_Xue_2-1647515833746.png

 

 

Lancer_Xue_3-1647515833748.png

Lancer_Xue_5-1647515985541.png

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:

Lancer_Xue_7-1647516117876.png

 

Approach #1 (system-assigned identity)

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

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

    Lancer_Xue_10-1647516286032.png

  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.

    Lancer_Xue_11-1647516369184.pngLancer_Xue_12-1647516397470.png

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

    Lancer_Xue_15-1647516676170.png

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

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

    Lancer_Xue_17-1647516794625.png

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

    Lancer_Xue_18-1647516859595.pngLancer_Xue_19-1647516890330.png
  4. Below is the connection string for user-assigned managed identity

    Lancer_Xue_20-1647516932429.pngLancer_Xue_22-1647516977404.png

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

Lancer_Xue_23-1647517025679.png

 

Have fun!

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