Going passwordless with Azure SQL

As more and more organizations move their applications and data to the cloud, the importance of security becomes paramount. One area that requires particular attention is the authentication and authorization of users and services that need access to sensitive data stored in databases. Traditionally, connecting to a database like Microsoft’s Azure SQL required providing a username and password in a connection string. However, this approach has several drawbacks, including the potential for credentials to be stolen or leaked, the difficulty of managing and rotating passwords, and the limited ability to enforce granular access controls. In this blog post, we’ll explore how connecting Azure services to Azure SQL in a passwordless way can overcome these challenges and improve the security and manageability of your database connections. We’ll also discuss the benefits of using Azure Active Directory (Azure AD) and Managed Identities, two key components of the passwordless approach to authentication.

Creating a group for administrators

When you desire to work in a passwordless way with Azure SQL, it is key that your Azure SQL Server allows for authentication through Azure Active Directory. To do so, you don’t provide an admin username and password while creating the SQL Server, instead, you select an identity in Azure Active Directory as an administrator. It is always a good idea to select an AD Group instead of a user. This user can leave the organization, be on holiday, or whatever. Using a group allows you to manage access at scale. Instead of having to individually assign permissions to each user, you can add or remove members from the group as needed, and those changes will apply to all resources that use the group for authentication. This simplifies administration and reduces the risk of human error or oversight.

Make sure that when you create the group, you set the Azure AD roles can be assigned to this group setting to yes when you create the group. This allows you to assign roles to the group. You cannot change this setting once the group is created.

Once you created the group, you now need to assign a role to the group so it can do magic with SQL.

The example below creates a new Azure SQL instance using Azure Bicep. The SQL Server administrator is a group called MySqlServerAdmins.

resource mySqlServer 'Microsoft.Sql/servers@2019-06-01-preview' = {
  name: sqlServerName
  location: resourceGroup().location
  properties: {
    serverAdmins: [
      {
        administratorType: 'ActiveDirectory'
        principalType: 'Group'
        sid: '' // Object ID of the group
        tenantId: subscription().tenantId
        login: 'MySqlServerAdmins'
        azureADOnlyAuthentication: true
      }
    ]
  }
}

Adding group members

It is a good practice to do database maintenance work from your pipeline. So schema migrations and seeding the database are jobs to be executed from your pipeline (or workflow). It is good to know, what identity is used to run this task. In Azure DevOps for example, this will be the identity of your pipeline agent. I Use a Virtual Machine Scale Set (VMSS) to host and scale my pipeline agents. It is the identity of the VMSS that is used for the pipeline. I added this identity to the SQL Server Administrators group in Azure Active Directory, so the pipeline agents in my custom agent pool are now administrators in my SQL Server.

I can connect to my SQL Server using the following Connection String (newlines added for readability):

Server=tcp:{serverNameOption.Value()}.database.windows.net,1433; `
Initial Catalog={databaseNameOption.Value()}; `
Persist Security Info=False; `
MultipleActiveResultSets=False; `
Encrypt=True; `
TrustServerCertificate=False; `
Connection Timeout=30; `
Authentication=Active Directory Default

Finishing off

I completed the bicep file so it also adds a SQL Database on my SQL Server. So now everything is set database-wise. Time to deploy an API. In this case, I host the API using the Azure Web App service. This means that I need to create an App Service Plan, and then the Web App itself:

It is important to understand that the Web App needs to be configured so that Managed Identities is turned on.

resource webApp 'Microsoft.Web/sites@2022-03-01' = {
  name: 'my-api-web-app'
  location: location
  tags: {
    costCenter: costCenter
  }
  kind: 'linux'
  identity: {
    type: 'SystemAssigned'
  }
  properties: {
    httpsOnly: true
    serverFarmId: appServicePlan.id
    siteConfig: {
      alwaysOn: true
      ftpsState: 'Disabled'
      minTlsVersion: '1.2'
      linuxFxVersion: 'DOTNETCORE|7.0'
    }
    virtualNetworkSubnetId: VNet::Subnet.id
  }
}

The snippet above creates a new Web App with the name my-api-web-app. It uses the App Service Plan appServicePlan. You can see that the identity of SystemAssined is used. This means that Azure will create an identity for you and register that in Azure AD. The biggest advantage now is that you can assign permissions to that Identity.

Setting permissions

To do so, I created a small CLI that I can execute from my Azure DevOps pipeline. Again, it is important to understand, that this CLI will run under the identity I assigned on my VMSS. The CLI, written in C#, connects using a standard SqlConnection object using the ConnectionString above and executes the following script:

IF NOT EXISTS(select * from sys.database_principals where name = 'my-api-web-app')
BEGIN
    CREATE USER [my-api-web-app] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA=[dbo];
    ALTER ROLE db_datareader ADD MEMBER [my-api-web-app];
    ALTER ROLE db_datawriter ADD MEMBER [my-api-web-app];
END

The SQL Script, adds a new user to the database. The FROM EXTERNAL PROVIDER indicates that the user comes from an external provider. In this case, Azure Active Directory. Once the user is added to the database, it is granted the db_datareader and db_datawriter roles. This means that the service can now read and write data, but does not have enough permissions to make (for example) schema changes. So if a SQL Injection accidentally succeeds and DROP TABLE [dbo].[Users] is executed, the service simply does not have enough permissions to do so.

You can use the same connection string to execute Entity Framework Schema Migrations and/or seed the database so everything is up and running and ready to serve once your API is deployed.

Conclusion

It takes some time and a little bit of understanding to get everything up and running (successfully). But it results in a completely passwordless connection between your web service and a SQL Server Database. The service has enough permissions to do what it needs to do, but nothing more. Schema migrations and seeding if you like are taken care of from the pipeline.

Everyone happy…

Last modified on 2023-05-14