Sqlpackage.exe does not work with Windows Authentication

Hello,

we are currently evaluating Octopus Deploy.

We want to use Octopus to deploy DacPacs against our SQL Server.
Therefore we want reuse our current powershell Scripts in the first place.
The Powershell Script executes:

.\bin\SqlPackage.exe /a:publish /p:CreateNewDatabase=False /p:VerifyDeployment=True /sf:$dacpacFile /pr:$publishFile /tcs:“Data Source=$hostname;Integrated Security=True;Pooling=False” /v:CertificatePassword=$pass0 /v:SigningCertificatePasswordRouter=$pass1
(Windows Authentication)

Our Octopus Server runs as a Service with an ActiveDirectory Account (Not Local Service)
This Active Directory Account is in a AD group which has access to the databases.

If we execute the Deployment it fails with the reason:

*** Could not deploy package.
June 8th 2021 13:21:08
Error
Unable to connect to master or target server ****. You must have a user with the same password in master or target server ***.

SQL Server Error Log shows:

08 Jun 2021 13:21 Logon Login failed for user domain\user. Reason: Could not find a login matching the name provided. [CLIENT: xxx.xxx.xx.xx] Show less

If we grant Login and Access to the databases to the user explictly and not just to the group the deployment works correctly.

Do you have any suggestion why it is not enough to grant login to the group?
If i login on the Octopus Server and open a Powershell by “Run as Different user” with the Octopus user provided at the WIndows Service and execute the Powershell script it works without adding the Login to SQL Server.

Kind Regards,

Jens

Hey @Jens.Nintemann,

Thanks for reaching out.

I see you also have a ticket open via email, so we will continue to work the issue there. Please let me know if you would rather move the conversation here instead.

Best,
Jeremy

Hi Jeremy,

thanks for your reply. We can continue our conversation via e-mail.

Regards,

Jens