Deploying Dacpac's via Powershell using Service Principals

[simondmorias]

Hi,

I'm trying to deploy a dacpac to Azure SQL, I would like to deploy it using a service principal and PowerShell. But I cannot figure out how the connection string should work.

Using a SQL Login I would do something like this:

$dacServices = New-Object Microsoft.SqlServer.Dac.DacServices $ConnectionString

Where the connection string container the login and password.

Looking at the constructors for DacServices here there is a IUniversalAuthProvider which I think should be able to do what I want. But I cannot figure out how to construct it in PowerShell. I was expecting something like:

$Auth = New-Object Microsoft.SqlServer.Dac.IUniversalAuthProvider $AccessToken
$dacServices = New-Object Microsoft.SqlServer.Dac.DacServices $ConnectionString $Auth

Where Access Token is obtained from AAD via a Token request.

Another example I can find is in the Azure DevOps tasks for the Dacpac deploy here.

In Utility.ps1 there is a function called Get-AADAuthenticationConnectionString - but this seems to only work with AAD credentials (and not 2FA).

Is this possible?

[kevcunnane]

That likely won't work as you can't new up an interface (IUniversalAuthProvider). I know this should work with SqlPackage (use the /AccessToken: input), but if you want to do this yourself you'd need a class defined that implements that interface's GetValidAccessToken method and returns your token. That's likely what SqlPackage is doing as it uses this same interface. It's unfortunate that a SimpleTokenAuthProvider class doesn't exist already - @pensivebrian or @kenvanhyning maybe you can comment? Doesn't seem like Udeesha / Kim are part of this repo.

[pensivebrian]

With powershell 5, you can implement an interface, so something like the following (untested) code should work:

$server=
$database=
$accessToken=

Add-Type -Path 'C:\Program Files\Microsoft SQL Server\150\DAC\bin\Microsoft.SqlServer.Dac.dll'

Invoke-Expression @'
class AuthProvider : Microsoft.SqlServer.Dac.IUniversalAuthProvider {
  AuthProvider([string] $token) { $this.t = $token}
  [String] $t;
  [bool]IsTokenExpired()  { return $false }
  [string]GetValidAccessToken() { return $this.t }
}
'@

$authProvider = new-object AuthProvider $accessToken
$dacServices = new-object Microsoft.SqlServer.Dac.DacServices "server=$server;database=$database", $authProvider

[simondmorias]

Thanks @pensivebrian - works a treat!