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