Forum Discussion

Noddy285's avatar
Noddy285
Copper Contributor
Mar 02, 2021

Deploying a Dacpac using SQLPackage

Hi,

 

I'm trying to use SQLPackage to deploy a Dacpac using AccessToken authentication.

 

I'm using the following command...

.\sqlpackage.exe /Action:Publish /SourceFile:Database.dacpac /TargetServerName:xxxx.database.windows.net /TargetDatabaseName:DatabaseName /AccessToken:xxxx

 

The error message I'm getting is:

Unable to connect to target server 'xxxx.database.windows.net'. Please verify the connection information such as the server name, login credentials, and firewall rules for the target server.
Login failed for user '<token-identified principal>'.

 

Any ideas what's wrong here?

 

azure sql
azure sql database
development
sql server

1 Reply

  • AussieRobSql's avatar
    AussieRobSql
    Copper Contributor
    Mar 06, 2021
    Hi.
    I just ran into a similar issue.
    Firstly, grab the access token and decode it (somewhere like jwt.io does it client side)
    Check that the payload has the following.
    "aud": "https://database.windows.net/"

    If this is correct, check the user that the token is for is in a user or group that has access to the SQL Server. This can be via the Active Directory Admin setting on the Azure SQL Server, or a contained user on the SQL Database.
    If you're using an SPN / Managed identity, it will need to be in an Azure AD Group, and added using the syntax here.
    https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver15#j-create-an-azure-ad-user-without-an-aad-login-for-the-database

    -rob

Resources