In our recent article published on this forum, we have described the principles of using Azure Active Directory to authenticate to Azure SQL Database. We also provided a high-level overview of the multi-step procedure that allows you to implement this functionality. In this article, we will focus on its specifics.
A successful implementation of the Azure Active Directory-based authentication for Azure SQL Database relies on a combination of several distinct configuration changes. The scope of these changes includes not only the Azure Active Directory tenant hosting security principals that will be used to authenticate, the target instance of Azure SQL Database and its server, but also applications that will rely on the Azure Active Directory to authenticate, as well as computers where these applications will be running. More specifically, you will need to ensure that the following settings are in place:
- Identify the Azure subscription that will be hosting your SQL Database. This is important, since this subscription must be associated with the Azure Active Directory tenant that will host security principals authenticating to your Azure SQL Database. If necessary, it is possible to change this association, however at any point in time there is only one Azure Active Directory tenant you can use to grant access to resources within an Azure subscription. Keep in mind that changing the existing association might have negative implications if there are existing permissions in place, since they will no longer be valid once the change takes place.
To change the association, browse to the Azure classic portal and navigate to the SETTINGS page in the navigation bar on the left hand side of the portal page. Once there, on the SUBSCRIPTIONS tab, click the name of your subscription and click the EDIT DIRECTORY icon in the command bar at the bottom of the page. This will display the dialog box from which you can choose another Azure Active Directory tenant that you added to your subscription. Note that you have to be the Service administrator of the Azure subscription in order to be able to perform this task.
- Once you have identified the Azure Active Directory tenant, you will want to create users, groups, and potentially service principals (representing Azure Active Directory-aware applications) that will be authenticating to the target SQL Database. Alternatively, if you have an on premises Active Directory that synchronizes with that Azure Active Directory tenant, you can use your on-premises identities.
You must designate one of these Azure Active Directory users or groups that you created to become an additional Azure SQL Server administrative account (in addition to the one created at the time of server provisioning). This account is configured as a so-called contained database user in the master database, with the dbo_owner role in all user databases. Contained database users do not have the corresponding logins in the master database.
You can use either the Azure portal or the Azure PowerShell module to configure this additional administrative account. In the portal, you would access the relevant functionality from the Active directory admin blade accessible via the identically named entry in the Settings blade of the Azure SQL Database. Once there, clicking on the Set admin icon will reveal the list of existing Azure Active Directory accounts. At that point you can simply pick the account you designated and save the assignment. Alternatively, you can accomplish the same objective by running the Set-AzureRmSqlServerActiveDirectoryAdministrator Azure PowerShell cmdlet.
Similarly, you will need to define contained database users within user databases. Note that this functionality is not available from the Azure portal or Azure PowerShell. Instead, you will have to resort to using the CREATE USER Transact SQL statement with the FROM EXTERNAL PROVIDER clause. While this assigns only the PUBLIC role to the user, at that point, you have the option of providing an elevated level of privileges following the same methods that apply to traditional database users, such as assigning other database roles or granting required permissions directly.
You will obviously need an Azure SQL Database and hosting server. Make sure to create the server in the subscription you identified in the first step. In addition, you must provision the server by using the V12 version in order for Azure Active Directory authentication to be available. This is the option you can specify at the provisioning time. Alternatively, you can upgrade to V12 by following the procedure described on the Azure documentation.
- Another set of components that makes the Azure Active Directory authentication against Azure SQL Database possible includes local installed software. The computers that you intend to authenticate must have .NET Framework 4.6 or newer and Azure Active Directory Authentication Library for SQL Server (ADALSQL.DLL). Depending on the intended connectivity method, you might also need the SQL Server 2016 Management Studio and SQL Server Data Tools for Visual Studio 2015. Incidentally, installation of either of these tools automatically takes care of the ADALSQL.DLL prerequisite.
When connecting from SQL Server 2016 Management Studio or SQL Server Data Tools for Visual Studio 2015, you have two options to choose from that will allow you to connect to an Azure SQL Database by leveraging Azure Active Directory authentication:
- Active Directory Password Authentication - this is applicable when connecting using the Azure Active Directory-managed credentials (i.e. sourced from Azure Active Directory. It requires that you provide the name of the Azure Active Directory user (including the fully qualified domain name) as well as the user's password.
- Active Directory Integrated Authentication - this is applicable when connecting using the Azure Active Directory-federated credentials (i.e. sourced from your on-premises Active Directory and synchronized to Azure Active Directory). In this case, you do not have to provide a password as long as you are already authenticated by your on-premises Active Directory.
When authenticating from a custom code, you have three options to choose from:
- User-integrated Windows authentication - this is equivalent to the Active Directory Integrated Authentication described above, which relies on federation between your on-premises Active Directory and Azure Active Directory. In this case, the application connection string must include the Authentication=Active Directory Integrated entry.
- Azure Active Directory principal user name and password - this is equivalent to the Active Directory Password Authentication described above, which relies on Azure Active Directory credentials. In this case, the application connection string, in addition to the Authentication=Active Directory Password entry, must also include the UID and PWD values, representing respectively the user name and the password.
- Application token authentication - this requires registering your application in the Azure Active Directory tenant hosting the Azure SQL Database and creating a corresponding database user that represents the service principal, corresponding to the registered application. In addition, you need to generate a certificate representing your application and install it on the computer where the application is running. We will explore this approach in more detail in an upcoming article.
This concludes our description of the steps necessary to implement the recently introduced functionality that allows you to authenticate to Azure SQL Database by leveraging Azure Active Directory.