5

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

arpan desai
  • 889
  • 2
  • 13
  • 23
  • Any update?If you feel my answer is useful /helpful.Please mark it as an answer so that other folks could benefit from it. – Brando Zhang Oct 23 '17 at 01:29
  • Actually we dropped the plan of using Active Directory Integrate authentication and we have created SQL Server user to connection to sql server. This user is specifically for app service only. – arpan desai Nov 01 '17 at 06:08

3 Answers3

8

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";
Jakub Holovsky
  • 6,543
  • 10
  • 54
  • 98
Brando Zhang
  • 22,586
  • 6
  • 37
  • 65
  • 2
    Does this run the risk of exposing the password in the connection string? – Paul H Jun 04 '20 at 01:59
  • 1
    A note, Windows Authentication is **not** the same as Azure Active Directory Integrated authentication – Peter Clotworthy Aug 11 '20 at 10:42
  • More details [here](https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?view=azuresql&tabs=azure-powershell#active-directory-integrated-authentication) – Lukasz Dynowski Jul 28 '22 at 08:15
7

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://learn.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.

Tsahi Asher
  • 1,767
  • 15
  • 28
Frog Pr1nce
  • 730
  • 9
  • 8
  • 1
    The question was about Active Directory Integrated authentication, but your answer is about Active Directory Interactive authentication (more recently known as "Azure Active Directory - Universal with MFA"). They are different things. – Mike Apr 07 '20 at 04:08
  • @Mike different or not, it solved my problem! It works for Azure Managed Identity authentication. – Tsahi Asher Feb 01 '21 at 15:45
  • Works like a charm, also make sure you grant the app access to the database CREATE USER [MyAppName] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [MyAppName]; ALTER ROLE db_datawriter ADD MEMBER [MyAppName]; ALTER ROLE db_ddladmin ADD MEMBER [MyAppName]; GO – rfcdejong Oct 20 '21 at 06:30
1

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.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30