Deploy SSDT Project using Jenkins CI and SqlPackage command error

Question

I am using the following:

Local Dev Desktop:
Windows 7 Enterprise
Visual Studio 2015 Professional with Update 2
SSDT for Visual Studio 2015

Remote Database Server:
Windows Server 2012
SQL Server 2012

Jenkins CI Desktop Server:
Windows 7 Enterprise
Jenkins v1.580.1
SSDT for Visual Studio 2015

I have create a SQL Database project. I am trying to get Jenkins to build and deploy this to my integration testing server.

The build is working, but the deployment is not.

I have created an Execute Windows Batch Command build step with the following details:

"C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\sqlpackage.exe"
/a:publish
/sf:.\ddu.dev.cg\bin\Debug\ddu.dev.cg.dacpac
/pr:.\ddu.dev.cg\ddu.sit.publish.xml

When I run the build I get the following in the console output:

Publishing to database 'xxxx' on server 'xxxx'.
Initializing deployment (Start)
Initializing deployment (Failed)
*** Could not deploy package.
Unable to connect to master or target server 'xxxx'. You must have a user with the same password in master or target server 'xxxx'.

I have used this article to help me, however this uses windows authentication and I need to use SQL authentication because my remote database server sits in a different physical domain to to my Jenkins server (let's not get into that...)

I have done the below:

Created a Windows User Account for Jenkins on the Jenkins server and assigned it to the Jenkins service
Created a Login for Jenkins in SQL Server on my database server that uses the same password as the windows account created in step 1.

We had something like that, but were able to set up "child" Jenkins servers in each domain. The master Jenkins box controlled the builds/releases, but each child belonged to the domain and was set up appropriately within each domain to do builds there. Is that an option for you? Might be better in the long run for more than just SQL releases. — Peter Schott
– Peter Schott, Commented Jun 17, 2016 at 12:40

acg · Accepted Answer · 2016-06-28 18:41:15Z

1

Just had the same problem with TeamCity, stored profiles don't persist the passwords, just pass the password as a param: /TargetPassword:p@ssw0rd

answered Jun 28, 2016 at 18:41

acg

9615 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sport · Accepted Answer · 2017-01-20 10:42:40Z

0

We had a similar problem and found out that it had to do with different versions of the DAC-service (SqlPackage), the target database and the Target Platform stated in the SSDT project in Visual Studio.

It looks like you are using the version of sqlpackage for SQL Server 2016 (../130/..) with a target database beeing SQL Server 2012.

So you could try either to use the dacpackage under /110/ folder (for SQL Server 2012) and make sure you have the right target platform. Or you could add a the parameter: AllowIncompatiblePlatform (/p: AllowIncompatiblePlatform=true) to you publish command

edited Jan 20, 2017 at 10:42

Sport

9,0136 gold badges50 silver badges67 bronze badges

answered Jan 20, 2017 at 10:37

ChopperPer

11 bronze badge

Collectives™ on Stack Overflow

Deploy SSDT Project using Jenkins CI and SqlPackage command error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related