A few weeks ago on the blog, we saw how it was possible to configure Azure Active Directory (AAD) Security Groups as an access mechanism for your Azure SQL Databases. Depending on how this feature is utilised, it has enormous potential to reduce the amount of administrative effort involved when managing access to your databases at scale. Also, the solution can even help to increase security, by ensuring your database users are authenticating with their AAD accounts instead of separate/shared accounts to carry out their daily tasks.

A natural consequence of introducing this functionality is that you may need to modify how you deploy your database changes if you are using source control or a Visual Studio database project as a central repository for your entire database schema. Typically, you would use the SQL Server built-in admin account or a similarly privileged account to create all Data Manipulation Language (DML), Data Definition Language (DDL) etc. components during a data-tier application package (DACPAC) deployment. However, in this case, any attempt to create an EXTERNAL PROVIDER user account will likely lead to the following error occurring:

Error SQL72014: .Net SqlClient Data Provider: Msg 33159, Level 16, State 1, Line 1 Principal ‘My AAD Security Group’ could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

Fortunately, in this case, the error message is pretty helpful and, if deploying your database changes out via an Azure DevOps Pipeline, we can modify our build pipeline to accommodate this change, using the Active Directory - Password Authentication Type option:

So far, so good - however, when we attempt to run this deployment task through, we get the following error message

Appearances can be deceiving on this one - the error would seem to suggest that the incorrect username or password values have been supplied, but even after triple-checking these values and even hardcoding them in, the same error still occurs. Only after some research online and a dig through the following Visual Studio Developer Community post do we discover that the current Azure SQL Database deployment task is not actually supported for use alongside AAD credentials. This is somewhat confusing, given that it is there as an available option currently. It looks as if this is something that Microsoft is actively looking into but, in the meantime, courtesy of a Stack Overflow answer from Murray Foxcroft, we can instead use a connection string value on the Azure SQL Database deployment task. The steps involved here are pretty straightforward:

1. First, build out your connection string, using the example below as your template; replace any values surrounded by chevrons (<>) with the correct ones for your database/server:
   • Server=.database.windows.net;Initial Catalog=;Persist Security Info=False;User ID=;Password=;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Password
2. Next, on the Variables tab of your pipeline, create a new secret variable called sql-connstring and populate it with the connection string value from the previous step, as indicated below:

   

3. On the Tasks tab, navigate to your Azure SQL Database deployment task, and change the Authentication Type value to Connection String. Then, enter $(sql-connstring) into the Connection String field. It should resemble the screenshot below if done correctly:

   

From there, you should be good to go - connections to the database should authenticate successfully, and any EXTERNAL PROVIDER objects will get created successfully 🙂

It’s bizarre that the current Active Directory - Password option does not work at all, particularly given that its equivalent option for database deployments within Visual Studio works without issue. From the sounds of it, the issue is being actively looked at by Microsoft and we should (hopefully!) have an updated version of the Azure SQL Database deployment task available soon that works correctly. In the meantime, it’s good to know that there is an easily implementable workaround available, that can be secured appropriately via the Azure DevOps Pipelines interface.