AD Authentication and Azure SQL Database

Setting up AD authentication with Azure SQL Database sounds simple, it is assuming you plan carefully. I did run into issues but once rectified it felt great using AD authentication in Azure rather than just SQL logins.

If you look at the below diagram, I basically want to create an Active Directory Admin for my SQL Server which I have done and it is an AD group something that I would recommend over just a single user account.

Even though Microsoft provides a lot of documentation on the setup I thought I would share my experiences and issues encountered and hopefully one day someone may find my post useful.

adchart

I think it is important to highlight a couple of points, more specifically around the requirement of ADALSQL.DLL and proper setup of AD which I will highlight below and reference some links, please do this as it lays the foundation for you.

ADALSQL.DLL

You need ADALSQL.DLL which is part of the latest SQL Server Management Studio (SSMS) to test access. This stands for Active Directory Authentication Library for SQL Server.

adalsql

If you don’t have this then you might get the error below. Basically what I am trying to say here is use SSMS 17 found here: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

errorssms

Create and populate an Azure AD and Provision AD Admin

Along with your administrators follow this guide: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure  ( I will assume you have already).

The main goal here to set finally set an Azure Directory Admin within your SQL Server.

ad admin

Let’s jump into SSMS 17.

Login in via SSMS

You cannot login to SSMS via SQL authentication and issue create commands for AD users, it will not work even though you have Active Directory Admin setup. Try it and you will get the below.

Only connections established with Active Directory accounts can create other Active Directory users.

You will need to login via AD Authentication. So I select AD Integrated Authentication which picks up my account that is within the Admin group but I get this Multi-factor authentication (MFA) error.

mfa

I use MFA so I had to login via universal authentication where then I have to use my pass code to login via SSMS.

ssmsMFA

I am in as my main AD account now.

AZUREADLOGIN

I did find something “buggy”, if you edit top 200 rows you will be prompted with a pop box which prevents me from doing anything.

CONNECTTOSQL

Rather strange! I hope Microsoft fixes this.

Assume now I am connected via my Azure AD Admin account, it is here where you issue create user commands such as:

CREATE USER [Bill.Gates@domain.com]
FROM EXTERNAL PROVIDER;
GRANT CONNECT TO [Bill.Gates@domain.com]

EXEC sp_addrolemember 'db_datareader', ‘Bill.Gates@domain.com’;

So login to SSMS as the above user using AD Integrated Authentication and it should work.

ad intergrated.JPG

You MIGHT get this error.

generalerror

Why? You need to set your Default database under connection properties.

db props

Then you will be authenticated – read only, just to prove this I try to create a table which will fail.

createtables

I log back in as my main Azure admin account and issue the following query to show permissions.


SELECT
p.name,
prm.permission_name,
prm.class_desc,
prm.state_desc,
p2.name as 'Database role',
p3.name as 'Additional database role'
FROM sys.database_principals p
JOIN sys.database_permissions prm
   ON p.principal_id = prm.grantee_principal_id
   LEFT JOIN sys.database_principals p2
   ON prm.major_id = p2.principal_id
   LEFT JOIN sys.database_role_members r
   ON p.principal_id = r.member_principal_id
   LEFT JOIN sys.database_principals p3
   ON r.role_principal_id = p3.principal_id
   WHERE p.name <> 'public'

connections

3 thoughts on “AD Authentication and Azure SQL Database

  1. Pingback: Azure Cloud “Fear” Busting #1 – Security | All About SQL

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s