f you’ve been working with Azure in the last few years, you’ve noticed there is a relatively new feature that’s supposed to make identity management easier with your Azure solutions: Azure Active Directory (AAD).  This feature does work with Azure SQL Servers and Azure SQL Databases.  But it may not completely plug and play.  In this article I’m going to go through the steps I took to get everything to “just work” as you’d expect.

Step Zero Woes

I’ve been a user of Microsoft services since Xbox Live first hit the scene.  So I have two Microsoft accounts: a “Personal account” and a “Work or school account”.  This means every time I’m prompted to log into a Microsoft service I get this extra step asking me which account I mean.  To make things more interesting, I set up my “Work” account when office 365 first started supporting what would come to be known as Azure Active Directory. I later turned Azure Active Directory on for my primary Azure subscription.

So when I say there were some interesting steps I had to take to get any of this to work as you would expect, I’m not kidding!

Since my email is tied to Office 365, I couldn’t delete that account. So I deleted my primary Azure subscription.  Then I created a new Azure subscription and attached it to the AAD that I created as a part of my Office 365 subscription.

I still get prompted for which account (Work or Personal), since I still use my Xbox Live account on the rare occasion.  But at least now, when I log in to my Work account, I only have one domain in my AAD, and that domain controls all my Azure objects!  So with that pain resolved, let’s move on to set up.

Azure Portal Setup

After you log in to the Azure portal, you can find the Azure Active Directory icon on the left pane. Click that and you land at the domain overview page.  You can see all the users and groups currently defined for this domain.  Microsoft considers it a best practice to set up a group to act as your SQL Administrator, rather than a single user.  That’s because currently, you can only define a single entry for Administrator.  From the overview page, the upper-right box is Quick tasks. Click Add a group to define your SQL Administrators group.

Setting up a group is easy.  Give the group a name, and select your members.

It really is that easy.

Once you have that group defined and added yourself to it. You’re ready to set this group to the administrative role on your SQL Instance(s).

Click on a SQL Server instance in the portal, and on its overview screen, you will see a link to edit SQL Administrators. Click SQL Administrators to continue.

On the next screen, there is an icon at the top you can click to Set admin. Click this and an extra pane slides out for you to select the administrator group you set up earlier. Click the group name, and then hit Select. The Add Administrator panel closes, and you are returned to the Active Directory Admin panel. Click the Save icon and you’re finished.

 

 

 

 

 

Connecting via SSMS

Now that our instance is set up to use AAD, let’s connect to our instance from SSMS. If you’re running Management Studio 2016 and SQL S

erver Data Tools for 2015 (14.0.60311.1) or later you should have noticed there are some extra authentication methods available in SSMS now.  We’re going to cover these out of order, since some of these options take more work than others.

Active Directory Password Authentication looks similar to a SQL authentication, but it accepts AAD User names and passwords.  When you choose this method, your credentials are sent over to Azure and end at your AAD instance.  Once your username and password are validated, AAD will check to see if you have rights to connect to the instance.  If so, you will connect.  If not, you will get an error message that you’ll need to google (bing) to find out what it really means.

With the steps we took in the last section, you should be able to log in to your Azure SQL Server as an administrator by using Active Directory Password Authentication.

Other Authentication Methods

Before we can use Universal Authentication or Integrated Authentication, we’re going to need to set up an Active Directory Domain Controller on my local network so we can set up a mapping from my local machine to AAD.  And I’m going to cover that, next time.