Advanced automated deployment of Azure SQL Database with Azure DevOps (part 4 of 4)

johnmccormick99 commented 3 years ago

Hello. We are using your example of SqlAzureDacpacDeployment task with access token. We are using SqlTask rather than dacpacFile. We receive [error]Login failed for the user when running the pipeline. The Azure DevOps Service Connection service principal is a member of the SQL Server AADAdmin group. We can use the access token with success outside of the SqlAzureDacpacDeployment task.

This works

        - task: AzurePowerShell@4
          displayName: 'Get Database Access Token'
          inputs:
            azureSubscription: $(serviceConnection)
            ScriptType: inlineScript
            Inline: |
              $token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
              Write-Host("##vso[task.setvariable variable=accessToken;issecret=true]$token")
            azurePowerShellVersion: 'LatestVersion'

        - task: AzurePowerShell@4
          displayName: 'Query'
          inputs:
            azureSubscription: $(serviceConnection)
            ScriptType: inlineScript
            Inline: |
              Invoke-Sqlcmd -ServerInstance $(sqlServer).database.windows.net -Database $(sqlDatabase) -AccessToken $(accessToken) -query "SELECT 1"
            azurePowerShellVersion: 'LatestVersion'

This does not work

        - task: AzurePowerShell@4
          displayName: 'Get Database Access Token'
          inputs:
            azureSubscription: $(serviceConnection)
            ScriptType: inlineScript
            Inline: |
              $token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
              Write-Host("##vso[task.setvariable variable=accessToken;issecret=true]$token")
            azurePowerShellVersion: 'LatestVersion'

        - task: SqlAzureDacpacDeployment@1
          displayName: 'Execute SQL Migration Script'
          inputs:
            azureSubscription: $(serviceConnection)
            AuthenticationType: 'connectionString'
            ConnectionString: 'Data Source=$(sqlServer).database.windows.net;Initial Catalog=$(sqlDatabase);Encrypt=true;Connect Timeout=60'
            deployType: SqlTask
            SqlFile: '$(Pipeline.Workspace)/DbMigrationScript/DbMigrationScript.sql'
            AdditionalArguments: '/AccessToken:$(accessToken)'

We would appreciate any insights you could share :)

many thanks,

johnnyreilly commented 3 years ago

Just to add to what m'colleague @jmccor99 has said. When we're running SqlAzureDacpacDeployment@1 we can see it's using Invoke-Sqlcmd under the bonnet like so:

Temporary inline SQL file: C:\Users\VssAdministrator\AppData\Local\Temp\tmp604C.tmp
Invoke-Sqlcmd -connectionString "**********"  -Inputfile "C:\Users\VssAdministrator\AppData\Local\Temp\tmp604C.tmp" 
Sql file: D:\a\1\DbMigrationScript\DbMigrationScript.sql
Invoke-Sqlcmd -connectionString "**********"  -Inputfile "D:\a\1\DbMigrationScript\DbMigrationScript.sql"

Interestingly, it doesn't pass through -AccessToken as you might expect / hope. This seems surprising.

We did wonder about directly using Invoke-Sqlcmd itself, since it's being used internally, but didn't know whether SqlAzureDacpacDeployment@1 was bringing anything extra to the party that might be useful.

No worries if you don't know / don't have time; but it you've any pointers to share we'd sure appreciate them! ❤️ 🌻

ErikEJ commented 3 years ago

It is a well known issue with SqlTask, use a standard Azure PowerShell script instead to run .SQL scripts.

Dacpac deploy calls sqlpackage.exe.

ErikEJ commented 3 years ago

SqlAzureDacpacDeployment@1 brings the ability to fix firewall rules, otherwise you have to do it via script.

See https://docs.microsoft.com/en-us/azure/devops/pipelines/targets/azure-sqldb?view=azure-devops&tabs=yaml#sql-scripts

johnnyreilly commented 3 years ago

Thanks for responding @ErikEJ !

It is a well known issue with SqlTask, use a standard Azure PowerShell script instead to run .SQL scripts

Oh right - so DacpacTask can use access tokens, but SqlTask cannot?

Is this the same thing? https://github.com/microsoft/azure-pipelines-tasks/issues/13802

ErikEJ commented 3 years ago

Correct.

johnnyreilly commented 3 years ago

Thanks to @jmccor99 experiments - we've deduced that switching from authenticationType: connectionString to AuthenticationType: 'servicePrincipal' works.

        - task: SqlAzureDacpacDeployment@1
          displayName: 'Execute SQL Migration Script'
          inputs:
            azureSubscription: $(serviceConnection)
            AuthenticationType: 'servicePrincipal'
            ServerName: '$(sqlServer).database.windows.net'
            DatabaseName: '$(sqlDatabase)'
            TaskNameSelector: 'SqlTask'
            SqlFile: '$(Pipeline.Workspace)/DbMigrationScript/DbMigrationScript.sql'
            IpDetectionMethod: 'AutoDetect'

With this mechanism tokens are acquired by the task without needing to be explicitly obtained and supplied:

https://github.com/microsoft/azure-pipelines-tasks/blob/4f1cfb92d79b4b10eadc2417b328c7cd977c06fd/Tasks/SqlAzureDacpacDeploymentV1/DeploySqlAzure.ps1#L100

ErikEJ commented 3 years ago

Excellent!

dipti927 commented 3 years ago

As per the MSDN article https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps , Invoke-SQLCMD supports accesstoken. when trying to execute the same using powershell instead of from automated deployment task, but it is not working

Anyhelp would be appericiated!!!

ErikEJ commented 3 years ago

@dipti927 I think the versions of Invoke-SqlCmd on the DevOps agents does not support accessToken.

You can use System.Data.SqlClient instead.

Function ConnectAndExecuteSql {
            Param
            (
                [string] $sqlServerName,
                [string] $sqlDatabaseName,
                [string] $sqlServerUID = $null,
                [string] $sqlServerPWD = $null,
                [string] $accessToken = $null,
                [string] $Query
            )

          $sqlServerFQN = "$($sqlServerName).database.windows.net"
          $Port = 1433
          if ($sqlServerUID) {
            $ConnectionString = "Server=tcp:$($sqlServerFQN),$Port;Database=$sqlDatabaseName;UID=$sqlServerUID;PWD=$sqlServerPWD;Trusted_Connection=False;Encrypt=True;Connection Timeout=60;"
          } else {
            $ConnectionString = "Server=tcp:$($sqlServerFQN),$Port;Initial Catalog=$($sqlDatabaseName);Persist Security Info=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=60;"         
          }

          $Connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
          if ($AccessToken) {
            $Connection.AccessToken = $AccessToken
          }

          $Connection.Open()

          $sqlCmd = New-Object System.Data.SqlClient.SqlCommand($query, $Connection)
          $sqlCmd.ExecuteNonQuery()

          $Connection.Close()
        }

ErikEJ commented 3 years ago

@dipti927 I just checked, access token works fine with Invoke-SqlCmd in a AzurePowerShell@5 task.

dipti927 commented 3 years ago

@ErikEJ I have tried the same with AzurePowerShell@5 task from DevOps Pipeline but it's not working for me.

Can you just paste sample code for me ?

Regards, Dipti Mamidala

ErikEJ commented 3 years ago

@dipti927 Maybe you should start by sharing what you have tried? How are you capturing the accesstoken?

dipti927 commented 3 years ago

There is powershell task which will retrieve the token and other AzurePowerShell@5 task to execute invoke-sqlcmd with accesstoken

ErikEJ commented 3 years ago

Looks like you have some bad character after "-AccessToken" - also, please share text, not screenshots!

dipti927 commented 3 years ago

Here is the code :

steps:

task: AzurePowerShell@5 displayName: 'Azure PowerShell script: InlineScript' inputs: azureSubscription: 'Service connection name' ScriptType: InlineScript Inline: | $context = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile.DefaultContext $sqlToken = [Microsoft.Azure.Commands.Common.Authentication.AzureSession]::Instance.AuthenticationFactory.Authenticate($context.Account, $context.Environment, $context.Tenant.Id.ToString(), $null, [Microsoft.Azure.Commands.Common.Authentication.ShowDialog]::Never, $null, "https://database.windows.net").AccessToken Write-Host ("##vso[task.setvariable variable=SQLTOKEN;]$sqlToken") azurePowerShellVersion: LatestVersion
- task: AzurePowerShell@5 displayName: 'Azure PowerShell script: Execute Script' inputs: azureSubscription: 'service connection' ScriptType: InlineScript Inline: | Import-Module SQLServer Import-Module Az.Accounts -MinimumVersion 2.2.0
  
  Invoke-Sqlcmd -ServerInstance {servername} -Database {dbname} -AccessToken $(sqlToken) -query 'select * from abc.dataset' azurePowerShellVersion: LatestVersion

ErikEJ commented 3 years ago

Try the code I suggested here: https://github.com/ErikEJ/erikej.github.io/issues/29#issuecomment-805588390 - maybe I was wrong about it working...

dipti927 commented 3 years ago

The above code which you suggested is working but we would like to execute sql file which contains list of sql commands

ErikEJ commented 3 years ago

I suggest you ask Azure DevOps support.

kotsaris commented 1 year ago

Hi Erik

Very useful stuff.

Any ideas if one can use efbundle.exe that is generated by entity framework tooling to do the same thing? Can't see a parameter for accesstoken so I am wondering if access token can be included in the connection string or something.

Thanks

ErikEJ commented 1 year ago

@kotsaris Very interesting question!

Maybe you can update to SqlClient 4.1 (or at least 3.0 or later) and use Active Directory Default - if your pipeline account does not have access, then Environment credential looks promising.

weifeng9 commented 7 months ago

there is no database name in the connect string, is it because the sqlToken know which is target db? Will the database.dacpac deploy to all DBs?

ErikEJ commented 7 months ago

@weifeng9 "Initial Catalog" is the database name!

ErikEJ commented 6 months ago

@johnnyreilly @johnmccormick99

I finally figured your initial issue out, I think you just need to change this one parameter:

SqlAdditionalArguments: '-AccessToken $(accessToken)'

See https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

and https://learn.microsoft.com/en-us/azure/devops/pipelines/tasks/reference/sql-azure-dacpac-deployment-v1?view=azure-pipelines

ErikEJ commented 6 months ago

Full example of script file deployment with a collected access token:

 - task: SqlAzureDacpacDeployment@1
   displayName: 'Execute SQL Script'
   inputs:
     azureSubscription: ${{ parameters.serviceConnection }}
     AuthenticationType: 'connectionString'
     ConnectionString: 'Data Source=$(sqlServer).database.windows.net;Initial Catalog=$(sqlDatabase);Encrypt=true;Connect Timeout=60'
     deployType: SqlTask
     SqlFile: '$(Pipeline.Workspace)/DbScript.sql'
     SqlAdditionalArguments: '-AccessToken $(sqlAccessToken)'

ErikEJ / erikej.github.io

Advanced automated deployment of Azure SQL Database with Azure DevOps (part 4 of 4) #29