Issues Connecting to Serverless SQL Pool in Azure DevOps Pipeline

Question

Encountering connection issues while deploying a DacPac SQL database project to the serverless pool of Azure Synapse via a DevOps pipeline. The authentication method being used is a service principal, which has already been registered in the Synapse workspace. The build process completes successfully, but the deployment step fails with the following error logs:

##[error]*** Could not deploy package.

##[error]Unable to connect to target server 'XXXX-ondemand.sql.azuresynapse.net'. Please verify the connection information such as the server name, login credentials, and firewall rules for the target server.

##[error]Login failed for user '<token-identified principal>'.

##[error]The Azure SQL DACPAC task failed. SqlPackage.exe exited with code 1. Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-

The deployment job defined in env.yml is as follows:

- deployment: Deploy_SSP_DATAMODEL
    displayName: Deploy SSP DataModel
    environment: Env-${{ parameters.projectName }}-${{ parameters.envName }}
    strategy:
      runOnce:
        deploy:
          steps:
          - task: SqlAzureDacpacDeployment@1
            displayName: Deploy DacPac
            inputs:
              azureSubscription: '${{ parameters.azureSubscriptionName }}'
              AuthenticationType: 'servicePrincipal'
              ServerName: '${{ parameters.azureServerlessSvrName }}'
              DatabaseName: '${{ parameters.dbServerlessName }}'
              deployType: 'DacpacTask'
              DeploymentAction: 'Publish'
              DacpacFile: '$(Pipeline.Workspace)\db\source\${{ parameters.projectName }}_SSP\bin\${{ parameters.buildConfiguration}} \${{ parameters.projectName }}_SSP.dacpac'

What steps should be taken to resolve the connection issue with the serverless SQL pool? What specific configurations should be checked? It is worth noting that the same pipeline successfully deploys to the dedicated SQL pool.

Answer 1

Hello

You're encountering the error "Login failed for user '<token-identified principal>'" when deploying a DACPAC to an Azure Synapse serverless SQL pool via Azure DevOps using a service principal. This issue typically arises due to authentication or permission configurations specific to Azure Synapse serverless pools.

The error message indicates that the service principal lacks the necessary permissions to authenticate against the serverless SQL pool. In Azure Synapse, serverless SQL pools require that any Azure Active Directory (Azure AD) principal, including service principals, be explicitly created within the SQL pool before they can authenticate and perform operations.

Recommended Steps to Resolve

1. Create the Service Principal User in the Serverless SQL Pool Connect to the serverless SQL pool using an account with administrative privileges and execute the following T-SQL command to create a user for the service principal:

      
      CREATE USER [<service-principal-name>] FROM EXTERNAL PROVIDER;
   

Replace <service-principal-name> with the display name of your service principal.

1. Assign Appropriate Roles to the Service Principal After creating the user, grant the necessary permissions by adding the user to appropriate database roles. For example:

      
      ALTER ROLE db_owner ADD MEMBER [<service-principal-name>];
   

Adjust the role (db_owner in this example) based on the level of access required for your deployment.

1. Verify Azure AD Admin Configuration Ensure that an Azure AD admin is configured for your Synapse workspace. This is necessary to manage Azure AD users and service principals within the SQL pool. Check Firewall and Network Settings Confirm that your Azure DevOps environment can connect to the serverless SQL pool. Ensure that firewall rules allow access from Azure services, or specifically from the IP ranges used by Azure DevOps.
2. Review Token Expiry Settings Service principal tokens have a default expiration time (typically 1 hour). If your deployment process takes longer, consider refreshing the token or adjusting the token lifetime

Answer 2

Hi Abdelmoughit Bouddine

The core reason your Azure DevOps pipeline fails when trying to deploy a DACPAC to the Synapse Serverless SQL Pool is very simple Serverless SQL Pools are not "real databases" that hold schema objects.

They are designed to query data directly from your data lake (ADLS) not to store tables, views, stored procedures or any DDL (schema).

So, when your DACPAC deployment tries to "install" schema objects like tables, views, procedures they I only let you run queries against files like Parquet, CSV, JSON sitting in your lake.

You can run T-SQL queries like this (on-demand, no storage)

SELECT  *  FROM OPENROWSET(
    BULK 'https://<storageaccount>.dfs.core.windows.net/<container>/file.parquet',
    FORMAT =  'PARQUET' ) AS  rows;

You can also create views or external tables to simplify these queries:

CREATE  VIEW myview AS  SELECT  *  FROM OPENROWSET(...);

These can be deployed via T-SQL scripts but not via DACPAC files.

Why is DACPAC Deployment Not Supported because DACPAC means "deploy this schema into the database" But Serverless SQL has no schema store. It does not keep tables or indexes. Everything comes from your lake files. Microsoft’s own documentation states this clearly

Click Here to get the MS-Doc

But that login error 'token-identified principal' happened because your Service Principal is not created in the Serverless SQL pool yet.

To fix the login error (if you plan to run queries or manage views manually), you must run this once:

CREATE  USER [your-service-principal-name] FROM  EXTERNAL PROVIDER; ALTER ROLE db_owner ADD  MEMBER [your-service-principal-name]; -- if high permission is needed

But this only lets you connect and query, you still cannot deploy a DACPAC after that.

To fix:

Option 1: Use Dedicated SQL Pool if DACPAC Deployment Is Truly Needed

If your solution must deploy database schema objects via DACPAC:

Provision a Dedicated SQL Pool in Synapse:

az synapse sql pool create \
  --name MyDedicatedPool \
  --workspace-name MyWorkspace \
  --performance-level DW100c \
  --resource-group MyResourceGroup 

Change your pipeline to target Dedicated Pool endpoint:

`ServerName:  '<workspace>.sql.azuresynapse.net'  DatabaseName:  'MyDedicatedPool'` 

DACPAC deployment will succeed here because Dedicated Pools store schema.

Option 2: Remove DACPAC Step Use T-SQL Script for Serverless Pool Instead

If you're sticking with Serverless SQL Pool, you must remove DACPAC deployment task from the pipeline.

Instead, deploy plain .sql scripts that create views or external tables:

CREATE  VIEW MyExternalView AS  SELECT  *  FROM OPENROWSET(
    BULK 'https://mystorage.dfs.core.windows.net/container/data.parquet',
    FORMAT =  'PARQUET' ) AS  rows; 

Your DevOps pipeline can run these using Azure CLI or SQLCmd:

- task: AzureCLI@2
  inputs:
    azureSubscription: '<Service Connection>'
    scriptType: 'bash'
    scriptLocation: 'inlineScript'
    inlineScript: |
      sqlcmd -S <serverless-sql-endpoint> -d <db-name> -G -U <service-principal> -P <password> -i create-views.sql

Option 3: Skip DACPAC Deployment in Pipeline for Serverless Targets

For example, in YAML:

- task: SqlAzureDacpacDeployment@1
  condition: and(succeeded(), ne(variables['targetSqlPoolType'], 'Serverless'))

This way DACPAC deploys only if Dedicated Pool is used.

If you really need DACOAC & schema, use Dedicated SQL pool, if you only plan to query stay serverless but remove DACPAC deploy step and deploy simple .sql views or external tables.

---

I hope this has been helpful! If above is unclear and/or you are unsure about something add a comment below.

Please click the answer as original posters help the community find answers faster by identifying the correct answer.