Changing the connection string during the TFS build for the Publish Profile of the SSDT (Sql Server Data Tool)

One of our customers needed to change that, they need to type the password for the SQL login inside the publish profile but of course they couldn’t do that in terms of security reasons, so we created that during the TFS  build as the following:

  • Create a PowerShell script that replace 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 then, replace connection string with the one with the password, remember that we need to do that before compiling the SSDT so during 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 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

So the script can 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

SSDT change connection string


Share This: