Using Azure AD to authenticate public clients to SQL Azure


Azure AD enables access authorization to SQL Azure as an alternative to providing username/password information in the connection string: https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/. It is somewhat analogous to using Windows Authentication when both the client and the database are on a Windows domain network. It is particularly useful on public (non-confidential) clients where storing secrets is inappropriate and the only alternative would be to have the user use special SQL-only credentials. Azure AD authentication allows the user to access SQL by using their work or school credentials and supports single signon.

Sample code showing use of this approach in a WinForms/C# project is available here: https://github.com/mrochon/AADPublicClient2SQLAzure/. It shows three options for using AAD to connect to SQL Azure:

  1.  Using current Windows identity (assuming user is on-domain and Azure AD is federated with on-premises AD)
  2.  Using Azure AD challenge mechanism (including MFA) to authenticate the user
  3.  Using username/password entered directly into the client's UI

It also shows the use of these authentication methods with both ADO.NET and Entity Framework 6 (Code First). The SQL Azure and Azure AD setup follows instructions in: https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/. In my implementation I have defined a security group ('sql users' in my case) and added it as the SQL user login in  SQL Azure, as per above instructions. That way, to allow a new user to access the database all I need to do is to add this user to the group).

(There seems to be a missing setup step in the setup instructions provided in https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/. In addition to what it describes, you also need to allow your client application to request OAuth2 access tokens intended for SQL Azure as resource. To do that, use the Azure AD application manifest download/upload functionality to modify the manifest by including the following in the requiredResourceAccess section - see source code for example manifest).

{
     "resourceAppId": "022907d3-0f1b-48f7-badc-1ba6abab6d66"
}

Oct 7th, 2016: to use non-public, confidential clients to login to SQL, you can add the client app's display name (in Azure AD) as user to SQL and assign appropriate roles. You can then use OAuth2 client credential flow (with symmetric key or certificate) to obtain an access token.

 

Comments (2)

  1. Rees Watkins says:

    Please could you add more detail to this. I did not understand it and I think it may be related a problem I have – described below.
    > (There seems to be a missing setup step in the setup instructions provided in https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/.

    I followed the https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/.

    I am trying to connect to my web application using AD security which works fine but when entity framework tries to connect to SQLserver but keep getting this error. Using Authentication=”Active Directory Integrated” connection string

    Any light you could add would be greatly appreciated

    System.InvalidOperationException was unhandled by user code
    HResult=-2146233079
    Message=This operation requires a connection to the ‘master’ database. Unable to create a connection to the ‘master’ database because the original database connection has been opened and credentials have been removed from the connection string. Supply an unopened connection.
    ….
    Message=Failed to authenticate the user NT Authority\Anonymous Logon in Active Directory (Authentication=ActiveDirectoryIntegrated).
    Error code 0x534; state 10
    No mapping between account names and security IDs was done.
    Number=0
    Procedure=ADALGetAccessToken
    Server=tcp:occfintech.database.windows.net,1433
    Source=.Net SqlClient Data Provider
    State=0
    ….

    1. SailingRock says:

      No, your issue is not related to the ‘undocumented step’ – it is most likely caused by how EF and SQLConnect work together. The sample code I shared on GitHub includes an additional DbContext constructor (see AdverntureWorksModel.Token.cs) to take SQLConnection instead of connection string. This works for code-first EF. For model first EF you need to use the code currently commented out (and untested by me) in Form.cs (lines 68ff). (I got this code from someone else and did not have the time to test it; another colleague has successfully used it though). Hope this helps.

Skip to main content