Working with Azure Active Directory and Azure SQL Database

Introduction

By default, when someone creates an Azure SQL Server, an Administrator with SQL Authentication is created by default. However, for administrative purposes, it is a best practice to use Azure Active Directory.

Azure Active Directory (AD) can be used to access to several Azure resources like Azure SQL Database, Azure SQL Data Warehouse, Office 365, Salesforce, Dropbox, Adobe Create Cloud, ArcGis and more. Azure Active Directory is a cloud directory and an identity management service.

Having different credentials for each application is a chaos. That is why using Azure for Azure SQL Database or other Azure applications is necessary. You can centralize the authentication management. It also provides stronger security to your Azure SQL Databases.

Target audience

If you do not have any experience in AD on-premises or Azure AD, this article is for you.
If you have experience in AD on-premises and you do not have experience in Azure AD, this article is also for you!

Overview

In this new article, we will learn how to:

Create an Azure Active Directory user
Create an Azure Active Directory group and assign the user the group
How to add an Azure Active Directory user/group as an Azure SQL Administrator
How to Add Azure Active Directory users to Azure SQL Database

Requirements

A subscription to Azure
An Azure SQL Server and database created (if you do not have one, you can create one following our previous tutorial Working with Visual Studio and SQL Azure databases)

Getting started

By default, an Azure AD directory is already created by default. We will first create the user and then add it to a group. Finally, we will add it to Azure SQL Database.

Create an Azure Active Directory User

In the Azure Portal, right click on the account and select your directory:

The directory stores information like users and groups in Azure. In this example, the directory is dani671hotmail.onmicrosoft.com.

To create a user, in the Azure Portal press the > icon and look for Users and Groups:

Go to all users and press New user:

Add information about the user like the name, user name, first name, last name, work information, etc.:

You can assign roles like the User, Global and Limited administrator role. Once that you select the options, press create:

Roles

A User can access to the resouces, but cannot manage the directory resources.
A Global administrator can do anything, except changing the password of another administrator.
The Limited administrator is limited to the role assigned. It can be a password administrator, Service administrator, SharePoint administrator, Security Administrator, etc.

You will notice that the new user was created successfully:

If you click on the new users, you have the options to reset the password or to delete it. By default, the user is created with a temporary password that you are forced to change after the first login in the portal:

You also have the option to check the Profile:

In the Profile, you can add your photo, block the permissions to sign in, specify usage location and more information:

Create an Azure Active Directory Group and assign the user the group

It is a best practice to work with groups instead of working with individual users. It simplifies the administration and it is more intuitive. Also it is easier to handle when the people moves to another office or another company. When a new person comes, you only need to assign him to the role instead of adding the user to each resource.

To create a new user, in the Azure Portal, go to User and Groups and then go to All Groups and select New Group:

You can assign users to the group now. In Members, check the users that you want to add to the group:

How to add an Azure Active Directory user/group as an Azure SQL Administrator

To assign an Azure Active Directory user/group to Azure SQL Database as an Administrator, in the Azure Portal, click the > icon and search for SQL servers:

Select the SQL Server with an Azure SQL Database:

Click Active Directory admin and press the option Set admin option:

You can select a User or a Group as the Active Directory administrator:

Once you select the user or group, press save:

In SSMS, try to login using the new Azure Active Directory User created:

A typical error message is this one:

Cannot connect to sqlshackserver.database.windows.net.

– – – – – – – – – –
ADDITIONAL INFORMATION:

Failed to authenticate the user jlennon@dani671hotmail.onmicrosoft.com in Active Directory (Authentication=ActiveDirectoryPassword).
Error code 0xCAA20064; state 10
AADSTS50055: Force Change Password.
Trace ID: 3c10ceb7-d998-4a94-9013-59efcc8f3900
Correlation ID: 19b78a89-ba7e-402d-bbc8-7b004b2ec523
Timestamp: 2017-07-14 00:16:07Z (Microsoft SQL Server, Error: 0)

This error happens if you keep your user with the default password. You need to login to the Azure Portal specify your credentials and the Portal will ask you to provide a new password:

If you do not remember your password, you can always reset it. If everything is OK, you will login with your user in SSMS:

How to Add Azure Active Directory Users to Azure SQL Database

We added Azure Active Directory Users as Administrators of Azure SQL Database. Is it possible to work with non-administrator users?

Yes, you can basically work with Azure Active Directory Users in the same way that you do with an Azure SQL Database User. The following lines of code shows how to add a user named jgonzales from the domain dani671hotmail.onmicrosoft.com to Azure SQL:

CREATE USER [jgonzales@dani671hotmail.onmicrosoft.com] FROM EXTERNAL PROVIDER;

Note that we are assuming the user jgonzales is already created in Azure Active Directory. Also, you can only add AD with an Azure AD Account. If you try to run the T-SQL code provided before using SQL Authentication you will receive the following error message:

Msg 33159, Level 16, State 1, Line 1
Principal ‘jgonzales@dani671hotmail.onmicrosoft.com’ could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

The following example shows how to grant select permissions to the AD user jgonzales on the table SalesLT.Customer. The table SalesLT.Customer is included in the AdventureworksLT that can be installed with the Azure SQL Database:

GRANT SELECT ON [SalesLT].[Customer] TO [jgonzales@dani671hotmail.onmicrosoft.com];

The following example shows how to add the AD user jgonzales to the dbmanager role. You need to run the sentence in the master database:

ALTER ROLE dbmanager ADD MEMBER [jgonzales@dani671hotmail.onmicrosoft.com];

As you can see, working with Azure AD is a straightforward process.

Conclusion

Azure Active Directory allows to create a unique authentication to the thousands of resources in Azure including Azure SQL Database and Azure SQL Data Warehouse. In this article, we learned how to add an Azure Active Directory user, how to create an Azure Active Directory Group and add a member to it. Finally, we learned to create an Azure Admin for our Azure SQL. With that user, we login and we can create other Azure Active Directory users in Azure SQL Database with lower privileges.

Finally, to address a common question, if you have Active Directory on-premises, yes, you can integrate with Azure Active Directory. Please check the references below for more information.