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.

sql

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.

sql

Set the admin. Only an account or security group within your directory organization can be used.

Untitled.png

Connect to the SQL database

Query Editor

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.

SSMS

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.

 

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 [jess@heyitsjlahotmail.onmicrosoft.com] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [jess@heyitsjlahotmail.onmicrosoft.com]

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.

Untitled

Reference : https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s