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.
How should i update the connection strings under storage accounts in powershell ..??