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
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.
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.
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.
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.
It gives you the list of all AD users in your AD directory.
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.
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.
Click on Create, and you get the user details as shown below.
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.
Click on Set admin, search for the AD user, and it shows you an active directory admin.
Click on Save to update the active directory admin for your Azure SQL Server.
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.
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.
Now, you can connect successfully using Azure AD authentication.
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.
Click on Create, and it configures the Azure AD group as shown below.
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.
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 >>.
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.
Now, you can connect to Azure SQL database using AD group members.
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.
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.
In the configuration, select the users and click enable.
It asks for a confirmation before enabling multi-factor authentication. You can go through the deployment guide from the link provided in the message.
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.
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.
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.
Click on Connect, and it asks you to enter the password in a pop-up window.
Click on Sign In, and it sends a code to your configured mobile number.
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.
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
- Read more articles on SQL Server Azure Tips.
- Refer to Microsoft documents for more details.
