Can not connect to Azure SQL Server using Active directory integrated authentication in AppService

Question

We have web application deploy on Azure App Service. Our database is also on Azure which is configured to use AAD authentication (We have assigned AAD Admin).

We are using below connection string in web app to connect to this server and database using below connections string.

Data Source=xxxxxxx.database.windows.net;Initial Catalog=xxxxxxx;Persist Security Info=False;Authentication=Active Directory Integrated

Please note: This connection string is working fine when using thru local system. But getting below error when we use this conn string in Azure App Service:

Failed to authenticate the user NT Authority\Anonymous Logon in Active Directory (Authentication=ActiveDirectoryIntegrated). Error code 0x4BC; state 10 The format of the specified domain name is invalid

Answer 1

According to your description, I found you used the Active Directory integrated authentication.

To use integrated Windows authentication, your domain’s Active Directory must be federated with Azure Active Directory. Your client application (or a service) connecting to the database must be running on a domain-joined machine under a user’s domain credentials

If you published the web app to Azure, Azure's web app server will not be in your domain’s Active Directory. So the SQL server will not pass the auth.

I suggest you could try to use Active Directory password authentication instead of the Active Directory integrated authentication.

Replace the connection string as below use azure AD user name and password. It will work well.

Server=tcp:brandotest.database.windows.net,1433;Initial Catalog=bradnotestsql;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Password";

Answer 2

Since the accepted answers are a bit dated, if you are out here in 2020 or later, the correct way for setting up integrated authentication is as follows:

(excerpted from here, the asp.net standard implementation)

https://docs.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi

1. add the Microsoft.Azure.Services.AppAuthentication nuget package.

2. modify your web.config by adding: (in configSections)

    <section name="SqlAuthenticationProviders" type="System.Data.SqlClient.SqlAuthenticationProviderConfigurationSection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

(and then)

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

It's important to pay attention to the name you use there. Then... your connection string will look like:

<add name="MyEntities" connectionString="metadata=res://*/Data.MyDB.csdl|res://*/Data.MyDB.ssdl|res://*/Data.MyDB.msl;provider=System.Data.SqlClient;provider connection string=&quot;server=tcp:MyDB.database.windows.net;database=MyDB;UID=AnyString;Authentication=Active Directory Interactive;&quot;" providerName="System.Data.EntityClient" />

The important notes are that the name you specify in the SqlAuthenticationProviders section must be the exact same name you use in the connection string for Authentication.

The other important note is that, coming from your old connection strings, you have to change Data Source to be Server, and Initial Catalog to be Database. UID=AnyString is necessary, or an exception is thrown.

Failure to follow these steps exactly will net you a lovely error:

System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.AggregateException: One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> AdalException: The format of the specified domain name is invalid.\r\n at ADALNativeWrapper.ADALGetAccessToken(String username, IntPtr password, String stsURL, String servicePrincipalName, ValueType correlationId, String clientId, Boolean* fWindowsIntegrated, Int64& fileTime)\r\n at System.Data.SqlClient.ActiveDirectoryNativeAuthenticationProvider.<>c__DisplayClass2_0.b__0()\r\n at System.Threading.Tasks.Task`1.InnerInvoke()\r\n at System.Threading.Tasks.Task.Execute()\r\n --- End of inner exception stack trace

At the first the error doesn't make sense, but once you see that the parameters were renamed from Data Source to Server, it does make sense.

Answer 3

Maybe all you need to use is token (certificate) authentication as explained on below resource:

https://github.com/Microsoft/sql-server-samples/tree/master/samples/features/security/azure-active-directory-auth/token

Try to register your application with Azure Active Directory as explained on that resource.

Hope this helps.