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
