Azure AD Authentication for Azure SQL Databases


By:   |   Updated: 2021-03-01   |   Comments   |   Related: > Azure


Problem

In a traditional on-premises SQL Server, users can connect using either Windows or SQL authentication. In the Windows authentication, you can add your AD user accounts, groups in SQL Server and provide appropriate permissions so that users can authenticate using secure and AD managed accounts.

If you plan to implement Azure SQL Database, by default, it creates a SQL user for performing all administrative tasks. In this tip, we are going to learn about how you can authenticate using the Azure Active Directory for a SQL Database.

Solution

Suppose you manage multiple Azure SQL databases for your organization. If you use the default SQL authentication, you need to create accounts in each SQL Server instances\databases for your users. It becomes complex to manage the credentials as well.

Azure Active Directory (AD) provides centralized management for all users for authentication to Azure services such as Azure SQL Database. You do not need to manage users credentials and store them in a secure location similar to a traditional active directory.

Azure Active Directory gives the following authentication options in SQL Server Management Studio.

  • Azure Active Directory Universal with MFA
  • Azure Active Directory Password
  • Azure Active Directory Integrated
connect to sql server

The multi-factor authentication (MFA) gives additional security using strong authentication with text messages, phone call smart cards with a pin, or mobile app notification.

In the below image (reference: Microsoft docs), we get a high-level overview of Azure AD authentication using applications such as SSMS, SSDT, SQL package or the on-premises active directory.

aad auth diagram azure ad authentication

In the Azure AD authentication mechanism, we get two administrator accounts.

  • Azure SQL database administrator account: It is the default user that uses the SQL authentication.
  • Azure AD administrator: It is the AD user that has the administrator permissions for SQL database.

In the Azure AD authentication, we use a contained database user for database-level authentication. Azure SQL database can use contained database users to authenticate identities at the database level. You can add users, groups in your SQL database; however, only the Azure AD administrator account can create the first Azure AD container database user.

In the below image (image reference: Microsoft docs), we can compare the Azure SQL database administrator and Azure AD administrator account.

azure ad authentication

Environment Details

To work with this tip, you require an active instance of the Azure SQL Database. You can follow tip SQL Azure Create Database Tutorial for more details.

In my lab environment, I have the following Azure SQL Server and database.

list of databases

Configure Azure AD authentication for Azure SQL Database

By default, you get an Azure active directory for your Azure resources. You can create another AD directory as well, but for this tip, we use the default directory.

In the Azure portal, search for users.

azure users

It gives you the list of all AD users in your AD directory.

azure users

In this portal, click on New User for configuring a new AD user. In the new user window, enter the user name, first name, last name for a user.

azure new user

In the password, you can either auto-generate a password or specify a password. If you choose the auto-generated password, click on Show password and copy the initial password for a user.

azure new user password

Click on Create, and you get the user details as shown below.

azure list of users

Now, go back to your Azure SQL Server and click on Active directory admin. As shown below, our Azure SQL database does not have an Active Directory admin.

azure active directory admin

Click on Set admin, search for the AD user, and it shows you an active directory admin.

azure active directory admin

Click on Save to update the active directory admin for your Azure SQL Server.

updating active directory message

To connect to the Azure SQL Database with Azure AD authentication, enter the following information in SSMS.

  • Server name: Enter the Azure SQL Server FQDN.
  • Authentication: Choose the authentication as – Azure Active Directory – Password.
  • User name and password: Enter the user name and password that we configured in the Azure AD.

As shown below, it generates an error message while you try to authenticate. The error message says – Password is expired.

connect to sql server

Actually, for the first time, the user needs to change his password during the first login. We cannot change the AD user password from SSMS. Therefore, connect to the Azure portal using your credentials. It asks you to supply the old and new password, as shown below.

update azure password

Now, you can connect successfully using Azure AD authentication.

ssms object explorer

Configure Azure AD group and add users for DB authentication

As per best practices, you should create the group and add users in the respective groups for better user management. You can quickly manage permissions for multiple users using the Azure AD groups. For example, suppose a set of users requires db_datawriter permissions for the time being. You can provide the db_datawriter permission to the AD group and all users belong to the AD groups get the needed permissions.

Before we create an Azure AD group, I have created two users.

  • User1: mssql1
  • User 2: mssql2

To create the AD group, search groups in the Azure Portal. Create a new group with the following information.

  • Group Type: Security
  • Group name: Enter an AD group name.
  • Group description: You can enter an optional AD group description.
  • Owner: Select an Azure AD user as the owner of the AD group.
  • Members: In the members, add both users mssql1 and mssql2.
new azure group

Click on Create, and it configures the Azure AD group as shown below.

azure ad group

Previously, we created the Azure AD user and assigned active directory permissions to it. It automatically creates the contained database user. To add the Azure AD group in the Azure SQL database, execute the following script in the [labazuresql] database. The alter command adds the AD groups in the db_datawriter fixed database role.

CREATE USER [dbwritergroup] FROM external provider
ALTER ROLE db_datawriter ADD MEMBER [dbwritergroup];

You can query the sys.database_principals and it gives you external user and external groups.

query data and results

Before, we can connect using the users in the AD group, change the password from the Azure portal during the first login.

Once you have modified your password, try to connect to Azure SQL Server using the group members. We still get an error message – Login failed for user <<token-identified principal >>.

connect to sql server error

As we explained earlier, the Azure AD user is a contained database user. We created the AD group in the [labazuresql] database; however, by default, SSMS tries to connect to the master database.

In the SSMS connection box, navigate to connection properties and specify the Azure SQL database name as shown below.

connect to sql server specific database

Now, you can connect to Azure SQL database using AD group members.

ssms object explorer

To validate the permissions, execute a select and insert statement. The insert runs successfully while the select statements fail because the group has db_datawriter permissions on [labazuresql] database, but it does not have db_datareaderpermissions.

query and results

Use Multi-factor authentication for Azure AD users.

Multi-factor authentication gives the additional form of identification for AD authentication for Azure SQL databases. Once you enable MFA for a user, it gets a code via a text message, email, MFA device, or it can use a fingerprint scan for authentication.

For example, let’s say we want to use MFA for the mssql1 and mssql2 users. Click on Multi-Factor authentication from the user's dashboard.

multi factor authentication

In the configuration, select the users and click enable.

multi factor authentication

It asks for a confirmation before enabling multi-factor authentication. You can go through the deployment guide from the link provided in the message.

multi factor authentication

Click on enable multi-factor auth. You get a confirmation message once the MFA is activated for selected users.

Note: The user's contact number, the email address should be updated so that they can receive the code via text or email.

multi factor authentication

Similarly, I have enabled MFA for John Peter as well. Now, if you try connecting to Azure SQL database using SSMS method Azure Active Directory: password, you get the following error message.

error message

As per the error message, it asks you to use MFA for Azure AD authentication. We need to switch to Azure Active Directory – Universal with MFA and provide the user name in SSMS as shown below.

connect to sql server

Click on Connect, and it asks you to enter the password in a pop-up window.

enter password

Click on Sign In, and it sends a code to your configured mobile number.

enter mfa code

Once you enter the code, click on Verify. It validates your code and allows the Azure AD authentication. As you saw using the MFA code, we get an additional layer of security.

ssms object explorer

In this tip, we explored Azure AD authentication for Azure SQL databases. You can use both individual and groups for connecting to SQL databases from SSMS or client tools. It supports .Net framework 4.6 or higher for SQL Server.

Next Steps






get scripts

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips


Article Last Updated: 2021-03-01

Comments For This Article





download














get free sql tips
agree to terms