How to provision an Azure SQL Database with Active Directory authentication

In this article, we will talk about how to provision an Azure SQL Database with authentication restricted to Active Directory users/groups/applications. We will use Pulumi to do that.

Why this article?

In a previous article, I already talked about connecting to an Azure SQL Database using Azure Active Directory authentication. However, my focus was on querying an Azure SQL Database from C# code (from an ASP.NET 6 Minimal API that was using Microsoft.Data.SqlClient 'Active Directory Default' authentication mode to be more precise), and not on the configuration of the Azure AD authentication itself.

Still, in that article, I wrote an Azure CLI script that showed how to provision and configure the database with Azure AD authentication enabled. So why write another article about that? First because I did not show how to give an Azure AD entity (user, group, or managed identity) permission to access the database. (In my samples, to simplify things I was using the SQL server Azure AD administrator account to make my queries 🤫). Yet, it is something you will probably have to do if you want your App Service or Function App to query your database. Second because even if Azure CLI is great to handle Azure resources (if you are a reader of my blog, you probably know that I enjoy very much Azure CLI), in a real project I would probably use a more advanced Infrastructure as Code solution like Pulumi. And that is what we will show here.

🗨️ If you are not familiar with Pulumi, it is an IaC solution similar to Terraform but using programming languages like C#. Speaking of C#, that is what I will use to write my infrastructure code but you can easily do the same in another language supported by Pulumi (TypeScript, Go, Python,... choose the one you are used to), the concepts stay relevant and the code will be similar.

Now, let's get to the heart of the matter.

An Azure AD user as our SQL Server administrator

Usually, when you create an Azure SQL Server, you have to provide an administrator login and an administrator password. But I said I wanted to limit the authentication to Azure Active Directory authentication only. So we will only need an Azure AD account to set as the administrator of our SQL Server. We could use an existing Azure AD account, but let's create a new Azure AD user just for that:

var config = new Config();
var sqlAdAdminLogin = config.Require("sqlAdAdmin");
var sqlAdAdminPassword = config.RequireSecret("sqlAdPassword");

var sqlAdAdmin = new User("sqlAdmin", new UserArgs
{
    UserPrincipalName = sqlAdAdminLogin,
    Password = sqlAdAdminPassword,
    DisplayName = "Global SQL Admin"
});

To create a new Azure AD user we need a login (it will be the email of the new user in our tenant) and a password. In this example, we retrieve these values from the configuration which is stored in the YAML settings file. You can notice there that we retrieve a secret (the password) from the configuration thanks to the config.RequireSecret method. Indeed to avoid exposing a secret in the configuration file or the state file, Pulumi has built-in support for secret encryption and decryption (not sure Terraform folks can say the same thing 😉).

Create the Azure SQL Server and its database.

Now that we have our administrator account, we can create the Azure SQL Server:

var sqlServer = new Server($"sql-sqlDbWithAzureAd-{Deployment.Instance.StackName}", new ServerArgs
{
    ResourceGroupName = resourceGroup.Name,
    Administrators = new ServerExternalAdministratorArgs
    {
        Login = sqlAdAdmin.UserPrincipalName,
        Sid = sqlAdAdmin.Id,
        AzureADOnlyAuthentication = true,
        AdministratorType = AdministratorType.ActiveDirectory,
        PrincipalType = PrincipalType.User,
    },
});

Nothing special here: we are using the variable sqlAdmin that is our newly created user to set the administrator of the SQL Server and we set the authentication to Azure AD only. We can then create the database:

var database = new Database("sqldb-sqlDbWithAzureAd-Main", new DatabaseArgs
{
    ResourceGroupName = resourceGroup.Name,
    ServerName = sqlServer.Name,
    Sku = new SkuArgs
    {
        Name = "Basic"
    }
});

Grant SQL Database access permissions to Azure AD entities

Once we have provisioned the Azure SQL Server and its database, here comes the tough part: we need to configure who can access the database. In a project, you will probably have to give access to some users and to the Azure resources that need to query the database (you will have to assign these resources a managed identity before that). But to keep things simple, we will just consider we need to grant SQL Database access to an Azure AD group. That could be a good way to do things by the way: create an Azure AD group, grant permissions to this group and add users and managed identities that need access to the database.

Why did I say that this part was tough? It's because to grant SQL database permissions, we need to execute an SQL command on the Server as you can read in the documentation.

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
GO

With this command, we are creating a user and giving db_datareader and db_datawriter roles. However it is not a classical user, it's a user that is "external" to the database: in our case, it corresponds to an Azure AD entity (a user, group, or application).

So it's not just about setting a property to properly configure an Azure resource, it's a bit more complicated.

I see multiple ways of doing that:

• Create a new Pulumi provider "SQL Server provider" that is to able manage users in an SQL Server database
• Write custom C# code that executes the SQL command once the database is created
• Use the Pulumi Command provider to execute the SQL command using the sqlcmd utility

Let's review these solutions.

New "SQL Server Provider"

To manage SQL Server resources like users and roles, we can create a complete provider. We could create it from scratch of course or use this Pulumi GitHub repository that provides some boilerplate code to create a Pulumi provider. Usually, Pulumi providers are written in Go (like the Terraform providers by the way) and generate SDKs for all programming languages supported by Pulumi.

Another way would be to adapt the existing Microsoft SQL Server Provider for Terraform. This Terraform provider made by the community enables you to create and manage logins and users on a SQL Server. I talked about "adapting" this provider because you can create a Pulumi provider out of a Terraform provider by using the Pulumi Terraform Bridge. That's great because instead of reinventing the wheel you can benefit from Terraform ecosystem by creating a Pulumi provider that wraps an existing Terraform provider. This GitHub repository contains boilerplate code to do exactly that.

You might have noticed that I sometimes criticize Terraform in my articles. That's not because I think Terraform is a bad infrastructure as code solution, in fact, I think it is a great solution with a rich ecosystem. However, I am critical of Terraform because I believe Infrastructure as Code should be done with programming languages instead of Domain-Specific Languages. Moreover, there are some areas (API coverage of major cloud providers, security, IDE support, ...) where I found Terraform is not good enough, especially compared to other platforms like Pulumi. So I am always a bit disappointed when I see that many people choose by default Terraform as their infrastructure as code platform without considering alternatives (and I am not only talking about Pulumi, there are also Farmer and Bicep for instance), even when these alternatives would be better suited to their use cases. That being said, Terraform has also advantages like its great community that creates and contributes to many providers like the mssql one.

This first solution of creating a new "SQL Server Provider" (whether it be from scratch, from boilerplate code, or from the mssql Terraform provider) is interesting but could be time-consuming because there are some things to set up and some amount of code to write.

Custom C# code

When you need to do something specific and there is no existing provider that can help you with it, you can just write the code to do it yourself without creating a complete provider. It's one of the reasons why I like Pulumi, even if you are doing Infrastructure as Code, at the end of the day you are just developing software so you can code what you need in the language you are familiar with. For instance, as I am developing in .NET, I can use the Microsoft.Data.SqlClient library (which is a data provider for Azure SQL Database) to connect and send commands to the database. And if I want to use Dapper on top of it because that's the library I am used to for querying a database I can. Hence writing the code that executes on the database the SQL command we have previously seen should not be very difficult.

Now, even if we are using imperative language in Pulumi to write the infrastructure code it's still declarative infrastructure as code with a state. Therefore, we have to be careful about how and when this custom code should be executed.

The easiest way is to use an Apply method on an output of the database like this:

database.Name.Apply(name =>
{
    /*** 
     * Indempotent code using Microsoft.Data.SqlClient library
     * to execute the SQL command that assigns the correct roles
     * to the Azure AD group we want to have access to the database.
    ***/ 
    return true;
});

The code in the Apply will execute on every run after the resource is created, that is why it needs to be idempotent. Having to make the code idempotent is a constraint that I would prefer to avoid but at least it gives us a simple way to execute the code that grants access to the database.

Another way would be to use Dynamic Providers whose purpose is exactly that: do an infrastructure task that no existing provider can help you deliver. You can see some use cases of dynamic providers in this Pulumi article. In our use case, we could imagine writing a dynamic resource provider for an Azure AD entity user in an Azure SQL Database. We would have to implement the different CRUD operations to handle the different use cases properly (a user is added, a user is removed, user roles are updated, ...). Unfortunately, as you can see in this GitHub issue, .NET Dynamic Providers are not yet supported (only TypesScript, JavaScript and Python are for the moment). It's a shame because Dynamic Providers provide an easy and efficient way of supporting custom resource types.

Command provider with the sqlcmd utility

The Microsoft tutorial, that shows how to grant database permissions to an Azure AD entity, explains how the necessary SQL commands can be run using the sqlcmd utility. So instead of writing some C# code to do the same, an interesting idea would be to directly run the sqlcmd utility. And you know what? There is a Pulumi provider for executing commands and scripts: the Command Provider.

Because it's a Pulumi provider, the sqlcmd command would be executed "as part of the Pulumi resource model" which means the scripts would be executed at the corresponding time of the resource life-cycle (the create script when the resource is created and so on). So it's very nice and not the same as executing the sqlcmd outside of a Pulumi program, without access to all the variables and where you would have to make your script idempotent. Moreover, the ability to execute commands remotely can bring interesting use cases, just not for our current concern here.

Pulumi Command Provider is currently in preview and only supports running scripts on create and destroy operations (support for diff, update and read operations will probably be added later). It works fine but does not log details about the error when a script fails, which makes debugging difficult. That should not prevent you from using it but as with any components in preview, use it with caution knowing everything is not perfect yet.

Implement the database permissions for an Azure AD Group

Of the 3 possible solutions let's take the 3rd one with the Command provider and the sqlcmd utility. It is probably not the "best" solution but I thought it would be simpler to use the sqlcmd utility than writing a complete provider or even custom C# code to do the same. Furthermore, it's the opportunity to test the Command provider which is fairly new.

Allow the machine running the Pulumi program to connect to the SQL Server

To run a SQL command in the database, the machine that executes the Pulumi program needs to have its public IP authorized. To programmatically retrieve the public IP address from where the Pulumi program is running we can use ipify API. It's a simple open source HTTP API that returns the public IP address of the caller.

var publicIp = Output.Create(new HttpClient().GetStringAsync("https://api.ipify.org"));

You can note here that we are just using standard C# code with an HttpClient that makes a GET to the API and returns asynchronously a string. I like the fact that with Pulumi we can reuse our existing C# skills, and the libraries we are used to. If we were to do that in Terraform we would have to look in the documentation how to do HTTP calls, discover that there is an http data source that can be used, understand how it works (to be honest it seems quite simple but still that is not natural) and use it.

Now we can enable this public IP by creating a firewall rule in the SQL Server.

var enableLocalMachine = new FirewallRule("AllowLocalMachine", new FirewallRuleArgs
{
    ResourceGroupName = resourceGroup.Name,
    ServerName = sqlServer.Name,
    StartIpAddress = publicIp,
    EndIpAddress = publicIp
});

Create the Azure AD group that will be given access to the database

We said we wanted to grant SQL Database access to an Azure AD group that will contain in the future users and application managed identities that need access to the database. So let's create that:

var sqlDatabaseAuthorizedGroup = new Group("SqlDbUsersGroup", new GroupArgs
{
    DisplayName = "SqlDbUsersGroup",
    SecurityEnabled = true,
    Owners = new InputList<string> { sqlAdAdmin.Id }
});

We set the Azure SQL Server admin as the owner of the group. This way, the admin of the database can add Azure AD users to the group and they directly have the permissions configured for this group. I like authorizing an Azure AD group instead of each Azure AD user because:

• it is easier to manage a group than individual users (adding a user to a group is less work than using SQL commands to assign the correct role for each user)
• you don't lose granularity of access control (you can always create several groups with different permissions if you need to)
• you can ensure that your application runs with the same permissions locally (the code you debug uses your user account identity) and on Azure (the code uses the managed identity of the App Service where it is hosted) by putting users and managed identities in the same group

Assign the roles to the Azure AD group using the Command provider

As we already talked about, we can specify a script to run on the create operation and another on the destroy operations. To keep things simple for this sample, we will only handle the creation scenario where we will add our Azure AD group as a user of the database and give it the expected roles. We already showed the SQL Command to execute, with our new group name it becomes:

CREATE USER {sqlDatabaseAuthorizedGroup.DisplayName} FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER {sqlDatabaseAuthorizedGroup.DisplayName};
ALTER ROLE db_datawriter ADD MEMBER {sqlDatabaseAuthorizedGroup.DisplayName};
GO

The sqlcmd utility can be used like this to send a command on the database:

sqlcmd -S {sqlServer.Name}.database.windows.net -d {database.Name} -U {sqlAdAdmin.UserPrincipalName} -P {sqlAdAdmin.Password} -G -l 30 -Q ' ___SQL Command___'

You can check the documentation to learn more about how to use sqlcmd but that is quite simple: we are just specifying to send a command line query on our database using Azure Active Directory to authenticate.

If we use all that with our Command provider, we get the following C# code.

var authorizeAdGroup = new Command("AuthorizeAdGroup", new CommandArgs
{
    Create = Output.Format($"sqlcmd -S {sqlServer.Name}.database.windows.net -d {database.Name} -U {sqlAdAdmin.UserPrincipalName} -P {sqlAdAdmin.Password} -G -l 30 -Q 'CREATE USER {sqlDatabaseAuthorizedGroup.DisplayName} FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER {sqlDatabaseAuthorizedGroup.DisplayName}; ALTER ROLE db_datawriter ADD MEMBER {sqlDatabaseAuthorizedGroup.DisplayName};'"),
    Interpreter = new InputList<string>
    {
        "pwsh",
        "-c"
    }
});

As you can see, we can specify a specific interpreter to use (PowerShell here).

Don't do like me and forget that our variables are outputs (only fully known when the infrastructure resource is completely provisioned). Because of that it is necessary to use the Output.Format method for string interpolation instead of using the C# operator $. Thanks to the community on Slack for helping me on that one because with the Command provider not logging the errors details I had a hard time on this.

Results

And that's it! We now have created the Azure AD group as an external user in the database and assigned it the db_datareader and db_datawriter roles. Here is what it looks like in Azure Data Studio:

Conclusion

This article is a bit long because I explain all the steps and possibilities but the complete code is not very big or complex. You can find it in this GitHub repository.

I did not see that many articles on the web that talk about using Azure Active Directory authentication for an Azure SQL Database, and even less that showed how to properly configure it using Infrastructure as Code. Yet, I think it's an important thing to do to properly secure your Azure SQL database. So I hope you enjoyed it and learn something. Whether you use Azure CLI, Bicep, ARM Templates, Terraform, or Pulumi, don't hesitate to use Azure AD authentication on your Azure SQL Database, for me that is the right and secure way to go.

As you have seen in this article, even when there is no provider for your custom resource or task, there are always several solutions to do what you want with Pulumi. Some are more elegant, some are more complex than others but you will always find a way and you will not be limited by the platform.

A big thank you to the Pulumi community that gave me some insights on how to configure Azure AD authentication on a database properly using Pulumi. Without the help of some people in the Pulumi Slack or the GitHub Issues/Discussions I would not have been able to write this article. Indeed some ideas and solutions are directly inspired by people's answers to my questions. This article is my way of contributing back and helping others that would have similar questions.