PowerShell and XML editing

I have the following code that allows me to change a line in web config but if there are 2 connection strings it fails.

How can I change it so it works?

$New_Pass = 'Welcome"
$Web = 'C:\inetpub\wwwroot\Contoso\web.config'
$CONNECTION_STRING ="Data Source=JOHN.WORLD;User Id=peter;password=" + $New_Pass  
 
$doc = new-object System.Xml.XmlDocument  
$doc.Load($Web)

$root = $doc.get_DocumentElement();

# Change password
# If there 2 entries it will fail  
$root.connectionStrings.add.connectionString = $CONNECTION_STRING

$doc.Save($Web)

Code does exactly what I need but if there are 2 or more entries in that tag it will fail
hwalchAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
cls
$path = "c:\temp\ee\"
$Web = $path + "web.config"

$xml = New-Object XML
$xml.Load($web)
# Change password
$xml.configuration.connectionStrings.add |
  % {
    $pwd = switch ($_.Name)
      {
        'ConString1'       { 'NewPass1' }
        'ConstringPSupply' { 'NewPass2' }
      }
    $_.ConnectionString = $_.ConnectionString -replace 'Password=(\w*)', "Password=$pwd" 
  }
$xml.Save($Web)
Get-Content $web

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Would you mind posting an example web.config?
0
 
hwalchAuthor Commented:
<?xml version="1.0"?>
<configuration>
   <connectionStrings>
      <add name="ConStringPSupply" connectionString="Data Source=.;Initial      
                           Catalog=pSupply;User ID=sa;Password=1234"/>
    <add name="ConString1" connectionString="Data Source=.;Initial
                          Catalog=NEW1;Integrated Security=True"
                          providerName="System.Data.SqlClient"/>
  </connectionStrings>
  
<system.web>
		<!--
            Set compilation debug="true" to insert debugging
            symbols into the compiled page. Because this
            affects performance, set this value to true only
            during development.
        -->
		<httpHandlers>
      <remove verb="*" path="*.asmx"/>
      <add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
      <add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
      <add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false"/>
      <add verb="GET" path="CrystalImageHandler.aspx" type="CrystalDecisions.Web.CrystalImageHandler, CrystalDecisions.Web, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
    </httpHandlers>
    <httpModules>
      <add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
    </httpModules>
<compilation debug="true">
	<assemblies>
		<add assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
				<add assembly="System.Web.Extensions.Design, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
				<add assembly="System.Web.RegularExpressions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
				<add assembly="System.Drawing.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
				<add assembly="CrystalDecisions.CrystalReports.Engine, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
				<add assembly="CrystalDecisions.ReportSource, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
				<add assembly="CrystalDecisions.Shared, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
				<add assembly="CrystalDecisions.Web, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
				<add assembly="CrystalDecisions.ReportAppServer.ClientDoc, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
				<add assembly="CrystalDecisions.Enterprise.Framework, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
				<add assembly="CrystalDecisions.Enterprise.InfoStore, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
				<add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
				<!--<add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>-->
      </assemblies>
</compilation>
		<!--
            The <authentication> section enables configuration
            of the security authentication mode used by
            ASP.NET to identify an incoming user.
        -->
<authentication mode="Windows"/>
      <!--
            The <customErrors> section enables configuration
            of what to do if/when an unhandled error occurs
            during the execution of a request. Specifically,
            it enables developers to configure html error pages
            to be displayed in place of a error stack trace.

        <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
            <error statusCode="403" redirect="NoAccess.htm" />
            <error statusCode="404" redirect="FileNotFound.htm" />
        </customErrors>
        -->
</system.web>
</configuration>

Open in new window

0
NEW Internet Security Report Now Available!

WatchGuard’s Threat Lab is a group of dedicated threat researchers committed to helping you stay ahead of the bad guys by providing in-depth analysis of the top security threats to your network.  Check out this quarters report on the threats that shook the industry in Q4 2017.

 
David Johnson, CD, MVPOwnerCommented:
<#
   <connectionStrings>
      <add name="ConStringPSupply" connectionString="Data Source=.;Initial Catalog=pSupply;User ID=sa;Password=1234"/>
    <add name="ConString1" connectionString="Data Source=.;Initial Catalog=NEW1;Integrated Security=True"                           providerName="System.Data.SqlClient"/>
  </connectionStrings>
#>
$path = "D:\Documents\WindowsPowerShell\Scripts\"
$Web = $path + "ee22jun13.xml"
#$output = "connectionstring2.xml"
$New_Pass = "Welcome"
$Connection_String1 ="Data Source=JOHN.WORLD;User Id=peter;password=" + $New_Pass  
$Connection_String2 = "Data Source=WAYNE.WORLD;User ID=peter;password=" + $New_Pass
$xml = New-Object XML
$xml.Load($web)
# Change password
# If there 2 entries it will fail  
$xml.configuration.connectionStrings.Add[0] = $Connection_String1
$xml.configuration.connectionStrings.Add[1] = $Connection_String2
$xml.Save($Web)
cls
$Connection_String1
Get-Content $web

Open in new window


It changes it in memory but for the life of me I can't get it to save
0
 
hwalchAuthor Commented:
Interesting, it looks very simple.  I will test it on Monday.

Now I web.config could have 2 or 3 or more connectionstrings how do I find out how many are there?  

Thanks
0
 
David Johnson, CD, MVPOwnerCommented:
$xml.configuration.connectionStrings.add.Count
Will return the number of connectionstrings objects
remember to start your counter when assigning from 0 and not 1 i.e. two objects  first is object[0]
0
 
hwalchAuthor Commented:
You were right.  I am able to change the value perfectly.  I was able to see the count also, it returns two.  But when I save the changes, nothing happens.  

I tried to do one save and then do the second and then save but it didn't change the outcome.

Beatiful !!! :-)
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Wrong. Neither in memory nor on disk you will see any change - because the change is wrong ;-).
When changing the connection string, we need to provide the attribute "connectionString", as was in the original code.
$path = "c:\temp\ee\tst\"
$Web = $path + "ee22jun13.xml"
$New_Pass = "Welcome"
$Connection_String1 ="Data Source=JOHN.WORLD;User Id=peter;password=" + $New_Pass  
$Connection_String2 = "Data Source=WAYNE.WORLD;User ID=peter;password=" + $New_Pass
$xml = New-Object XML
$xml.Load($web)
# Change password
$xml.configuration.connectionStrings.Add[0].connectionString = $Connection_String1
$xml.configuration.connectionStrings.Add[1].connectionString = $Connection_String2
$xml.Save($Web)
cls
Get-Content $web

Open in new window

Of course that only works well if you have exactly the same amount and content of connectionstrings everywhere.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
This seems to be much better:
$path = "c:\temp\ee\tst\"
$Web = $path + "ee22jun13.xml"

$New_Pass = "Welcome"

$xml = New-Object XML
$xml.Load($web)
# Change password
$xml.configuration.connectionStrings.add |
  ? { $_.ConnectionString -like '*Password=*' } |
  % { $_.ConnectionString = $_.ConnectionString -replace 'Password=(\w*)', "Password=$New_Pass" }
$xml.Save($Web)
cls
Get-Content $web

Open in new window

0
 
hwalchAuthor Commented:
Sorry for going away for so long and thank you for your input.  

That last entry almost works.  

It's the only one I have managed to make work.  

However this is assuming that all paswords are the same.  That is not the case.  

If you have 3 connections strings, one will be forWindows SQL, one for Oracle, and one for Sybase or just 3 different servers.  

So you need to be able to replace only one for each add name
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
In that case we just need to check for the fitting Name attribute:
$path = "c:\temp\ee\tst\"
$Web = $path + "web.config"

$New_Pass = "Welcome"

$xml = New-Object XML
$xml.Load($web)
# Change password
$xml.configuration.connectionStrings.add |
  ? { $_.Name -eq 'ConString2' } |
  ? { $_.ConnectionString -like '*Password=*' } |
  % { $_.ConnectionString = $_.ConnectionString -replace 'Password=(\w*)', "Password=$New_Pass" }
$xml.Save($Web)
cls
Get-Content $web

Open in new window

Line 11 isn't really necessary, as we are very specific in which connection string we want to change, and the replace won't do any harm if there is no password tag (as with trusted security). You can remove it or leave it as-is hence.
0
 
hwalchAuthor Commented:
NOOOOOOOOOOOOOOOOOOOOOOOOOOOO

I am actively using it
0
 
hwalchAuthor Commented:
That is totally incorrect.  

The posted script works only for one line.  That was not the question.

Do I need to repost my question?
0
 
hwalchAuthor Commented:
I think if you are going to assign points for me,  500 is incorrect if the question is only partially answered.

500 would be for a perfect answer.
0
 
hwalchAuthor Commented:
So is there any one else who want to help?

There is a lot activity about to change Web.config in google weird that no-one has the knowledge.
0
 
hwalchAuthor Commented:
I did
0
 
hwalchAuthor Commented:
Do do I need to start all over again and repost?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You haven't been verbose and precise about your requirements. From what you have posted now, I derive you need to change all db connect strings, and have to do so with different passwords?

How should that happen? Check for how many connection strings are in there, then ask a new password for each? Is the sequence the same all the time, or might they get mixed up, or whatsoever?
We either need a static sequence of strings, with. e.g. ConString2 always being the MSSQL connections, or something else clearly identifying each connection string and the new password to associate.
We can also do a simple old => new password replacement, if that is more simple.
0
 
hwalchAuthor Commented:
The logic is correct but the code to navidate from ConnectionString to connectionString and replace the Password with Xpath that is what I need
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.