Deploying a Dacpac using SQLPackage

Question

Hi,&nbsp;I'm trying to use SQLPackage to deploy a Dacpac using AccessToken authentication.&nbsp;I'm using the following command....\sqlpackage.exe /Action:Publish /SourceFile:Database.dacpac /TargetServerName:xxxx.database.windows.net /TargetDatabaseName:DatabaseName /AccessToken:xxxx&nbsp;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 '&lt;token-identified principal&gt;'.&nbsp;Any ideas what's wrong here?&nbsp;

aussierobsql · Answer

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

Forum Discussion

Deploying a Dacpac using SQLPackage

Resources