Azure SQL authentication using Azure Active Directory

Question

Connection works fine following this tutorial when using:

var connection = (SqlConnection)Database.GetDbConnection();
connection.AccessToken = (new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;

But now the docs say "Microsoft.Azure.Services.AppAuthentication is no longer recommended"

So changing my connection as described in Using Azure Active Directory authentication with SqlClient I get the following errors:

Using Active Directory Integrated authentication

Integrated Windows Auth is not supported for managed users.

Using Active Directory Managed Identity authentication

Tried to get token using Managed Identity. Access token could not be acquired. A socket operation was attempted to an unreachable network. (169.254.169.254:80)

Nothing is blocking that address, but also where is it getting that IP from? The tutorial's code used https://database.windows.net/ to get the token (which resolves 65.55.23.107).

Can/should I override that address somewhere?
Any other config missing?

If you used azure sql server, I think you need to check if you've added ip in the server firewall(azure portal go to your database->click Set server firewall). Another point is to check if you've set the ad admin of sql server — tiny-wa
Doubt it's a firewall issue if connection works from SSMS and using the tutorial's code — JvR

Joy Wang-MSFT Joy Wang-MSFT · Accepted Answer · 2021-03-22T02:43:04

These auth ways apply to different scenarios, for example, if you want to use Active Directory Integrated authentication, you need to federate the on-premises AD with Azure AD via ADFS, if you want to use Active Directory Managed Identity authentication, you must run your code in an Azure service which supports MSI(need to enable MSI first), because the code essentially makes an API call to the azure instance metadata endpoint to get the access token, then use the token to auth, it is just available in the MSI-supported service.

So if you want to migrate the code from the old sdk to the new one, you need to choose the correct auth way that applies to your scenario. Here I recommend you to use the Active Directory Service Principal authentication, it can apply to any scenario, please follow the steps below.

1.Register an application with Azure AD and create a service principal.

2.Get values for signing in and create a new application secret.

3.Grant the permission to the service principal with CREATE USER [Azure_AD_Object] FROM EXTERNAL PROVIDER.

4.Then use the code here, fix the values with yours and got from step 2.

string ConnectionString = @"Server=demo.database.windows.net; Authentication=Active Directory Service Principal; Database=testdb; User Id=AppId; Password=secret";

using (SqlConnection conn = new SqlConnection(ConnectionString)) {
    conn.Open();
}

Azure SQL authentication using Azure Active Directory

1 Answers