Tutorial: Set up Azure Active Directory authentication for SQL Server

Applies to: SQL Server 2022 (16.x)

This document describes a step-by-step process on how to set up Azure Active Directory (Azure AD) authentication for SQL Server, and how to use different Azure AD authentication methods. This feature is available in SQL Server 2022 (16.x) and later versions, and is only supported for SQL Server on-premises, for Windows and Linux hosts and SQL Server 2022 on Windows Azure VMs.

In this tutorial, you learn how to:

• Create and register an Azure AD application
• Grant permissions to the Azure AD application
• Create and assign a certificate
• Configure Azure AD authentication for SQL Server through Azure portal
• Create logins and users
• Connect with a supported authentication method

Prerequisites

• SQL Server 2022 (16.x) is installed.
• SQL Server is connected to Azure cloud. For more information, see Connect your SQL Server to Azure Arc.
• Access to Azure Active Directory is available for authentication purpose. For more information, see Azure Active Directory authentication for SQL Server.
• SQL Server Management Studio (SSMS) version 18.0 or higher is installed on the client machine. Or download the latest Azure Data Studio.

Authentication prerequisites

Note

Extended functionality has been implemented in Azure to allow the automatic creation of the Azure Key Vault certificate and Azure AD application during setting up an Azure AD admin for the SQL Server. For more information, see Tutorial: Using automation to set up the Azure Active Directory admin for SQL Server.

• To perform Azure AD authentication, SQL Server needs to be able to query Azure AD and requires an Azure AD app registration, which it can authenticate as. The app registration also needs a handful of permissions for the queries SQL Server will perform.

• SQL Server uses a certificate for this authentication, and it is stored in Azure Key Vault (AKV). The Azure Arc agent downloads the certificate to the SQL Server instance host.

Warning

Connections authenticated by Azure AD are always encrypted. If SQL Server is using a self-signed certificate, you must add trust server cert = true in the connection string. SQL Server and Windows authenticated connections don't require encryption, but it is recommended.

Create and register an Azure AD application

1. Go to the Azure portal, select Azure Active Directory > App Registrations > New Registration.
   1. Specify a name - In the example below, we use SQLServerCTP1.
   2. Select Supported account types and use Accounts in this organization directory only
   3. Don't set a redirect URI
   4. Select Register

See the application registration below:

Grant application permissions

Select the newly created application, and on the left side menu, select API Permissions.

1. Select Add a permission > Microsoft Graph > Application permissions

   1. Check Directory.Read.All
   2. Select Add permissions

2. Select Add a permission > Microsoft Graph > Delegated permissions

   1. Check Application.Read.All
   2. Check Directory.AccessAsUser.All
   3. Check Group.Read.All
   4. Check User.Read.All
   5. Select Add permissions

3. Select Grant admin consent

Note

To grant Admin consent to the permissions above, your account requires a role of Azure AD Global Administrator or Privileged Role Administrator.

Create and assign a certificate

1. Go to the Azure portal, select Key vaults, and select the key vault you wish to use or create a new one. Select Certificates > Generate/Import

   1. For the Method of certificate creation, use Generate.

   2. Add a certificate name and subject.

   3. The recommended validity period is at most 12 months. The rest of the values can be left as default.

   4. Select Create.

   

   Note

   Once the certificate is created, it may say it is disabled. Refresh the site and it will show the certificate as enabled.

2. Navigate to the new certificate, and select the row for the certificate's latest version. Select Download in CER format to save the certificate's public key.

   

   Note

   This does not need to be done on the SQL Server host. Rather, any client that will access the Azure portal for the next step.

3. In the Azure portal, navigate to the app registration created above and select Certificates list

   1. Select upload certificate.
   2. Select the public key (.cer file) downloaded in the last step.
   3. Select Add.

   

4. In the Azure portal, navigate to the Azure Key Vault instance where the certificate is stored, and select Access policies

   1. Select Add Access Policy.

   2. For Key permissions, use 0 selected.

   3. For Secret permissions, select Get and List.

   4. For Certificate permissions, select Get and List.

   5. For Select principal, use the account for your Azure Arc instance, which is the hostname of the SQL Server host.

   6. Select Add and then select Save.

      You must Save to ensure the permissions are applied. They are not applied after selecting Add. To ensure permissions have been stored, refresh the browser window, and check the row for your Azure Arc instance is still present.

   

Configure Azure AD authentication for SQL Server through Azure portal

Note

Using the Azure CLI, PowerShell, or ARM template to set up an Azure AD admin for SQL Server is available.

1. Go to the Azure portal, and select SQL Server – Azure Arc, and select the instance for your SQL Server host.

2. Check the status of your SQL Server - Azure Arc resource and see if it's connected by going to the Properties menu. For more information, see Validate the SQL Server - Azure Arc resources.

3. Select Azure Active Directory on the left-hand column.

4. Select Set Admin, and choose an account that will be added as an admin login to SQL Server.

5. Select Customer-managed cert and Select a certificate.

6. Select Change certificate, and select your AKV instance and certificate that you created earlier in the new pane.

7. Select Customer-managed app registration.

8. Select Change app registration, and select the app registration you created earlier.

9. Select Save. This will send a request to the Arc server agent, which will configure Azure AD authentication for that SQL Server instance.

   

   It takes several minutes to download certificates and configure settings. After setting all parameters and selecting Save on the Azure portal, the following message may appear: SQL Server's Azure Arc agent is currently processing a request. Values below may be incorrect. Please wait until the agent is done before continuing. Wait until the save process is confirmed with Saved successfully, before attempting an Azure AD login.

   The Azure Arc server agent can only update once the previous action has completed. This means that saving a new Azure AD configuration before the last one has finalized can cause a failure. If you see the message Extended call failed when you select Save, wait 5 minutes and then try again.

   The Azure AD admin login is listed in sys.server_principals, but is not part of the sysadmin role. To grant the Azure AD admin the sysadmin role, use the sp_addsrvrolemember stored procedure.

   ALTER SERVER ROLE sysadmin ADD MEMBER [aadadmin@contoso.com]
   GO
   

   Note

   Once the Azure AD admin login is granted the sysadmin role, changing the Azure AD admin in the Azure portal does not remove the previous login that remains as a sysadmin. To remove the login, it must be dropped manually.

   The Azure AD admin change for the SQL Server instance takes place without a server restart, once the process is completed with the SQL Server's Azure Arc agent. For the new admin to display in sys.server_principals, the SQL Server instance must be restarted, and until then, the old admin is displayed. The current Azure AD admin can be checked in the Azure portal.

Create logins and users

After the Azure Arc agent on the SQL Server host has completed its operation, the admin account selected in the Azure Active Directory pane in the portal will be a sysadmin on the SQL Server instance. To sign in, use any SQL Server client like SSMS or Azure Data Studio.

Note

All connections to SQL Server that are done with Azure AD authentication require an encrypted connection. If the Database Administrator (DBA) has not set up a trusted SSL/TLS certificate for the server, logins will likely fail with the message The certificate chain was issued by an authority that is not trusted. To fix this, either configure the SQL Server instance to use an SSL/TLS certificate which is trusted by the client or select trust server certificate in the advanced connection properties. For more information, see Enable encrypted connections to the Database Engine.

Create login syntax

The same syntax that is used for creating Azure AD logins and users on Azure SQL Database and Azure SQL Managed Instance can now be used on SQL Server. However, on SQL Server this can be done by any account that has the ALTER ANY LOGIN or ALTER ANY USER permission. Any account with either of these permissions can create a login or user respectively. They don't need to be an Azure AD login.

To create a login for an Azure AD account, execute the T-SQL command below in the master database:

CREATE LOGIN [principal name] FROM EXTERNAL PROVIDER;

For users, the principal name should be in the format user@contoso.com. For all other account types, the tenant name isn't necessary and either the Azure AD group name or application name must be used.

Here's some examples:

-- login creation for Azure AD user
CREATE LOGIN [user@contoso.com] FROM EXTERNAL PROVIDER;
GO
-- login creation for Azure AD group
CREATE LOGIN [my_group_name] FROM EXTERNAL PROVIDER;
GO
-- login creation for Azure AD application
CREATE LOGIN [my_app_name] FROM EXTERNAL PROVIDER;
GO

To list the Azure AD logins in master database, execute the T-SQL command:

SELECT * FROM sys.server_principals;

To grant an Azure AD user membership to the sysadmin role (for example admin@contoso.com), execute the following commands in master database:

CREATE LOGIN [admin@contoso.com] FROM EXTERNAL PROVIDER; 
GO
EXEC sp_addsrvrolemember @loginame='admin@contoso.com', @rolename='sysadmin';
GO

The sp_addsrvrolemember stored procedure must be executed as a member of the SQL Server sysadmin server role.

Create user syntax

You can create an Azure AD user either as a user with an Azure AD login, or as an Azure AD contained user.

To create an Azure AD user from an Azure AD login in a SQL Server database where the user should reside in, use the following syntax:

CREATE USER [principal_name] FROM LOGIN [principal_name];

The principal_name syntax is the same as for logins.

Here are some examples:

-- for Azure AD user
CREATE USER [user@contoso.com] FROM LOGIN [user@contoso.com];
GO
-- for Azure AD group
CREATE USER [my_group_name] FROM LOGIN [my_group_name];
GO
-- for Azure AD application
CREATE USER [my_app_name] FROM LOGIN [my_app_name];
GO

To create an Azure AD contained user without a login, the following syntax can be executed:

CREATE USER [principal name] FROM EXTERNAL PROVIDER;

Use Azure AD group name or Azure AD application name as <principal name> when creating an Azure AD user as a group or application.

Here are some examples:

-- for Azure AD contained user
CREATE USER [user@contoso.com] FROM EXTERNAL PROVIDER;
GO
-- for Azure AD contained group
CREATE USER [my_group_name] FROM EXTERNAL PROVIDER;
GO
--for Azure AD contained application
CREATE USER [my_group_name] FROM EXTERNAL PROVIDER;
GO

To list the users created in the database, execute the following T-SQL command:

SELECT * FROM sys.database_principals;

The newly created user in a database has only the Connect permission, by default. All other SQL Server permissions for this user must be explicitly granted by the grantors.

Azure AD guest accounts

The CREATE LOGIN and CREATE USER syntax also supports guest users. For example, if testuser@outlook.com was invited to the contoso.com tenant, it could be added as a login to SQL Server with the syntax below. In the example, outlook.com is provided even though SQL Server will use the account registered in the contoso.com tenant.

The following section has examples of creating guest users.

Create a guest user with login that exists

CREATE USER [testuser@outlook.com] FROM LOGIN [testuser@outlook.com];

Create a guest user as a contained user

CREATE USER [testuser@outlook.com] FROM EXTERNAL PROVIDER;

Connect with a supported authentication method

SQL Server supports four authentication methods for Azure AD authentication:

• Azure Active Directory Password
• Azure Active Directory Integrated
• Azure Active Directory Universal with Multi-Factor Authentication
• Azure Active Directory access token

Use one of these methods to connect to the SQL Server instance. For more information, see Azure Active Directory authentication for SQL Server.

Authentication example using SSMS

Below is the snapshot of the SQL Server Management Studio (SSMS) connection page using the authentication method, Azure Active Directory - Universal with MFA.

During the authentication process, a database where the user was created must be explicitly indicated in SSMS. Expand Options > Connection Properties > Connect to database: database_name.

For more information on Azure Active Directory - Universal with MFA authentication method, see Universal with MFA.

SQL Server tools that support Azure AD authentication for Azure SQL are also supported for SQL Server 2022 (16.x).

Location where Azure AD parameters are stored

Warning

Azure AD parameters are configured by the Azure Arc agent, and should not be reconfigured manually.

On Linux, Azure Active Directory parameters are stored in mssql-conf. For more information about the Azure AD configuration options in Linux, see Configure SQL Server on Linux with the mssql-conf tool.

Known issues

• Updating certificate doesn't propagate:

  • Once Azure AD is configured for SQL Server, updating the certificate in SQL Server - Azure Arc resource's Azure AD pane may not propagate fully. This results in the save being successful but the old value still being displayed. To update the certificate, do the following:

    • Select Remove Admin.
    • Select Save.
    • Select Set Admin and reconfigure Azure AD authentication with the new certificate.
    • Select Save.

See also

• Connect your SQL Server to Azure Arc
• Azure Active Directory authentication for SQL Server
• Linked server for SQL Server with Azure Active Directory authentication