How to build a connection string from other parameters within MSDeploy packages to avoid repeating yourself in Release Management variables

Apr 18, 2016 · 6 min read · Uncategorised ·

Share on:

Whilst working with the new Release Management features in VSTS/TFS 2015.2 I found I needed to pass in configuration variables i.e. server name, Db name, UID and Password to create a SQL server via an Azure Resource Management Template release step and a connection string to the same SQL instance for a web site’s web.config, set using an MSDeploy release step using token replacement (as discussed in this post)

Now I could just create RM configuration variables for both the connection string and ARM settings,

However, this seems wrong for a couple of reason

You should not repeat your self, too easy to get the two values out of step
I don’t really want to obfuscate the whole of a connection string in RM, when only a password really needs to be hidden (note the connection string variable is not set as secure in the above screenshot)

What did not work

I first considered nesting the RM variables, e.g. setting a the connection string variable to be equal to ‘Server=tcp: $(DatabaseServer).database.windows.net,1433;Database=$(DatabaseName)….’, but this does not give the desired results, the S(DatabaseServer) and $(DatabaseName) variables are not expanded at runtime, you just get a string with the variable names in it.

How I got want I was after….

(In this post as a sample I am using the Fabrikam Fiber solution. This means I need to provide a value for the FabrikamFiber-Express connection string)

I wanted to build the connection string from the other variables in the MSDeploy package. So to get the behaviour I want…

In Visual Studio load the Fabrikam web site solution.
In the web project, use the publish option to create a publish profile use the ‘WebDeploy package’ option.

If you publish this package you end up with a setparameter.xml file containing the default connection string

1<setParameter name="FabrikamFiber-Express-Web.config Connection String" value="Your value”/>
2```Where ‘your value’ is the value you set in the Publish wizard. So to use this I would need to pass in a whole connection string, where I only want to pass parts of this string

To add bespoke parameters to an MSDeploy package you add a parameter.xml file to the project in Visual Studio (I wrote a Visual Studio Extension that help add this file, but you can create it by hand). My tool will create the parameters.xml file based on the AppSettings block of the projects Web.config. So if you have a web.config containing the following
```
1<appSettings>  
2    <add key="Location" value="DEVPC" />  
3  </appSettings>
4```It will create a **parameters.xml** file as follows  
```
```

If we publish at this point we will get a setparameters.xml file containing

1<?xml version="1.0" encoding="utf-8"?>  
2<parameters>  
3  <setParameter name="IIS Web Application Name" value="\_\_Sitename\_\_" />  
4  <setParameter name="Location" value="\_\_LOCATION\_\_" />  
5  <setParameter name="FabrikamFiber-Express-Web.config Connection String" value="\_\_FabrikamFiberWebContext\_\_" />  
6</parameters>
7```This is assuming I used the publish wizard to set the site name to **\_\_SiteName\_\_** and the DB connection string to **\_\_FabrikamFiberWebContext\_\_**

Next step is to add my DB related parameters to the paramaters.xml file, this I do by hand, my tool does not help

 1<?xml version="1.0" encoding="utf-8"?>  
 2<parameters>  
 3  <parameter defaultValue="\_\_LOCATION\_\_" description="Description for Location" name="Location" tags="">  
 4    <parameterentry kind="XmlFile" match="/configuration/appSettings/add\[@key='Location'\]/@value" scope="\\web.config$" />  
 5  </parameter>  
 6
 7  <parameter name="Database Server" defaultValue="\_\_sqlservername\_\_"></parameter>  
 8  <parameter name="Database Name" defaultValue="\_\_databasename\_\_"></parameter>  
 9  <parameter name="Database User" defaultValue="\_\_SQLUser\_\_"></parameter>  
10  <parameter name="Database Password" defaultValue="\_\_SQLPassword\_\_"></parameter>  
11 </parameters>

If I publish again, this time the new variables also appear in the setparameters .xml file
Now I need to supress the auto generated creation of the connection string parameter, and replace it with a parameter that uses the other parameters to generate the connection string. You would think this was a case of added more text to the parameters.xml file, but that does not work. If you add the block you would expect (making sure the name matches the auto generated connection string name) as below
```
 1<parameter   
 2  defaultValue="Server=tcp:{Database Server}.database.windows.net,1433;Database={Database Name};User ID={Database User}@{Database Server};Password={Database Password};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"   
 3  description="Enter the value for FabrikamFiber-Express connection string"   
 4  name="FabrikamFiber-Express-Web.config Connection String"   
 5  tags="">  
 6  <parameterentry   
 7    kind="XmlFile"   
 8    match="/configuration/connectionStrings/add\[@name='FabrikamFiber-Express'\]/@connectionString"   
 9    scope="\\web.config$" />  
10</parameter>
```
It does add the entry to setparameters.xml, but this blocks the successful operations at deployment. It seems that if a value needs to be generated from other variables there can be no entry for it in the setparameters.xml. Documentation hints you can set the Tag to ‘Hidden’ but this does not appear to work.
One option would be to let the setparameters.xml file be generated and then remove the offending line prior to deployment but this feels wrong and prone to human error

To get around this you need to added a file name .wpp.target to the same folder as the project (and add it to the project). In this file place the following

 1<?xml version="1.0" encoding="utf-8"?>  
 2<Project ToolsVersion="4.0" xmlns="[http://schemas.microsoft.com/developer/msbuild/2003"](http://schemas.microsoft.com/developer/msbuild/2003")\>  
 3<Target Name="DeclareCustomParameters"  
 4          BeforeTargets="Package">  
 5    <ItemGroup>  
 6      <MsDeployDeclareParameters Include="FabrikamFiber-Express">  
 7        <Kind>XmlFile</Kind>  
 8        <Scope>Web.config</Scope>  
 9        <Match>/configuration/connectionStrings/add\[@name='FabrikamFiber-Express'\]/@connectionString</Match>  
10        <Description>Enter the value for FabrikamFiber-Express connection string</Description>  
11        <DefaultValue>Server=tcp:{Database Server}.database.windows.net,1433;Database={Database Name};User ID={Database User}@{Database Server};Password={Database Password};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;</DefaultValue>  
12        <Tags></Tags>  
13        <ExcludeFromSetParameter>True</ExcludeFromSetParameter>  
14      </MsDeployDeclareParameters>  
15    </ItemGroup>  
16  </Target>  
17  <PropertyGroup>  
18    <AutoParameterizationWebConfigConnectionStrings>false</AutoParameterizationWebConfigConnectionStrings>  
19  </PropertyGroup>  
20</Project>

The first block declares the parameter I wish to use to build the connection string. Note the ‘ExcludeFromSetParameter’ setting so this parameter is not in the setparameters.xml file. This is what you cannot set in the parameters.xml

The second block stops the auto generation of the connection string. (Thanks to Sayed Ibrahim Hashimi for various posts on getting this working)

Once the edits are made unload and reload the project as the . wpp.targets file is cached on loading by Visual Studio.
Make sure the publish profile is not set to generate a connection string

[![image](/wp-content/uploads/sites/2/historic/image_thumb_298.png "image")](/wp-content/uploads/sites/2/historic/image_302.png)

Now when you publish the project, you should get a setparameters.xml file with only the four SQL variables, the AppSettings variables and the site name.
(Note I have set the values for all of these to the format __NAME__, this is so I can use token replacement in my release pipeline)
<?xml version="1.0" encoding="utf-8"?> <parameters> <setParameter name="IIS Web Application Name" value="\_\_Sitename\_\_" /> <setParameter name="Location" value="\_\_LOCATION\_\_" /> <setParameter name="Database Server" value="\_\_sqlservername\_\_" /> <setParameter name="Database Name" value="\_\_databasename\_\_" /> <setParameter name="Database User" value="\_\_SQLUser\_\_" /> <setParameter name="Database Password" value="\_\_SQLPassword\_\_" /> </parameters>
If you deploy the web site, the web.config should have your values from the setparameters.xml file in it
<appSettings> <add key="Location" value="\_\_LOCATION\_\_" /> </appSettings> <connectionStrings> <add name="FabrikamFiber-Express" connectionString="Server=tcp:\_\_sqlservername\_\_.database.windows.net,1433;Database=\_\_databasename\_\_;User ID=\_\_SQLUser\_\_@\_\_sqlservername\_\_;Password=\_\_SQLPassword\_\_;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" providerName="System.Data.SqlClient" /> </connectionStrings>

You are now in a position manage the values of the setparameters.xml file however you wish. My choice is to use the ‘Replace Tokens’ build/release tasks from Colin’s ALM Corner Build & Release Tools Extension, as this tasks correctly handles secure/encrypted RM variables as long as you use the ‘Secret Tokens’ option on the advanced menu.

Summary

So yes, it all seems a but too complex, but it does work, and I think it makes for a cleaner deployment solution, less prone to human error. Which is what any DevOps solution must always strive for.

Depending on the values you put in the .wpp.targets you can parameterise the connection string however you need.