Setup Azure SQL Server to use Azure Active Directory authentication option

Last week, I supported one of my clients to config Azure SQL Server to use their domain accounts (they only used SQL Server credentials for a long time).

In this article, I will show you how we can do it!

Firstly, why we need to use Azure Active Directory Authentication for authentication with SQL?

Trust architecture

The following diagram illustrates the solution of using Azure AD authentication with Azure SQL Database.

Image source: https://docs.microsoft.com/

Administrator structure

Image source: https://docs.microsoft.com/

The benefits of Azure Active Directory authentication:

• Management of passwords and accounts is centralized.
• Customers can manage database permissions using external (Azure AD) groups.
• Includes Multi-Factor Authentication (MFA).
• It can eliminate storing passwords by enabling integrated Windows authentication and other forms of authentication supported by Azure Active Directory.

Note: I’m not going to talk too much about Azure Active Directory Authentication as you can find fully document from Microsoft here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication

Step 1: Setup Azure Active Directory

Note: You can skip these steps if you already configured Azure Active Directory (AAD)

Please have a look at “Source” column in the image below and refer “Trust Architecture” above:

These are 2 types of source.

• Azure Active Directory —accounts that created on Azure Portal using Azure Active Directory (AAD)
• External Azure Active Directory. — accounts from your company (Active Directory)

Step 2: Setup Azure SQL Server

Let’s go to the Server Configurations that we’ve just created above

There are two types of admin (refer to “Administrator structure” section above for the details):

• Server Admin — we created this account when creating a new service.
• Active Directory Admin — not configured

Let’s try to login to the SQL Server with SQL Authentication option:

Your IP Address is not allowed by default and you have to login by your Azure Account to make it whitelisted (you can whitelist your IP Address when setting up the Azure SQL Server — Networking)

OK, we can log in with SQL Authentication option.

Step 3: Configure Active Directory Admin

You can search and choose any user from your Group to set Active Directory Admin for your SQL Server.

Let’s try to login to the SQL Server with AAD Authentication option:

There are 3 options with Active Directory. I recommend you to use Universal with MFA support option.

OK, we can log in with SQL Authentication option.

Step 4: Create New Account

Here is the SQL Command to create new accounts

CREATE USER [domainuser]
FROM EXTERNAL PROVIDER
WITH DEFAULT_SCHEMA = dbo;

— add user to role(s) in db
ALTER ROLE dbmanager ADD MEMBER [domainuser];
ALTER ROLE loginmanager ADD MEMBER [domainuser];

Conclusion

Using Azure Active Directory option for SQL Server authentication is a recommended approach, but can add layers of complexity and frustration. Hopefully, through this article, you could save your effort on configuration.

References

Azure Active Directory - Azure SQL Database