Blogs Banner
Changing the Connection String During TFS Build for the Publish Profile of the SSDT (SQL Server Data Tool)

Changing the Connection String During TFS Build for the Publish Profile of the SSDT (SQL Server Data Tool)

23rd Jan, 2017

One of our customers needed to do so and were required to type the password for the SQL login inside the publish profile but of course this was not possible due to the obvious security reasons, we solved this during the TFS build by following the steps below:

 

  • Create a PowerShell script that replaces the connection string
  • Check-In the script into source control
  • Execute the PowerShell script during the build and before compiling the code

 

Create a PowerShell script that replace the connection string

 

The idea is to create a PowerShell script to search for the publish profile in the build directory on the build machine and then, replace the connection string with the one with the password. It is worth noting that we need to do that before compiling the SSDT, so whilst we are running the SSDT deployment, it will use the modified file.

 

During the build I will send $pathToSearch which will be the build directory,$publishProfileString which will be set as argument in the build definition and so $sqlUserName and $sqlPassword.

 

[code language=”PowerShell”]
Param(
[string]$pathToSearch,
[string]$publishProfileString,
[string]$sqlUserName,
[string]$sqlPassword
)
try
{
#$pathToSearch=”C:Radwan”
#$publishProfileString = “/p:SqlPublishProfilePath=../DAI.Infrastructure.DataAccess.Ef.DbPublishProfiles/DAI.Infrastructure.DataAccess.Ef.Db.preprod.publish.xml”
#$sqlUserName=”vvvvvvvv”
#$sqlPassword=”00000000″
$publishPostion = $publishProfileString.LastIndexOf(“publish.”);
$publishStringWihoutPublish = $publishProfileString.Substring(0, $publishPostion – 1);
$lastDotPostion = $publishStringWihoutPublish.LastIndexOf(“.”);
$publishProfileStageName = $publishStringWihoutPublish.Substring($lastDotPostion+1, $publishStringWihoutPublish.Length – $lastDotPostion-1);
$searchFilter = “*.” + $publishProfileStageName + “.publish.*”
$replaceValue=”User ID=” + $sqlUserName +”;password=”+$sqlPassword+”;”
gci -Path $pathToSearch -Filter $searchFilter -Recurse | %{
Write-Host ” -> Changing $($_.FullName)”
# remove the read-only bit on the file
sp $_.FullName IsReadOnly $false
# read the file content
$results = Get-Content $_.PSPath
Write-Host ” -> File content: $($results)”
# run the regex replace
#(gc $_.FullName) | % { $_ -replace ‘ID=user;’, “User;’, “User ID=””$sqlUserName””;password=””$sqlPassword””;” }| sc $_.FullName
(gc $_.FullName) | % { $_ -replace ‘User ID=user;’, $replaceValue }| sc $_.FullName

Write-Host “Done!”

}
}
catch {
Write-Host $_
exit 1
}
[/code]

 

Check-In the script into source control

 

This allows the script to be downloaded during the build, as I will need to run it from the build machine

 

Execute the PowerShell script during the build and before compiling the code

//