Azure SQL and Entra ID authentication, tips from the field

Azure SQL is a managed SQL server in the cloud. It’s a great service, but it can be a bit tricky to get it to work with Entra ID Authentication. Here are some tips from the field.

Entra ID Admin required

If you create a new SQL server in Azure and set it to Use Microsoft Entra-only authentication, you’re required to set an admin. This Entra ID account is the only person that can manage the SQL server. This is a good thing in my opinion. What they don’t tell you, is that is will also use that specific account to “discover” which tenant is needed to login to the SQL server, if you want to use Entra to login to the SQL server.

This means that if you’re done with the setup, and you want to remove the admin account (least privilege principle), you can’t. Because then you can no longer connect to your SQL server. It took me a while to figure this out, and I’m sharing it here so you don’t have to go through the same process.

You can in fact remove the admin account, as stated in the documentation. I’m just advising you to not do this, as it will stop you from connecting to the SQL server with Entra ID authentication, as also indicated in this “important” message.

Access control (IAM) in Azure

Apart from the required admin quirk, Azure SQL is (by my knowledge) one of the few services that don’t support Access control (IAM) for data access in Azure, what a bummer if you want to manage everything inside Azure (automation, pipeline, script or portal). There are some build-in roles to manage the database/server itself, but those don’t give data access.

“Access control” for data access in sql databases are managed within the SQL server itself. You’re required to execute a sql command to add a user to the database, and then two more sql commands to grant them specific permissions or roles. The first command, to create a database user from an external provider, can only be executed by an Entra ID account. This means you’ll need to login to the database with a client that supports Entra ID authentication, like SQL Management Studio, or the portal these days. After the first command, it’s just like any database user, you can add roles or permissions to it.

CREATE USER [<Microsoft_Entra_principal_name>] FROM EXTERNAL PROVIDER;

-- e.g. Creating a user for an Entra user
CREATE User [some_entra_user@domain.com] FROM EXTERNAL PROVIDER;

-- Granting write permissions to the newly created user
-- Did you know that the sql role 'db_datawriter' does not grant read permissions?
EXEC sp_addrolemember N'db_datawriter', N'some_entra_user@domain.com'
EXEC sp_addrolemember N'db_datareader', N'some_entra_user@domain.com'

Solution, use security groups

So we have two “issues” here, the admin “account” which is required and the lack off manageability in Azure. Security groups can help you with both!

1. Create a security group for admins (e.g. sg_sql_{server_name}_admins), and add your admin account as a member!
2. Create a security group for “writers” (users and or “applications”) (e.g. sg_sql_{database_name}_writers)
3. Create the SQL server with authentication set to Entra only and add the security group sg_sql_{server_name}_admins as an admin
4. Create the SQL database
5. Connect to the SQL server with SQL Management Studio to the (or any other preferred tool that supports logging in with Entra)
6. Change to the correct database, remember your writers won’t need access to the master database. USE [your_database_name];
7. Execute the command below to add the security group sg_sql_{database_name}_writers as a database user to the database and grant it the required roles/permissions
8. Remove your admin account from the security group sg_sql_{server_name}_admins

USE [your_database_name];
CREATE USER [sg_sql_{database_name}_writers] FROM EXTERNAL PROVIDER;
EXEC sp_addrolemember N'db_datawriter', N'sg_sql_{database_name}_writers'
EXEC sp_addrolemember N'db_datareader', N'sg_sql_{database_name}_writers'

Patience

You’re going to need a lot of patience! It can take up to 2 hours before security group memberships are synced to the SQL server. Which is why you might want to setup the security groups before you grant them access. I’ve not found a way to force a sync of memberships. Maybe you could remove the group from the SQL server and add it again, but I’ve not tested this.

It might be worth checking if this would be solved by using privileged identity management (PIM) for the security group, but I’ve not tested this. Since then the SQL server would think the user is in the shadow group.

Automate the initial setup

When writing this post I came to the conclusion that it would be cool if you could execute the sql commands from a script. I’m not sure if there is a command line client for SQL that supports Entra ID Authentication, so I might build a small dotnet application (or PowerShell module) to do just that.

sql-cli --connection "Data Source=tcp:servername.database.windows.net,1433;Initial Catalog=dbname;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Default" --command "CREATE USER [sg_sql_{database_name}_writers] FROM EXTERNAL PROVIDER; EXEC ...;"

Mind you I can safely show this connection string here, because there is no password in it. It’s is using the default Entra ID Authentication method.

Limitations

Not all applications support Entra ID authentication, so if you’re counting on those applications, SQL in Entra ID only mode might not be suited for your use case. Read more about the limitations

Conclusion

With the help of security groups, you can manage access to your data inside the Azure SQL databases in a more manageable way. It’s a bit of a hassle to set it up, but it’s worth it in the end. You can now manage the access to your SQL databases in a more centralized way, and you can remove the admin account from the SQL server.

I would like to see better integration with IAM in Azure, but for now, this is the way to go. If you have any tips or tricks, please let me know on twitter or LinkedIn.