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.
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.