Deploying a Dacpac using SQLPackage

Copper Contributor

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?

 

1 Reply
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...

-rob