title: Connecting to Azure SQL from App Service using AAD identity
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>A copy of the SQL database has already been created and configured.</p>
  
  <p>If I run the web application locally but using the new Azure database everything works as expected. </p>
  
  <p>If I run this command I get the expected AD user:</p>
  
  <pre><code>SELECT CURRENT_USER;
  </code></pre>
  
  <p>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:</p>
  
  <blockquote>
    <p>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'</p>
  </blockquote>
  
  <p>If I disable Managed Service Identity, I get this error instead:</p>
  
  <blockquote>
    <p>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: <a href="https://database.windows.net/" rel="nofollow noreferrer">https://database.windows.net/</a>, 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: <a href="https://database.windows.net/" rel="nofollow noreferrer">https://database.windows.net/</a>, 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: <a href="https://database.windows.net/" rel="nofollow noreferrer">https://database.windows.net/</a>, 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: <a href="https://database.windows.net/" rel="nofollow noreferrer">https://database.windows.net/</a>, 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: <a href="https://database.windows.net/" rel="nofollow noreferrer">https://database.windows.net/</a>,
    Authority: <a href="https://login.microsoftonline.com/common" rel="nofollow noreferrer">https://login.microsoftonline.com/common</a>. 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()</p>
  </blockquote>
  
  <p><strong>Is it what I'm trying to do possible? If yes, what am I missing? Any thoughts will be appreciated.</strong></p>
  
  <p>For reference, this is the method returning the SQL connection:</p>
  
  <pre><code>private SqlConnection GetSqlConnection()
  {
      var accessToken = new AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/").Result;
  
      return new SqlConnection
      {
          ConnectionString = WebConfigurationManager.ConnectionStrings["Default"].ConnectionString,
          AccessToken = accessToken
      };
  }
  </code></pre>
  
  <p>... and this is the connection string:</p>
  
  <pre><code>  &lt;connectionStrings&gt;
      &lt;add name="Default" connectionString="Data Source=myserver.database.windows.net;Initial Catalog=MyDB;" providerName="System.Data.SqlClient" /&gt;
    &lt;/connectionStrings&gt;
  </code></pre>
  
  <p>Note: The local AD is been synchronized with its Azure AD counterpart.</p>