Connecting to Azure SQL from App Service using AAD identity

Question

An on-premise web application (.net 4.6.1) which uses Windows Authentication for its users and then AD Integrated Authentication to connect to a MS SQL database is been migrated to Azure.

Ideally, the existing security mechanism of granting permissions to AD users on database objects and let the DB be security source of the application should be kept.

A copy of the SQL database has already been created and configured.

If I run the web application locally but using the new Azure database everything works as expected.

If I run this command I get the expected AD user:

SELECT CURRENT_USER;

Now, when I deploy exactly the same application to an Azure App Service and enable Azure Active Directory Authentication and Managed Service Identity, the user is authenticated correctly on the Web application but it is not able to connect to the SQL database and the following error is returned:

Errors = System.Data.SqlClient.SqlErrorCollection ClientConnectionId = e9f0c48a-3159-465c-ab72-c1da99761b8f Class = 14 LineNumber = 65536 Number = 18456 Procedure = Server = xxxxxxxxxxx.tr4.canadacentral1-a.worker.database.windows.net,11057 State = 1 Source = .Net SqlClient Data Provider ErrorCode = -2146232060 Message = Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

If I disable Managed Service Identity, I get this error instead:

InnerExceptions = System.Collections.ObjectModel.ReadOnlyCollection`1[System.Exception] Message = One or more errors occurred. Data = System.Collections.ListDictionaryInternal InnerException = Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProviderException: Parameters: Connection String: [No connection string specified], Resource: https://database.windows.net/, Authority: . Exception Message: Tried the following 4 methods to get an access token, but none of them worked. Parameters: Connection String: [No connection string specified], Resource: https://database.windows.net/, Authority: . Exception Message: Tried to get token using Managed Service Identity. Unable to connect to the Managed Service Identity (MSI) endpoint. Please check that you are running on an Azure resource that has MSI setup. Parameters: Connection String: [No connection string specified], Resource: https://database.windows.net/, Authority: . Exception Message: Tried to get token using Visual Studio. Access token could not be acquired. Visual Studio Token provider file not found at "D:\local\LocalAppData.IdentityService\AzureServiceAuth\tokenprovider.json" Parameters: Connection String: [No connection string specified], Resource: https://database.windows.net/, Authority: . Exception Message: Tried to get token using Azure CLI. Access token could not be acquired. 'az' is not recognized as an internal or external command, operable program or batch file. Parameters: Connection String: [No connection string specified], Resource: https://database.windows.net/, Authority: https://login.microsoftonline.com/common. Exception Message: Tried to get token using Active Directory Integrated Authentication. Access token could not be acquired. get_user_name_failed: Failed to get user nameInner Exception : The format of the specified domain name is invalidat Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider.d__14.MoveNext()

Is it what I'm trying to do possible? If yes, what am I missing? Any thoughts will be appreciated.

For reference, this is the method returning the SQL connection:

private SqlConnection GetSqlConnection()
{
    var accessToken = new AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/").Result;

    return new SqlConnection
    {
        ConnectionString = WebConfigurationManager.ConnectionStrings["Default"].ConnectionString,
        AccessToken = accessToken
    };
}

... and this is the connection string:

  <connectionStrings>
    <add name="Default" connectionString="Data Source=myserver.database.windows.net;Initial Catalog=MyDB;" providerName="System.Data.SqlClient" />
  </connectionStrings>

Note: The local AD is been synchronized with its Azure AD counterpart.

Please try the steps provided on this article: colinsalmcorner.com/post/… — Alberto Morillo
AAD is enabled for the database already. My question is, once I connect to SQL and run "SELECT CURRENT_USER", am I going to get the App Service user or the original AD credentials for the user? — JCallico
Any process now? If you still have any problem, please feel free to let me know. — Joey Cai

donatasj87 donatasj87 · Accepted Answer · 2019-09-03T13:18:08

I think to solve your issue you have to do the following:

Grant access for the user as external provider in the database: (you can also find in AAD, under Enterprise applications when you select All applications). Important to make sure, that app service identity is already created at this moment.
- For a web app it is the name of the web app
- If you are using deployment slots the sql user name is {application-name}/slots/{slot-name}
- If you are using User managed identity, you also have to specify app setting AzureServicesAuthConnectionString with the following value: RunAs=App;AppId={ClientId of identity}
Change your connection string and web config to use the interactive authentication.
- Connection string can be something like this: Server=tcp:{server}.database.windows.net,1433;Initial Catalog={DatabaseName};Persist Security Info=False;MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=False;Authentication='Active Directory Interactive'.
  - This also works with EntityFramework, but if you set up connection string directly in Azure you need to choose Custom instead of SqlAzure as a provider.
- In web.config add this

<SqlAuthenticationProviders>
   <providers>
      <add name="Active Directory Interactive" type="Microsoft.Azure.Services.AppAuthentication.SqlAppAuthenticationProvider, Microsoft.Azure.Services.AppAuthentication" />
   </providers>
</SqlAuthenticationProviders>

Connecting to Azure SQL from App Service using AAD identity

3 Answers