Solved

PowerShell and XML editing

Posted on 2013-06-21
24
115 Views
Last Modified: 2015-07-17
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
0
Comment
Question by:hwalch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 6
  • 2
24 Comments
 
LVL 70

Expert Comment

by:Qlemo
ID: 39266187
Would you mind posting an example web.config?
0
 

Author Comment

by:hwalch
ID: 39267201
<?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
 
LVL 81

Expert Comment

by:David Johnson, CD, MVP
ID: 39268657
<#
   <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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:hwalch
ID: 39268863
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
 
LVL 81

Expert Comment

by:David Johnson, CD, MVP
ID: 39268874
$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
 

Author Comment

by:hwalch
ID: 39272084
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
 
LVL 70

Expert Comment

by:Qlemo
ID: 39272191
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
 
LVL 70

Expert Comment

by:Qlemo
ID: 39272255
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
 

Author Comment

by:hwalch
ID: 40115287
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
 
LVL 70

Expert Comment

by:Qlemo
ID: 40115357
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
 

Author Comment

by:hwalch
ID: 40406878
NOOOOOOOOOOOOOOOOOOOOOOOOOOOO

I am actively using it
0
 

Author Comment

by:hwalch
ID: 40406886
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
 

Author Comment

by:hwalch
ID: 40406888
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
 

Author Comment

by:hwalch
ID: 40406893
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
 

Author Comment

by:hwalch
ID: 40406933
I did
0
 

Author Comment

by:hwalch
ID: 40406935
Do do I need to start all over again and repost?
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40406946
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
 

Author Comment

by:hwalch
ID: 40407400
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
 
LVL 70

Accepted Solution

by:
Qlemo earned 500 total points
ID: 40408551
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

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Scenario: Your operations manager has discovered an anomaly in your security system. The business will start to suffer within 15 minutes if it is a major IT incident. What should she do? We have 6 recommendations for managing major incidents (https:…
We asked our MSP customer base what their favorite tools were and how they help them serve clients. We focused our questions on favorite tools in the following categories: >PSA tools >RMM tools >Alert management tools >Communication tools and Mo…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question