CODEWITHSUNDEEP
jenkinssql-server-2012sql-server-data-tools
GreenyMcDuff
GreenyMcDuff

Reputation: 3622

Deploy SSDT Project using Jenkins CI and SqlPackage command error

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:

  1. Created a Windows User Account for Jenkins on the Jenkins server and assigned it to the Jenkins service
  2. 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.

Upvotes: 0

Views: 2625

Answers (2)

ChopperPer
ChopperPer

Reputation: 1

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

Upvotes: 0

acg
acg

Reputation: 961

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

Upvotes: 1

Related Questions