AAD – How to use Azure SQL Database with Azure Active Directory Authentification
In this post, i will go through the authentification with Azure Active Directory account and Azure SQL Database.
Creation of the SQL Server and database
In Azure, select the All Services button, search for SQL Server and click the star to add it in your favorites on the left bar.
Click the SQL Servers blade and Add
Fill the requested information to complete the creation of the SQL server.
Proceed next to add a new database to the SQL server. Click New Database
Fill the requested information to complete the database creation.
Management of the SQL Active Directory admin account
We will need to create and Active Directory admin account that will serve for the authentication between the Azure Active Directory user accounts and SQL Server.
This account will be used by SQL to authenticate others user accounts in Azure Active Directory.
Click on the Azure Active Directory blade, and then Users
Create the local user account (onmicrosoft.com) that you want to be defined as the SQL Active Directory admin account. Dont forget to change the password once completed.
Return to the SQL Server and click on the Active Directory Admin section.
Set the admin. Only an account or security group within your directory organization can be used.
Connect to the SQL database
The easiest way to proceed is to currently use the Query Editor within the Azure Portal.
Select your SQL Server > SQL Databases
Once on it, click Query Editor and Login
You will find three types of authentification.
Active Directory password authentification : To be used with the account that you previously set under the Azure Active Directory admin in the SQL Server or with an account that is part of the group that you assigned.
Active Directory single sign-on : To be used if the account that you set under the Azure Active Directory admin in SQL Server is the same with who you logged under your Azure subscription.
SQL Server authentification : To be used with the “local” account that you created when you build the SQL Server.
Use option 1 or 2, most important is to use the one you just set under the SQL Server.
Another way to connect is with Microsoft SQL Server Management Studio.
First you will need to add a firewall rules with your client IP to access the database server.
Select your SQL Server > SQL Databases
Once on it, click Set Server Firewall on top.
Add client IP
Once done, open SSMS, input the Server Name and select Active Directory – Password and click on Options > Connection Properties
Set the default database that you recently created in Azure and hit connect.
Errors connecting to Azure SQL DB
In case it doesnt work with the Active Directory – Password setting and you encounter this error : Failed to authenticate the user NT Authority\Anonymous Logon in Active Directory (Authentication=ActiveDirectoryIntegrated). Error code 0xCAA90002; state 10 WSTrust response does not have recognized SAML assertion. (.Net SqlClient Data Provider)
Use the Active Directory – Universal with MFA Support.
In case you cant connect with error 18456, add the name of the database manually in here and you should be OK.
Provide Access for the Azure Active Directory User Accounts / Security Group
In SQL, you can either add a single user account or a security group.
Before the creation in SQL, those accounts need to be present in Azure Active Directory within the same subscription of the SQL Server.
Also, its not possible to provide access to accounts from another source (Microsoft Account : hotmail/gmail) to access the database. Only the one created with onmicrosoft.com or custom domains names will be able to access (or accounts from another federated domains within the organisation).
CREATE USER [email@example.com] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [firstname.lastname@example.org]
CREATE USER [SG-SQL] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [SG-SQL]
To add those rights dont forget to use the AD Admin account under the SQL server that you previously set. You cant use the SQL auth account.
The use of the security group is very useful and practical as any new users that you will further add to this group from AAD will automatically have access to the database.