Azure AD and SQL Server Authentication

Azure AD and SQL Server Authentication

If you use Azure SQL Server and you care about security, then it definitely makes sense to give users access via their Azure Active Directory account. Azure AD supports multi-factor authentication, identity protection and a lot of other security features which makes it much more secure than using a connection string.

Admin Access

The first thing to configure is the Admin access via Azure AD. That’s easily doable via the Azure Portal:

Navigate to your Azure SQL Server (not the Database!)

Open the Active Directory Admin settings:

Go to Set Admin and configure your user. I suggest to configure a group as it gives you more flexibility

After that, you can connect to your SQL Server with your Azure AD user (even if MFA is activated). If you use e.g. SQL Server Management studio, you must simply select “Active Directory – Universal with MFA support” as authentication method:

Read-only access

Giving a user read-only access to DB (e.g. to use Power BI or other tools) unfortunately doesn’t work via the Azure UI and must be done via a SQL command: