Vfrdtyky

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 AUTHORITYANONYMOUS 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:localLocalAppData.IdentityServiceAzureServiceAuthtokenprovider.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.

Once you deploy the application, please put your application settings and connection string to your webapp Application Settings option on Azure. Refer to this article.

Note: It has pulled the value from the App Settings configured in the Azure App Service’s – Application Settings blade but not from the Web.config file. This way, you can change these configurable values whenever you like to change without depending on developers. The same rule applies even for Connection String.