SqlAzureDacpacDeployment - Login failed for user '<token-identified principal>'. Token is expired

Using a dacpac deployment like so from Azure Devops pipeline.

- task: SqlAzureDacpacDeployment@1
  inputs:
    azureSubscription: 'subscription id'
    AuthenticationType: 'servicePrincipal'
    ServerName: 'server name value'
    DatabaseName: 'db name value'
    deployType: 'DacpacTask'
    DeploymentAction: 'Publish'
    DacpacFile: 'dacpac_file_name.dacpac'
    AdditionalArguments: '/p:BlockOnPossibleDataLoss=false /p:VerifyDeployment=false /p:ignorePermissions=true'

It works fine, runs and does its thing but after about an hour the task errors out with message:

Login failed for user ''. Token is expired

This is a bit random, but it looks like I am getting these errors only during long running jobs. For anything shorter it works fine and there's no problem. Any advice?

It works fine, runs and does its thing but after about an hour the task errors out with message: Login failed for user ''. Token is expired

Unfortunately, To increase security and avoid data theft. By default the Azure AD application's Token Expiry is 1 hr. In order to use the Token again you need to refresh it by authenticating with the Azure SQL Server again.

Reference:- Configurable token lifetimes - Microsoft Entra | Microsoft Learn

As, An Alternative you can increase the Access token lifetime of your azure ad app to maximum 24 hours by running the Powershell command below:-

$policy = New-AzureADPolicy -Definition @('{"TokenLifetimePolicy":{"Version":1,"AccessTokenLifetime":"23:59:59"}}')  -DisplayName "WebPolicyScenario"  -IsOrganizationDefault $true  -Type  "TokenLifetimePolicy"  $sp = Get-AzureADServicePrincipal -Filter  "DisplayName eq '<service principal display name>'" Add-AzureADServicePrincipalPolicy -Id $sp.ObjectId -RefObjectId $policy.Id

Output:-

Make sure you Install AzureADPreview module in your Powershell for the AzureAD Policy to work.

Install-Module -Name AzureADPreview
Remove-Module AzureAD
Import-module AzureADPreview

As an alternative, You can use SQL Authentication or Azure AD Based authentication to avoid token expiry as you can still use your Azure DevOps service connection to authenticate with your AzureSQLdacpac Deployment task. Refer below:-

SQL Authentication:-

# Starter pipeline

# Start with a minimal pipeline that you can customize to build and deploy your code.

# Add steps that build, run tests, deploy, and more:

# https://aka.ms/yaml

  

trigger:

- main

  

pool:

vmImage: windows-latest

  

steps:

- script: echo Hello, world!

displayName: 'Run a one-line script'

  

- script: |

echo Add other tasks to build, test, and deploy your project.

echo See https://aka.ms/yaml

displayName: 'Run a multi-line script'

  

- task: SqlAzureDacpacDeployment@1

inputs:

azureSubscription: 'AzureSQL987'

AuthenticationType: 'server'

ServerName: '<azuresqlserver>.database.windows.net, 1433'

DatabaseName: 'silicondb987'

SqlUsername: '<username>'

SqlPassword: '<password>'

deployType: 'DacpacTask'

DeploymentAction: 'Publish'

DacpacFile: '$(Build.SourcesDirectory)\silicondb987.dacpac'

IpDetectionMethod: 'IPAddressRange'

StartIpAddress: '0.0.0.0'

EndIpAddress: '255.255.255.255'

DeleteFirewallRule: false

Output:-

Or you can also use Azure AD Authentication by adding Azure AD User as Admin:-

trigger:

- main

  

pool:

vmImage: windows-latest

  

steps:

- script: echo Hello, world!

displayName: 'Run a one-line script'

  

- script: |

echo Add other tasks to build, test, and deploy your project.

echo See https://aka.ms/yaml

displayName: 'Run a multi-line script'

  

- task: SqlAzureDacpacDeployment@1

inputs:

azureSubscription: 'AzureSQL987'

AuthenticationType: 'aadAuthenticationPassword'

ServerName: 'siliconserver654.database.windows.net, 1433'

DatabaseName: 'silicondb987'

aadSqlUsername: '<azureaduser>.onmicrosoft.com'

aadSqlPassword: '<azureaduserpassword>'

deployType: 'DacpacTask'

DeploymentAction: 'Publish'

DacpacFile: '$(Build.SourcesDirectory)\silicondb987.dacpac'

IpDetectionMethod: 'IPAddressRange'

StartIpAddress: '0.0.0.0'

EndIpAddress: '255.255.255.255'

DeleteFirewallRule: false

Output:-

By using Username and password whether SQL authentication or Azure AD authentication, You won't get any limitations like token expiry. Or you need to generate new access token by authenticating again with your service principal.

Reference:-

Deploying a DacPac to Azure SQL with Azure Pipelines and Managed Identities | by Christopher Derrig | Microsoft Azure | Medium

Get-AzureADPolicy is not working under version Version 2.0.2.138 - Microsoft Q&A By Vasil Michev