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:

  1. Navigate to your Azure SQL Server (not the Database!)
  2. Open the Active Directory Admin settings:
  3. 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:

  1. Connect to SQL Server with an Admin user
  2. Execute the following commands:
CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;
EXEC sp_addrolemember 'db_datareader', [[email protected]]
GRANT CONNECT TO [[email protected]]

-- to remove the user, execute:
-- DROP USER [[email protected]]

To see the permissions at the database, you can execute the following script by Benjamin Perkins (https://blogs.msdn.microsoft.com/benjaminperkins/2018/09/03/how-to-create-a-read-only-user-for-a-sql-azure-database/ )

SELECT DP1.name AS DatabaseRoleName,
    isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
    ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
    ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;

Common Issues

If your users face issues while connecting, please check and ensure the following things:

  • Correct DB – If you get the error: Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’
    • Make sure your users configure the Database when they connect to the Server. This issue usually pops up if users try to connect to the master db without having permissions.
  • Make sure you executed “GRANT CONNECT TO [[email protected]]”
  • Make sure that Port 1433 is open for outgoing connections from your local network
  • Make sure that the Azure Firewall allows connections from the IP address:
    • Open your Database in the Azure portal
    • In the overview, you can find the “Set server firewall” setting
    • Make sure that the client IP is listed there

Additional Information:

Use Azure Active Directory Authentication for authentication with SQL: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication

Configure and manage Azure Active Directory authentication with SQL: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure

Controlling and granting database access to SQL Database and SQL Data Warehouse: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

SQL Database security: Manage database access and login security: https://github.com/rgl/azure-content/blob/master/articles/sql-database/sql-database-manage-logins.md

Categories:

One response

Leave a Reply

Your email address will not be published. Required fields are marked *

About
about armin

Armin Reiter
Blockchain/Web3, IT-Security & Azure
Vienna, Austria

Reiter ITS Logo

Cryptix Logo

Legal information