CODEWITHSUNDEEP
sql-servermsbuildteamcity
JamieS
JamieS

Reputation: 307

TeamCity SQL Deploy

I followed the guide at http://blogs.adatis.co.uk/jonathon%20eveoconnor/post/Using-TeamCity-to-Automate-Building-and-Deployment-of-Database-Projects to set up SQL build and publish from TeamCity.

The build step works great, but I'm stuck when it comes to publishing. I've set the step up as below, which matches what is in the guide, but I'm getting an error.

Step 2/2: Publish (MSBuild) (2s)

[Step 2/2] KAInternal\KAInternal.sqlproj.teamcity: Build target: Publish

[KAInternal\KAInternal.sqlproj.teamcity] SqlPublish

[SqlPublish] SqlPublishTask

[SqlPublishTask] C:\TeamCity\buildAgent\work\1472f1322571ddd\KAInternal\bin\Debug\KAInternal.sqlproj.publish.sql error Deploy72002: Unable to connect to master or target server 'KAInternal'. You must have a user with the same password in master or target server 'KAInternal'. [Step 2/2] Step Publish (MSBuild) failed

I've searched a fair bit, but everyone seems to believe this is a permission issue on the source machine, well my publish profile is using the built in "sa" SQL Server account to publish (for now, mainly to disprove this theory).

Here is the publish profile XML:

<Project ToolsVersion="14.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
  <IncludeCompositeObjects>True</IncludeCompositeObjects>
  <TargetDatabaseName>KAInternal</TargetDatabaseName>
  <DeployScriptFileName>Staging_KAInternal.sql</DeployScriptFileName>
  <TargetConnectionString>Data Source=192.168.194.6;Persist Security Info=False;User ID=sa;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True</TargetConnectionString>
  <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
  <ExcludeLogins>True</ExcludeLogins>
  <IgnoreRoleMembership>True</IgnoreRoleMembership>
  <ExcludeUsers>True</ExcludeUsers>
  <ProfileVersionNumber>1</ProfileVersionNumber>
</PropertyGroup>

And the publish step in TeamCity

Publish Config

Hopefully someone can point me in the right direction, thanks in advance.

Upvotes: 2

Views: 903

Answers (1)

Steven Green
Steven Green

Reputation: 3517

The guide you're following describes creating a separate proj file for the deployment, but here you're using the sqlproj file instead. There actually is an msbuild publish target for sqlproj files, so I'm not sure whether this was an intentional deviation on your part.

It may be the case that the sqlproj build task doesn't accept the password as an msbuild property. You could test whether this is the problem by specifying the password in the connection string in the publish profile file.

Also, I would suggest ensuring that you're using the latest versions of SSDT and DACFx. See the SSDT team blog for information about the latest versions and download links: https://blogs.msdn.microsoft.com/ssdt/

Upvotes: 0

Related Questions