One of the challenges when considering migrating your on-premises SQL Server databases to Azure SQL Database is its lack of support for Active Directory-integrated authentication. Traditionally, your only option was to use SQL Server authentication. With the advent of Azure SQL Database V12, the authentication capabilities have been expanded, allowing for more flexibility that leverages Azure Active Directory. In this article, we will provide an overview of this functionality.
Azure Active Directory (AD) is an identity and access management solution integrated with Microsoft Azure. Every Azure subscription is associated with one and only one Azure AD tenant. This association determines which users, groups, and service principals (representing Azure AD-aware applications) can be granted permissions to resources within the Azure subscription. Users, groups, and service principals can be created directly in the tenant’s Azure AD instance or they can represent users synchronized from on-premises Active Directory forests.
The new authentication methodology introduced in Azure SQL Database V12 offers an Azure AD-based approach to supplement SQL Server authentication. Effectively, with Azure AD authentication in place, there are two administrative accounts on the Azure SQL Database server level. The first one is the traditional server administrator account, which has been available since the original implementation of Azure SQL Database. In addition, there is also an Azure AD-based administrative account, which corresponds to either an Azure AD user or an Azure AD group. Using a group (rather than a user) allows you to grant administrative access to multiple Azure AD users. Once you authenticate by using the Azure AD-based administrative account, you will have the ability to create database-level users corresponding to Azure AD identities. Incidentally, you can use the same administrative account to create database-level users based on the SQL Server authentication.
This new approach to authentication provides a number of benefits. The ability to use the same user name and password to obtain access to multiple Azure SQL Database server and database instances eliminates the credential sprawl, automatically improving security and lowering the management overhead associated with user account management. By virtue of integration with Azure AD, you also can implement centralized management of these credentials, either in the cloud (for Azure AD managed accounts) or on-premises (for synchronized and federated accounts originating from on-premises Active Directory). It is important to note that the current versions of SQL Server 2016 Management Studio and SQL Server Data Tools for Visual Studio 2015 supports the new Active Directory Universal Authentication option, which leverages Azure AD. This not only allows you to sign in to Azure SQL Database by using Azure AD credentials but also makes it possible to take advantage of natively available multi-factor authentication (MFA) capabilities.
An implementation of Azure AD authentication of Azure SQL Database involves the following high-level steps:
- Creating an Azure AD tenant, assuming that you do not have an existing one (keep in mind that, as we pointed out earlier, any Azure subscription is associated by default with an Azure AD instance). For details regarding this process, refer to the Azure Documentation, which describes different provisioning scenarios, including using an existing Office 365 subscription, using a Microsoft Account-based Azure subscription, using an organizational Azure subscription, as well as creating a new Azure AD tenant from scratch.
- Provisioning a new Azure SQL Database V12 server instance (by following SQL Database tutorial: Create a SQL database in minutes using the Azure portal) or upgrading an existing one to V12 (as documented in Upgrade to Azure SQL Database V12 using the Azure portal and Upgrade to Azure SQL Database V12 using PowerShell).
- Associating the Azure AD tenant with the Azure subscription hosting the Azure SQL Database server instance. For more information regarding this topic, refer to How Azure subscriptions are associated with Azure Active Directory.
- Creating an Azure AD user account that will become the administrator of the newly created Azure SQL Database server. You will find a description of this procedure at Add new users or users with Microsoft accounts to Azure Active Directory. Optionally, you can create an Azure AD group containing the Azure AD user accounts and use it instead to control the administrative privileges. More information about this procedure is available at Managing access to resources with Azure Active Directory groups.
- Designating the newly created Azure AD user or group as the Azure SQL Database administrator. You can execute this task directly from the Azure SQL Database server blade within the Azure portal or by running the Set-AzureRmSqlServerActiveDirectoryAdministrator Azure PowerShell cmdlet.
- Configuring client computers to allow them to take advantage of the Azure AD authentication. This requires installing .NET Framework 4.6 (or newer) and Azure Active Directory Authentication Library for SQL Server (available from Microsoft Download Center). Note that these components are included in the SQL Server 2016 Management Studio and SQL Server Data Tools for Visual Studio 2015.
- Creating database-level users corresponding to the Azure AD-based identities. This functionality is currently not exposed via the Azure portal. Instead you need to sign into the Azure SQL Database server by using the Azure AD-based administrative credentials and run the CREATE USER Transact-SQL statements with the FROM EXTERNAL PROVIDER parameter, referencing the userPrincipalName attribute of the Azure AD user credentials.
In our upcoming articles, we will describe this procedure in more detail. We will also illustrate how to configure your applications with access to an Azure SQL Database by using Azure AD-based authentication.