Configure and manage Azure Active Directory authentication with SQL Database or SQL Data Warehouse

This article shows you how to create and populate Azure AD, and then use Azure AD with Azure SQL Database and SQL Data Warehouse. For an overview, see Azure Active Directory Authentication.

Note

Connecting to SQL Server running on an Azure VM is not supported using an Azure Active Directory account. Use a domain Active Directory account instead.

Create and populate an Azure AD

Create an Azure AD and populate it with users and groups. Azure AD can be the initial domain Azure AD managed domain. Azure AD can also be an on-premises Active Directory Domain Services that is federated with the Azure AD.

For more information, see Integrating your on-premises identities with Azure Active Directory, Add your own domain name to Azure AD, Microsoft Azure now supports federation with Windows Server Active Directory, Administering your Azure AD directory, Manage Azure AD using Windows PowerShell, and Hybrid Identity Required Ports and Protocols.

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 show you how to change the associated directory for a given subscription.

  1. Connect to your Azure Classic Portal by using an Azure subscription administrator.
  2. On the left banner, select SETTINGS.
  3. 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.

    select subscription

  4. In the settings area, click your subscription, and then click EDIT DIRECTORY at the bottom of the page.

    ad-settings-portal

  5. In the EDIT DIRECTORY box, select the Azure Active Directory that is associated with your SQL Server or SQL Data Warehouse, and then click the arrow for next.

    edit-directory-select

  6. In the CONFIRM directory Mapping dialog box, confirm that "All co-administrators will be removed."

    edit-directory-confirm

  7. Click the check to reload the portal.

    Note

    When you change the directory, access to all co-administrators, Azure AD users and groups, and directory-backed resource users are removed and they no longer have access to this subscription or its resources. Only you, as a service administrator, can 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, also changes the Azure AD administrator for SQL Database and SQL Data Warehouse and disallow database access for any existing Azure AD users. The Azure AD admin must be reset (as described below) and new Azure AD users must be created.

Create an Azure AD administrator for Azure SQL server

Each Azure SQL server (which hosts a SQL Database or SQL Data Warehouse) starts with a single server administrator account that is the administrator of the entire Azure SQL server. A second SQL 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.

When using Azure Active Directory with geo-replication, the Azure Active Directory administrator must be configured for both the primary and the secondary servers. If a server does not have an Azure Active Directory administrator, then Azure Active Directory logins and users receive a "Cannot connect" to server error.

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

The following two procedures show you how to provision an Azure Active Directory administrator for your Azure SQL server in the Azure portal and by using PowerShell.

Azure portal

  1. 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 server making sure that the same subscription is used for both Azure AD and SQL Server. (The Azure SQL server can be hosting either Azure SQL Database or Azure SQL Data Warehouse.)
    choose-ad

  2. In the left banner select SQL servers, select your SQL server, and then in the SQL Server blade, click Active Directory admin.

  3. In the Active Directory admin blade, click Set admin.
    select active directory

  4. 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 shows 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.) Role-based access control (RBAC) applies only to the portal and is not propagated to SQL Server.
    select admin

  5. At the top of the Active Directory admin blade, click SAVE.
    save admin

The process of changing the administrator may take several minutes. Then the new administrator appears in the Active Directory admin box.

Note

When setting up the Azure AD admin, the new admin name (user or group) cannot already be present in the virtual master database as a SQL Server authentication user. If present, the Azure AD admin setup will fail; rolling back its creation and indicating that such an admin (name) already exists. Since such a SQL Server authentication user is not part of the Azure AD, any effort to connect to the server using Azure AD authentication fails.

To later remove an Admin, at the top of the Active Directory admin blade, click Remove admin, and then click Save.

PowerShell

To run PowerShell cmdlets, you need to have Azure PowerShell installed and running. For detailed information, see How to install and configure Azure PowerShell.

To provision an Azure AD admin, execute the following Azure PowerShell commands:

  • Add-AzureRmAccount
  • Select-AzureRmSubscription

Cmdlets used to provision and manage Azure AD admin:

Cmdlet name Description
Set-AzureRmSqlServerActiveDirectoryAdministrator Provisions an Azure Active Directory administrator for Azure SQL server or Azure SQL Data Warehouse. (Must be from the current subscription.)
Remove-AzureRmSqlServerActiveDirectoryAdministrator Removes an Azure Active Directory administrator for Azure SQL server or Azure SQL Data Warehouse.
Get-AzureRmSqlServerActiveDirectoryAdministrator Returns information about an Azure Active Directory administrator currently configured for the Azure SQL server or Azure SQL Data Warehouse.

Use PowerShell command get-help to see more details for each of these commands, for example get-help Set-AzureRmSqlServerActiveDirectoryAdministrator.

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-AzureRmSqlServerActiveDirectoryAdministrator -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.

Note

The Azure PowerShell command Set-AzureRmSqlServerActiveDirectoryAdministrator does not prevent you from provisioning Azure AD admins for unsupported users. An unsupported user can be provisioned, but can not connect to a database.

The following example uses the optional ObjectID:

Set-AzureRmSqlServerActiveDirectoryAdministrator -ResourceGroupName "Group-23"
-ServerName "demo_server" -DisplayName "DBA_Group" -ObjectId "40b79501-b343-44ed-9ce7-da4c8cc7353f"
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 Classic 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-AzureRmSqlServerActiveDirectoryAdministrator -ResourceGroupName "Group-23" -ServerName "demo_server" | Format-List

The following example removes an Azure AD administrator:

Remove-AzureRmSqlServerActiveDirectoryAdministrator -ResourceGroupName "Group-23" -ServerName "demo_server"

You can also provision an Azure Active Directory Administrator by using the REST APIs. For more information, see Service Management REST API Reference and Operations for Azure SQL Databases Operations for Azure SQL Databases

Configure your client computers

On all client machines, from which your applications or users connect to Azure SQL Database or Azure SQL Data Warehouse using Azure AD identities, you must install the following software:

You can meet these requirements by:

Create contained database users in your database mapped to Azure AD identities

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.

Note

Database users (with the exception of administrators) cannot be created using portal. RBAC roles are not propagated to SQL Server, SQL Database, or SQL Data Warehouse. Azure RBAC roles are used for managing Azure Resources, and do not apply to database permissions. For example, the SQL Server Contributor role does not grant access to connect to the SQL Database or SQL Data Warehouse. The access permission must be granted directly in the database using Transact-SQL statements.

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 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.

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, provide the display name of a security group:

CREATE USER [ICU Nurses] FROM EXTERNAL PROVIDER;

To create a contained database user representing an application that connects using an Azure AD token:

CREATE USER [appName] FROM EXTERNAL PROVIDER;
Tip

You cannot directly create a user from an Azure Active Directory other than the Azure Active Directory that is associated with your Azure subscription. However, members of other Active Directories that are imported users in the associated Active Directory (known as external users) can be added to an Active Directory group in the tenant Active Directory. By creating a contained database user for that AD group, the users from the external Active Directory can gain access to SQL Database.

For more information about creating contained database users based on Azure Active Directory identities, see CREATE USER (Transact-SQL).

Note

Removing the Azure Active Directory administrator for Azure SQL server prevents any Azure AD authentication user from connecting to the server. If necessary, unusable Azure AD users can be dropped manually by a SQL Database administrator.

Note

If you receive a Connection Timeout Expired, you may need to set the TransparentNetworkIPResolution parameter of the connection string to false. For more information, see Connection timeout issue with .NET Framework 4.6.1 - TransparentNetworkIPResolution.

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.

Note

Azure AD users are marked in the database metadata with type E (EXTERNAL_USER) and for groups with type X (EXTERNAL_GROUPS). For more information, see sys.database_principals.

Connect to the user database or data warehouse by using SSMS or SSDT

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.

Important

Support for Azure Active Directory authentication is available with SQL Server 2016 Management Studio and SQL Server Data Tools in Visual Studio 2015. The August 2016 release of SSMS also includes support for Active Directory Universal Authentication, which allows administrators to require Multi-Factor Authentication using a phone call, text message, smart cards with pin, or mobile app notification.

Using an Azure AD identity to connect using SSMS or SSDT

The following procedures show you how to connect to a SQL database with an Azure AD identity using SQL Server Management Studio or SQL Server Database Tools.

Active Directory integrated authentication

Use this method if you are logged in to Windows using your Azure Active Directory credentials from a federated domain.

  1. Start Management Studio or Data Tools and in the Connect to Server (or Connect to Database Engine) 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.

    Select AD Integrated Authentication

  2. 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.

    Select the database name

Active Directory password authentication

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 in to 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.

  1. Start Management Studio or Data Tools and in the Connect to Server (or Connect to Database Engine) dialog box, in the Authentication box, select Active Directory Password Authentication.
  2. 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.
  3. In the Password box, type your user password for the Azure Active Directory account or federated domain account.

    Select AD Password Authentication

  4. 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. (See the graphic in the previous option.)

Using an Azure AD identity to connect from a client application

The following procedures show you how to connect to a SQL database with an Azure AD identity from a client application.

Active Directory integrated authentication

To use integrated Windows authentication, your domain’s Active Directory must be federated with Azure Active Directory. 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; Initial Catalog=testdb;";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();

The connection string keyword Integrated Security=True is not supported for connecting to Azure SQL Database. When making an ODBC connection, you will need to remove spaces and set Authentication to 'ActiveDirectoryIntegrated'.

Active Directory password authentication

To connect to a database using integrated authentication and an Azure AD identity, the Authentication keyword must be set to Active Directory Password. 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; Initial Catalog=testdb;  UID=bob@contoso.onmicrosoft.com; PWD=MyPassWord!";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();

Learn more about Azure AD authentication methods using the demo code samples available at Azure AD Authentication GitHub Demo.

Azure AD token

This authentication method allows middle-tier services to connect to Azure SQL Database or Azure SQL Data Warehouse by obtaining a token from Azure Active Directory (AAD). It enables sophisticated scenarios including certificate-based authentication. You must complete four basic steps to use Azure AD token authentication:

  1. Register your application with Azure Active Directory and get the client id for your code.
  2. Create a database user representing the application. (Completed earlier in step 6.)
  3. Create a certificate on the client computer runs the application.
  4. Add the certificate as a key for your application.

Sample connection string:

string ConnectionString =@"Data Source=n9lxnyuzhv.database.windows.net; Initial Catalog=testdb;"
SqlConnection conn = new SqlConnection(ConnectionString);
connection.AccessToken = "Your JWT token"
conn.Open();

For more information, see SQL Server Security Blog.

sqlcmd

The following statements, connect using version 13.1 of sqlcmd, which is available from the Download Center.

sqlcmd -S Target_DB_or_DW.testsrv.database.windows.net  -G  
sqlcmd -S Target_DB_or_DW.testsrv.database.windows.net -U bob@contoso.com -P MyAADPassword -G -l 30

Next steps