Powershell – IIS Set Connection String in web.config [Solution]

IIS Set Connection String in web.config using the Web-Administration powershell module

So in the web.config file  for “MyApplication” application under “Default Web Site”, I have a connection string called “MyChartsConnection”, that I want to change the connection string as I am modifying it for Production use , changing server name and username password, plus some parameters  that i do not need in test.

  <connectionStrings>
    <add connectionString="Data Source=SERVER1\GP12A;;Initial Catalog=MyCharts_Test;User Id=sql_test;Password=xxxx;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=True;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True;" name="MyChartsConnection" providerName="System.Data.SqlClient" />
  </connectionStrings>

So if we break down that middle line which is soooooo long what we see is that there are actually 3 parameters:

<add 
connectionString="Data Source=SERVER1\GP12A;;Initial Catalog=MyCharts_Test;User Id=sql_test;Password=xxxx;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=True;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True;" 
name="MyChartsConnection" 
providerName="System.Data.SqlClient"
/>

connectionString
name
providerName

NOTE:
It is confusing that you have connectionString and connectionStrings, so bear with me on that.
additionally bear in mind a lot of the IIS cmdlets can be case sensitive with properties , so always copy and paste what you see returned otherwise you will get frustrated as you type in ProviderName instead of providerName and get undesired results

So knowing that I can use the get-webConfiguration cmdlet, filtering on the connectionStrings section and the name “MyChartsConnection” 

get-WebConfiguration "IIS:\Sites\Default Web Site\MyApplication" -filter "connectionStrings/add[@name='MyChartsConnection']"

So if we look at what is returned we see the 3 parameters returned as properties

PS C:\WINDOWS\system32> get-WebConfiguration "IIS:\Sites\Default Web Site\MyApplication" -filter "connectionStrings/add[@name='MyChartsConnection']"

connectionString      : Data Source=SERVER1\GP12A;;Initial Catalog=PaediatricGrowthChart_Test;User Id=sql_test;Password=xxxxx;Persist Security 
                        Info=False;Pooling=False;MultipleActiveResultSets=True;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True;
name                  : MyChartsConnection
providerName          : System.Data.SqlClient
PSPath                : MACHINE/WEBROOT/APPHOST/Default Web Site/MyApplication
Location              : 
ConfigurationPathType : Location
ItemXPath             : /connectionStrings/add[@name='MyChartsConnection']
Attributes            : {connectionString, name, providerName}
ChildElements         : {}
ElementTagName        : add
Methods               : 
Schema                : Microsoft.IIs.PowerShell.Framework.ConfigurationElementSchema

So we can change those 3 things using Set-WebConfigurationProperty.

In this example I am changing the connection string using

-name  parameter and saying i want to change the property ‘connectionString’

set-WebConfigurationproperty -pspath "IIS:\Sites\Default Web Site\MyApplication" -filter "connectionStrings/add[@name='MyChartsConnection']" -name 'connectionString' -value "Data Source=SERVER22\GP12A;Initial Catalog=MyCharts_Prod;User Id=sql_Prod;Password=xxdfff;Connect Timeout=30;"

As  you can see I have to change the full string in one go, I cannot change just part of it.