Automated Deployment of Azure SQL Database/ Azure SQL Data warehouse through Azure Devops via Service Principal – Part 2

Related: 1|2

Problem Statement :

Is it possible to publish DACPAC on Azure SQL Database or Azure SQL Data Warehouse via Service Principal rather than SQL Account for better Security Standards.

Prerequisites :

1. Service Principal

How to Create a Service Principal

2. Azure SQL Server with Active Directory enabled

Solution :

1. Login into the Azure SQL Server via an AD admin account and execute the below set of queries in the Master database.

CREATE USER [<ServicePrincipalName>] FROM EXTERNAL PROVIDER ;

exec sp_addrolemember ‘dbmanager’, ‘<ServicePrincipalName>’ ;

exec sp_addrolemember ‘loginmanager’, ‘<ServicePrincipalName>’ ;

Note : The above queries can be executed only when logged in via an AD admin account and not via any SQL account logged in session.

2. Create a Release Pipeline in Azure Pipelines and within the Release Pipeline , create the below set of Pipeline Variables.

Release Pipeline > Variables > Pipeline Variables > + Add

a) TenantId : This is the TenantId of the Service Principal (The same tenant in which the SQL Server is present)

b) ClientId : This is the ClientId of the Service Principal

c) ClientSecret :This is the Client Secret of the Service Principal. Make the Variable as type ‘Secret’ to secure the value in the Pipeline Variable group

d) SQLAccessToken : This is blank variable which would be used to preserve the SQL token generated during job execution.

3. Select Azure PowerShell task and replace the Inline Script tab with the below code to generate the SQL Access token and assign it to the Pipeline Variable: SQLAccessToken.

$TenantID = ‘$(TenantId)’
$clientID = ‘$(ClientId)’
$clientSct = ‘$(ClientSecret)’
$resourceAppIdURI = ‘https://database.windows.net/&#8217;

$tokenResponse = Invoke-RestMethod -Method Post -UseBasicParsing -Uri "https://login.windows.net/$($TenantID)/oauth2/token"
-Body @{
resource=$resourceAppIdURI
client_id=$clientID
grant_type=’client_credentials’
client_secret=$clientSct
} -ContentType ‘application/x-www-form-urlencoded’

if ($tokenResponse) {
Write-debug “Access token type is $($tokenResponse.token_type), expires $($tokenResponse.expires_on)”
$Token = $tokenResponse.access_token
}

write-host “##vso[task.setvariable variable=SQLAccessToken]$Token”

YAML Script :

steps:

• task: AzurePowerShell@5
  displayName: ‘Generate SQLAccessToken’
  inputs:
  azureSubscription: ‘<<Subscription>>’
  ScriptType: InlineScript
  Inline: |
  $TenantID = ‘$(TenantId)’
  $clientID = ‘$(ClientId)’
  $clientSct = ‘$(ClientSecret)’
  $resourceAppIdURI = ‘https://database.windows.net/&#8217; $tokenResponse = Invoke-RestMethod -Method Post -UseBasicParsing -Uri "https://login.windows.net/$($TenantID)/oauth2/token"
  -Body @{
  resource=$resourceAppIdURI
  client_id=$clientID
  grant_type=’client_credentials’
  client_secret=$clientSct
  } -ContentType ‘application/x-www-form-urlencoded’ if ($tokenResponse) {
  Write-debug “Access token type is $($tokenResponse.token_type), expires $($tokenResponse.expires_on)”
  $Token = $tokenResponse.access_token
  } write-host “##vso[task.setvariable variable=SQLAccessToken]$Token” azurePowerShellVersion: LatestVersion

4. Select Azure SQL Database Deployment Task with Authentication type as ‘Connection String‘

Connection String : Server=<<ServerName>> ; Initial Catalog=<<DatabaseName>> ; Encrypt=True ; TrustServerCertificate=False ; Connection Timeout=30

Additional SqlPackage.exe Arguments : /AccessToken:$(SQLAccessToken)

YAML :

steps:

• task: SqlAzureDacpacDeployment@1
  displayName: ‘Azure SQL DacpacTask’
  inputs:
  azureSubscription: ‘<>’
  AuthenticationType: connectionString
  ConnectionString: ‘Server=<>;Initial Catalog=<>;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30’
  DacpacFile: ‘$(System.DefaultWorkingDirectory)/Dev/Dev/DB/DataSharkx/bin/Debug/DataSharkx.dacpac’
  AdditionalArguments: ‘/AccessToken:$(SQLAccessToken)’

There are multiple Authentication types supported for Azure SQL Database deployment but Service Principal is a much secure way as compared to some other authentication types.