Setting up Integrated Security / Windows Authentication with Azure SQL Databases

In this lengthy post I explain how to setup Azure AD Authenticated users such that they may be used to connect to SQL Database hosted in Azure. Users of Windows azure will note that the only way till now to connect with SQL database hosted in Azure was via User names and passwords created for logins. This is the typical SQL authentication format. However with large number of users connecting to the database and multiple applications connecting to the same database it can be cumbersome to manage SQL logins within the Azure database. A long time need for Azure developers was to allow some form of AD authenticated users to access the database since that was the industry standard for a long time. A place where all users can be managed from one place.

With the latest preview we are now able to add AD or domain authenticated users in Azure and then map them to contained users within the Azure database. Here is how you can do it.

Create an Azure AD and an Account.

Click New at the bottom of the Azure Management Console and follow the green arrows

 

Once you click CUSTOMER CREATE add the details below

Where my Domain is the name of the domain that belongs to you. After pressing the check mark you will see the below entry in the Azure Portal

Add a User to the azure AD Domain

 

Click the domain into which you want to add the user, you can repeat this step as many times as you need once for each user. In our case we will need an Database Admin AD Account as well as a Database Contained AD user account.

 

Click the Users link on the top of the AD Page

Click Add User at the bottom of the page

In the below pop up enter the account name for the user, for example in this case I call it testuser1

Press the Arrow at the bottom of the page, Enter details like first name , last name etc. and press the arrow

Create temporary password for the user in the below screen and share it with the user. The user will be prompted to change the password when they login next time.

Once created a domain cannot be delete until all users under the domain are deleted first.

Create and Azure V12 Database Server

Click the SQL Database option in the Azure portal and select Servers

Click the add button at the bottom of the screen

Enter the Username and Password details for the Server Admin in the screen below, this is not the same as the AD account that was created in the previous Steps, this is the Azure equivalent of SA account.

Make sure the Check box in red is checked, this will automatically create the database as a V12, if you already have a server you can upgrade it as shown here.

Once the database is created we need to add our AD user as the Admin for this SQL Server.

Adding AD user as Admin account

 

Connect to your Azure database by selecting the database from within the management portal.

Navigate to new portal link on the dashboard screen as shown below

Within the new portal navigate to the Server properties as shown below

Agree to the Terms

The Click Set admin on the top of the screen

Press Select at the bottom of the scree followed by the Save button marked in Red.

Now you have added an AD Authenticated Admin account for your SQL Server instance. This azure account is important because only this account can create a contained user that authenticates with Azure AD.

Download and install SQL 2016 SSMS

The Step is to install SQL 2016 SSMS which in turn installs the providers need to connect to Azure AD managed account

You can download SSMS 2016 here

Once installed you need to connect to your Azure database using the newly created and permission AD account.

Add the connection details

Press Connect and open a new query window.

Adding a Contained DB user

Before adding a contained DB user, go back to the first step and add an AD account for the contained user, the steps are exactly the same as creating the AD Admin Account.

CREATE
USER [containeduser1@mydomain.onmicrosoft.com]


FROM
EXTERNAL
PROVIDER

 

Once the account is created you can start connecting using the contained user account, be sure to connect to the user database instead of the master database. Also if you get a message saying Password is expired make sure you’re not using the default temporary password generated by the Azure and that you have changed the password to a new value.

And that’s all there is to it, now you have AD authenticated users connecting to SQL Azure. This is especially usefule for connecting across domains where trust relationships are a problem.