Update:

Refer to this post instead

Hello!

One of the things I feel strongly about is that a build/release pipeline needs to be as complete as possible - that is all configuration required to complete the build/deploy process needs to be automated in some way or another. And as we will see from todays post, this tends to create further complexity, and unfortunately sometimes we have to get inventive for a process to work…

As part of an SSDT project we have a contained user that authenticates against an Azure Active Directory group (read more on the CREATE USER page). However the account we are executing deployments with is the SQL Admin account on the Azure SQL Instance. And so we get this error -

The executed script:
CREATE USER [myUser] FOR EXTERNAL PROVIDER;
' Reason: ''
At line:94 char:13
+             Throw $toThrow
+             ~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (Deployment fail...
' Reason: '':String) [], RuntimeException
    + FullyQualifiedErrorId : Deployment failed: 'Could not deploy package.
    Error SQL72014: .Net SqlClient Data Provider: Msg 33159, Level 16, State 1, Line 1 Principal 'myUser' could not be  
   created. Only connections established with Active Directory accounts can create other Active Directory users.
Error SQL72045: Script execution error. 

All this means we need to use an AD authenticated user to deploy database scripts. Simple enough, we’ll alter our ARM template to deploy an Active Directory Administrator and use that account instead. If you’re interested in how to add an AD Admin via ARM then this page on StackOverflow will show you how. If you don’t know what an Active Directory Administrator is for SQL Azure then this page on GitHub will help.

Now that we have added an Active Directory as admin on the server, we can alter our SSDT connection string to use “Active Directory Password” authentication, meaning our string sort of looks like the one below -

$aadConnectionString = "Server=tcp:$($AzureSqlFQDN),1433;Initial Catalog=myLittleDatabbase;Persist Security Info=False;User ID=$($aadAdminUser);Password=$($aadAdminPassword);MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=""Active Directory Password"";"

This should work and all is good with the world. However, when deploying using PoshSSDTBuildDeploy, I get the funniest of errors, only I’m not laughing:

Executing Deployment...
Initializing deployment (Start)
Unable to load adalsql.dll (Authentication=ActiveDirectoryPassword). Error code: 0x2. For more information, see http://go.microsoft.com/fwlink/?LinkID=513072
Initializing deployment (Failed)
Deployment failed: 'Could not deploy package.' Reason: 'Unable to connect to target server
'tcp:redacted.database.windows.net,1433'. Please verify the connection information such as the server name, login
credentials, and firewall rules for the target server.'

Apparently adalsql.dll is not loading. Looking through the GAC I see that it is installed on the machine running the deployment. And also via Control Panel: if you are looking for it yourself it is called something like “Active Directory Authentication Library for SQL Server”.

OK, long story short, this is installed as part of SQL Server Management Studio. Only the version packaged with SSMS does not work with DacFX - you get the error above. What you have to do is uninstall the latest version and install 13.0.300.275 for deployments via DacFX to work successfully. The version number of the one that comes with SSMS escapes me, but it is 14.something-or-other.

What makes this slightly more complex is that in a pipeline, you need to be sure that the machine you are deploying from has the correct version of the adalsql.dll on it. So I’ve written this function, which is frankly horrifying, as it uninstalls the version we don’t want using the pertinent GUID I found in the registry, then downloads and install the version we need to do a deploy. Microsoft have been alerted to the issue, and hopefully it will be resolved soon.