Azure Active Directory authentication is a mechanism of connecting to Microsoft Azure SQL Database by using identities in Azure Active Directory (Azure AD). With Azure Active Directory authentication you can centrally manage the identities of database users and other Microsoft services in one central location. Central ID management provides a single place to manage SQL Database users and simplifies permission management. Benefits include the following:
- It provides an alternative to SQL Server authentication.
- Helps stop the proliferation of user identities across database servers.
- Allows password rotation in a single place
- Customers can manage database permissions using external (AAD) groups.
- It can eliminate storing passwords by enabling integrated Windows authentication and other forms of authentication supported by Azure Active Directory.
- Azure Active Directory authentication uses contained database users to authenticate identities at the database level.
[AZURE.IMPORTANT] Azure Active Directory authentication is a preview feature and is subject to the preview terms in the license agreement (e.g., the Enterprise Agreement, Microsoft Azure Agreement, or Microsoft Online Subscription Agreement), as well as any applicable Supplemental Terms of Use for Microsoft Azure Preview.
The configuration steps include the following procedures to configure and use Azure Active Directory authentication.
- Create and populate an Azure Active Directory
- Ensure your database is in Azure SQL Database V12
- Optional: Associate or change the active directory that is currently associated with your Azure Subscription
- Create an Azure Active Directory administrator for Azure SQL Server
- Configure your client computers
- Create contained database users in your database mapped to Azure AD identities
- Connect to your database by using Azure AD identities
The following high level diagram summarizes the solution architecture of using Azure AD authentication with Azure SQL Database. The arrows indicate communication pathways.
The following diagram indicates the federation, trust, and hosting relationships that allow a client to connect to a database by submitting a token that was authenticated by an Azure AD, and which is trusted by the database. It's important to understand that access to a database using Azure AD authentication requires that the hosting subscription is associated to the Azure Active Directory.
When using Azure AD authentication there will be two Administrator accounts for the SQL Database server; the original SQL Server administrator and the Azure AD administrator. Only the administrator based on an Azure AD account can create the first Azure AD contained database user in a user database. The Azure AD administrator login can be an Azure AD user or an Azure AD group. When the administrator is a group account, it can be used by any group member, enabling multiple Azure AD administrators for the SQL Server instance. Using group account as an administrator enhances manageability by allowing you to centrally add and remove group members in Azure AD without changing the users or permissions in SQL Database. Only one Azure AD administrator (a user or group) can be configured at any time.
To create new users you must have the ALTER ANY USER permission in the database. The ALTER ANY USER permission can be granted to any database user. The ALTER ANY USER permission is also held by the server administrator accounts, and database users with the CONTROL ON DATABASE or ALTER ON DATABASE permission for that database, and by members of the db_owner database role.
To create a contained database user in Azure SQL Database you must connect to the database using an Azure AD identity. To create the first contained database user, you must connect to the database by using an Azure AD administrator (who is the owner of the database). This is demonstrated in steps 4 and 5 below.
The following members of Azure Active Directory can be provisioned in Azure SQL Server:
- Native members: A member created in Azure AD in the managed domain or in a customer domain. For more information, see Add your own domain name to Azure AD.
- Federated domain members: A member created in Azure AD with a federated domain. For more information, see Windows Azure now supports federation with Windows Server Active Directory.
- Imported members from other Azure Active Directories who are native or federated domain members.
- Active Directory groups created as security groups.
Microsoft accounts (for example outlook.com, hotmail.com, live.com) or other guest accounts (for example gmail.com, yahoo.com) are not supported. If you can login to https://login.live.com using the account and password, then you are using a Microsoft account which is not supported for Azure AD authentication for Azure SQL Database.
- To enhance manageability we recommended you provision a dedicated Azure Active Directory group as an administrator.
- Only one Azure AD administrator (a user or group) can be configured for an Azure SQL Server at any time.
- Only an Azure Active Directory administrator can initially connect to the Azure SQL Server using an Azure Active Directory account. The Active Directory administrator can configure subsequent Azure Active Directory database users.
- We recommend setting the connection timeout to 30 seconds.
- Some tools like BI and Excel are not supported.
- Azure Active Directory authentication only supports the .NET Framework Data Provider for SqlServer (at least version .NET Framework 4.6). Therefor Management Studio (available with SQL Server 2016) and data-tier applications (DAC and .bacpac) can connect, but sqlcmd.exe cannot connect because sqlcmd uses the ODBC provider.
- Two-factor authentication or other forms of interactive authentication are not supported.
Create an Azure Active directory and populate it with users and groups. This includes:
- Create the initial domain Azure AD managed domain.
- Federate an on-premises Active Directory Domain Services with Azure Active Directory.
For more information, see Add your own domain name to Azure AD, Windows Azure now supports federation with Windows Server Active Directory, Administering your Azure AD directory, and Manage Azure AD using Windows PowerShell.
Azure Active Directory authentication is supported in the latest SQL Database V12. For information about SQL Database V12 and to learn whether it is available in your region, see What's new in the Latest SQL Database Update V12.
If you have an existing database, verify it is hosted in SQL Database V12 by connecting to the database (for example using SQL Server Management Studio) and executing SELECT @@VERSION; The expected output for a database in SQL Database V12 is at least Microsoft SQL Azure (RTM) - 12.0.
If your database is not hosted in SQL Database V12, see Plan and prepare to upgrade to SQL Database V12, and then visit the Azure Portal to migrate the database to SQL Database V12.
Alternatively, you can create a new database in SQL Database V12 by following the steps listed in Create a database in SQL Database Update. Tip: Read the next step before you select a subscription for your new database.
3. Optional: Associate or change the active directory that is currently associated with your Azure Subscription
To associate your database with the Azure AD directory for your organization, make the directory a trusted directory for the Azure subscription hosting the database. For more information, see How Azure subscriptions are associated with Azure AD.
Additional information: Every Azure subscription has a trust relationship with an Azure AD instance. This means that it trusts that directory to authenticate users, services, and devices. Multiple subscriptions can trust the same directory, but a subscription trusts only one directory. You can see which directory is trusted by your subscription under the Settings tab at https://manage.windowsazure.com/. This trust relationship that a subscription has with a directory is unlike the relationship that a subscription has with all other resources in Azure (websites, databases, and so on), which are more like child resources of a subscription. If a subscription expires, then access to those other resources associated with the subscription also stops. But the directory remains in Azure, and you can associate another subscription with that directory and continue to manage the directory users. For more information about resources, see Understanding resource access in Azure.
The following procedures provide step by step instructions on how to change the associated directory for a given subscription.
-
Connect to your Azure portal by using an Azure subscription administrator.
-
On the left banner, select SETTINGS.
-
Your subscriptions appear in the settings screen. If the desired subscription does not appear, click Subscriptions at the top, drop down the FILTER BY DIRECTORY box and select the directory that contains your subscriptions, and then click APPLY.
-
In the settings area, click your subscription, and then click EDIT DIRECTORY at the bottom of the page.
-
In the EDIT DIRECTORY box, select the Azure Active Directory that is associated with your SQL Server, and then click the arrow for next.
-
In the CONFIRM directory Mapping dialog box, confirm that "All co-administrators will be removed."
-
Click the check to reload the portal.
[AZURE.NOTE] When you change the directory, access to all co-administrators, Azure AD users and groups, and directory-backed resource users will be removed and they will no longer have access to this subscription or its resources. Only you, as a service administrator, will be able to configure access for principals based on the new directory. This change might take a substantial amount of time to propagate to all resources. Changing the directory will also change the Azure AD administrator for SQL Database and disallow SQL Database access for any existing Azure AD users. The Azure AD admin must be re-set (as described below) and new Azure AD users must be created.
Each Azure SQL Server starts with a single server administrator account which is the administrator of the entire Azure SQL Server. A second server administrator must be created, that is an Azure AD account. This principal is created as a contained database user in the master database. As administrators, the server administrator accounts are members of the db_owner role in every user database, and enter each user database as the dbo user. For more information about the server administrator accounts, see Managing Databases and Logins in Azure SQL Database and the Logins and Users section of Azure SQL Database Security Guidelines and Limitations.
[AZURE.NOTE] Users that are not based on an Azure AD account (including the Azure SQL Server administrator account) cannot create Azure AD based users because they do not have permission to validate proposed database users with the Azure AD.
Provision an Azure Active Directory administrator for your Azure SQL Server by using the Azure Portal
-
In the Azure Portal, in the upper-right corner, click your connection to drop down a list of possible Active Directories. Choose the correct Active Directory as the default Azure AD. This step links the subscription association with Active Directory with Azure SQL Database making sure that the same subscription is used for both Azure AD and SQL Server.
-
In the left banner select SQL servers, select your SQL server, and then in the SQL Server blade, at the top click Settings.
-
In the Settings blade, click Active directory admin (preview), and accept the preview clause.
-
In the Active directory admin (preview) blade, click to review, and then click OK to accept the preview terms.
-
In the Active directory admin (preview) blade, click Active directory admin, and then at the top, click Set admin.
-
In the Add admin blade, search for a user, select the user or group to be an administrator, and then click Select. (The Active Directory admin blade will show all members and groups of your Active Directory. Users or groups that are grayed out cannot be selected because they are not supported as Azure AD administrators. (See the list of supported admins in Azure AD Features and Limitations above.)
-
At the top of the Active directory admin blade, click SAVE.
The process of changing the administrator may take several minutes. Then the new administrator will appear in the Active directory admin box.
To later remove an Admin, at the top of the Active directory admin blade, click Remove admin.
To continue, you must install and configure at least version 0.9.8 of Azure PowerShell. For more information, see How to install and configure Azure PowerShell.
To provision an Azure AD admin you must execute the following Azure PowerShell commands:
- Add-AzureAccount
- Select-AzureSubscription
- Switch-AzureMode -Name AzureResourceManager
Cmdlets used to provision and manage Azure AD admin:
| Cmdlet name | Description |
|---|---|
| Set-AzureSqlServerActiveDirectoryAdministrator | Provisions an Azure Active Directory administrator for Azure SQL Server. (Must be from the current subscription.) |
| Remove-AzureSqlServerActiveDirectoryAdministrator | Removes an Azure Active Directory administrator for Azure SQL Server. |
| Get-AzureSqlServerActiveDirectoryAdministrator | Returns information about an Azure Active Directory administrator currently configured for the Azure SQL Server. |
Use PowerShell command get-help to see more details for each of these commands, for example get-help Set-AzureSqlServerActiveDirectoryAdministrator.
The following script provisions an Azure AD administrator group named DBA_Group (object id 40b79501-b343-44ed-9ce7-da4c8cc7353f) for the demo_server server in a resource group named Group-23:
Set-AzureSqlServerActiveDirectoryAdministrator –ResourceGroupName "Group-23"
–ServerName "demo_server" -DisplayName "DBA_Group"
The DisplayName input parameter accepts either the Azure AD display name or the User Principal Name. For example DisplayName="John Smith" and DisplayName="johns@contoso.com". For Azure AD groups only the Azure AD display name is supported.
[AZURE.NOTE] The Azure PowerShell command
Set-AzureSqlServerActiveDirectoryAdministratorwill not prevent you from provisioning Azure AD admins for unsupported users. An unsupported user can be provisioned, but will not be able to connect to a database. (See the list of supported admins in Azure AD Features and Limitations above.)
The following example uses the optional ObjectID:
Set-AzureSqlServerActiveDirectoryAdministrator –ResourceGroupName "Group-23"
–ServerName "demo_server" -DisplayName "DBA_Group" -ObjectId "40b79501-b343-44ed-9ce7-da4c8cc7353f"
[AZURE.NOTE] The Azure AD ObjectID is required when the DisplayName is not unique. To retrieve the ObjectID and DisplayName values, use the Active Directory section of Azure portal, and view the properties of a user or group.
The following example returns information about the current Azure AD admin for Azure SQL Server:
Get-AzureSqlServerActiveDirectoryAdministrator –ResourceGroupName "Group-23" –ServerName "demo_server" | Format-List
The following example removes an Azure AD administrator:
Remove-AzureSqlServerActiveDirectoryAdministrator -ResourceGroupName "Group-23" –ServerName "demo_server"
On all client machines, from which your applications or users connect to Azure SQL Database using Azure AD identities, you must install the following software:
- .NET Framework 4.6 or later from https://msdn.microsoft.com/library/5a4x27ek.aspx.
- Azure Active Directory Authentication Library for SQL Server (ADALSQL.DLL) is available in multiple languages (both x89 and amd64) from the download center at Microsoft Active Directory Authentication Library for Microsoft SQL Server.
- Installing either SQL Server 2016 Management Studio or SQL Server Data Tools for Visual Studio 2015 meets the .NET Framework 4.6 requirement.
- SSMS installs the x86 version of ADALSQL.DLL.
- SSDT installs the amd64 version of ADALSQL.DLL. Azure AD authentication is only partially supported by SSDT.
- The latest Visual Studio from Visual Studio Downloads meets the .NET Framework 4.6 requirement, but does not install the required amd64 version of ADALSQL.DLL.
Azure Active Directory authentication requires database users to be created as contained database users. A contained database user based on an Azure AD identity is a database user that does not have a login in the master database, and which maps to an identity in the Azure AD directory that is associated with the database. The Azure AD identity can be either an individual user account or a group. For more information about contained database users, see Contained Database Users- Making Your Database Portable.
To confirm the Azure AD administrator is properly set up, connect to the master database using the Azure AD administrator account. To provision an Azure AD based contained database user (other than the server administrator that owns the database), connect to the database with an Azure AD identity that has access to the database.
[AZURE.IMPORTANT] Support for Azure Active Directory authentication is available with SQL Server 2016 Management Studio.
Use this method if you are logged into Windows using your Azure Active Directory credentials from a federated domain.
- Start Management Studio and in the Connect to Database Engine (or Connect to Server) dialog box, in the Authentication box, select Active Directory Integrated Authentication. No password is needed or can be entered because your existing credentials will be presented for the connection.
- Click the Options button, and on the Connection Properties page, in the Connect to database box, type the name of the user database you want to connect to.
Use this method when connecting with an Azure AD principal name using the Azure AD managed domain. You can also use it for federated account without access to the domain, for example when working remotely.
Use this method if you are logged into Windows using credentials from a domain that is not federated with Azure, or when using Azure AD authentication using Azure AD based on the initial or the client domain.
- Start Management Studio and in the Connect to Database Engine (or Connect to Server) dialog box, in the Authentication box, select Active Directory Password Authentication.
- In the User name box, type your Azure Active Directory user name in the format username@domain.com. This must be an account from the Azure Active Directory or an account from a domain federate with the Azure Active Directory.
- In the Password box, type your user password for the Azure Active Directory account or federated domain account.
- Click the Options button, and on the Connection Properties page, in the Connect to database box, type the name of the user database you want to connect to.
To create an Azure AD based contained database user (other than the server administrator that owns the database), connect to the database with an Azure AD identity (as described in the previous procedure) as a user with at least the ALTER ANY USER permission. Then use the following Transact-SQL syntax:
CREATE USER Azure_AD_principal_name
FROM EXTERNAL PROVIDER;
Azure_AD_principal_name can be the user principal name of an Azure AD user or the display name for an Azure AD group or application.
Examples: To create a contained database user representing an Azure AD federated or managed domain user:
CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;
CREATE USER [alice@fabrikam.onmicrosoft.com] FROM EXTERNAL PROVIDER;
To create a contained database user representing an Azure AD or federated domain group:
CREATE USER [Nurses] FROM EXTERNAL PROVIDER;
For more information about creating contained database users based on Azure Active Directory identities, see CREATE USER (Transact-SQL).
When you create a database user, that user receives the CONNECT permission and can connect to that database as a member of the PUBLIC role. Initially the only permissions available to the user are any permissions granted to the PUBLIC role, or any permissions granted to any Windows groups that they are a member of. Once you provision an Azure AD-based contained database user, you can grant the user additional permissions, the same way as you grant permission to any other type of user. Typically grant permissions to database roles, and add users to roles. For more information, see Database Engine Permission Basics. For more information about special SQL Database roles, see Managing Databases and Logins in Azure SQL Database. A federated domain user that is imported into a manage domain, must use the managed domain identity.
[AZURE.NOTE] Azure AD users are marked in the database metadata with type E (EXTERNAL_USER) and for groups or applications with type X (EXTERNAL_GROUPS). For more information, see sys.database_principals.
Azure Active Directory authentication supports the following methods of connecting to a database using Azure AD identities:
- Using integrated Windows authentication
- With an Azure AD principal name and a password
To use integrated Windows authentication, your domain’s Active Directory must be federated with Azure Active Directory and your client application (or a service) connecting to the database must be running on a domain-joined machine under a user’s domain credentials.
To connect to a database using integrated authentication and an Azure AD identity, the Authentication keyword in the database connection string must be set to Active Directory Integrated. The following C# code sample uses ADO .NET.
string ConnectionString =
@"Data Source=n9lxnyuzhv.database.windows.net; Authentication=Active Directory Integrated;";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
Note that the connection string keyword Integrated Security=True is not supported for connecting to Azure SQL Database.
To connect to a database using integrated authentication and an Azure AD identity, the Authentication keyword must be set to Active Directory Password and the connection string must contain User ID/UID and Password/PWD keywords and values. The following C# code sample uses ADO .NET.
string ConnectionString =
@"Data Source=n9lxnyuzhv.database.windows.net; Authentication=Active Directory Password; UID=bob@contoso.onmicrosoft.com; PWD=MyPassWord!";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
For specific code examples related to Azure AD authentication see the SQL Server Security Blog on MSDN.