Connect Azure using Active Directory Authentication using SSMS

Helping businesses improve reliability and efficiency of SQL Server

As promised in my tweet and an update to the SQL Server Citation FB Page here are some excerpts for on Connect Azure using Active Directory Authentication. Recently I was reviewing one of the script I had to do for a client, and while working with SQL Server Management Studio (SSMS) I have noticed that there are total 3 new login options. Actually these new type of login options were available since SSMS 16.3 but I noticed it only recently while using SSMS 17.0 RC1.  Since long we have been seeing and using Windows Authentication, that includes Active Directory authentication already. Similarly there has been Azure Active directory as well, and it is also available with Multi-Factor authentication. SQL Server Management Studio has made it simple to Connect Azure using Active Directory Authentication using SSMS. Let us see those 3 new login options:

Active Directory Universal Authentication

As we all are aware, now a days we uses multi-factor authentication (MFA) for a security reasons. Microsoft Azure supports that too and with the SSMS 16.3 we can login Azure having MFA enabled using SSMS. Just like other MFA options, it does provide us to choose verification options like phone call, sms, smart card with pin or a pop-up notification on a mobile application. And this is known as Interactive authentication. There are two more options to connect Azure using Active Directory Authentication using SSMS which are not interactive.

Active Directory Password Authentication

Mainly, this option can be used to connect Azure using Active Directory (Azure AD) Authentication type when you need to connect to SQL Database while we are already logged into windows with credential which is not federated.

Active Directory Integrated Authentication

Now, this options is something similar to what we are using and knowing as Windows Authentication where we authenticate using Active Directory(AD).  Similarly, when you want to connect Azure using active directory authentication using SSMS use this option and it will use authentication mechanism to grant you login or reject via Azure Active Directory which is or can be federated domain.

As we can see, all the 3 new login options are little different then another, Active Directory Universal Authentication uses Multi-Factor authentication. Active Directory Password Authentication for credentials that are not federated and Active Directory Integrated Authentication when the credentials are federated, the later two authentication types are non interactive aka multi-factor authentication.

I am pausing here explaining each new login options  for Azure but I would extend it with how it works type of post or may be a video on SQL Server Citation YouTube Channel. Do let me know how you like this post.

If you encounter such issues please refer to this article Resolving High CPU usage in SQL Server.