web.config connection string changing when published

Question

I have a connection string in my web.config which includes a password with the % character as below

<add name="ConnectionName" 
providerName="System.Data.SqlClient" 
connectionString="server=ServerName;database=DatabaseName;
uid=UserName;password=abcde%F9abcd;" />

Locally in VS2013 the connection string works fine but when published to the IIS8 web server via VS2013 and Web Deploy, something in that process manipulates the XML and changes the password section of the string to the following

password=abcdeùabcd

So it's turning the %F9 into ù (unicode conversion).

I have tried encoding the % to  which doesn't resolve the issue.

Is the problem something I can resolve with either escaping somehow or a configuration setting? Unfortunately I have no control over changing the password itself, it's supplied by a third party.

Namara

Comment removed after closer look – Accelerator

You 1)Change your password, 2)Use Integrated Security=True and not set password 3) set UTF-8 for the files on web.config – Martial

1) I already explained this is provided by a third party and can't be changed, 2) See #1, 3) UTF8 is already set on web.config. Surely someone else has run into this issue as it doesn't seem to only affect connection strings... it will do it to any data anywhere in the web.config file. – Namara

Take a look at your build process for anything that may have been customized. You may have to look at the build XML directly to find everything. – Badalona

The fact that it's turning %F9 into ù tells me that somewhere down the line the password is being URL decoded. The url encoded value of % is %25 – Rimma

That is my guess too, most likely by web deploy. I'm unable to find any documentation or other users experiencing the issue in my searching though – Namara

Answer 1

At the time of publishing, .NET assumes that you have placed encoded values in the settings and during publish process, it decodes these values which causes the problem. So, the encoded value of your connection string is "abcde%25F9abcd". Use this value in you web.config or place this separately in the settings tab of Publish dialog as I have done in the linked image. This solves the issue.

Hope it works for you.

Publish Website - Settings Tab

Arbe

Answer 2

You have to follow the standards of XML

According to the specifications of the World Wide Web Consortium (w3C), there are 5 characters that must not appear in their literal form in an XML document, except when used as markup delimiters or within a comment, a processing instruction, or a CDATA section. In all the other cases, these characters must be replaced either using the corresponding entity or the numeric reference according to the following table:

The named character reference ' (the apostrophe, U+0027) was introduced in XML 1.0 but does not appear in HTML. Authors should therefore use ' instead of ' to work as expected in HTML 4 user agents. Please visit - http://www.w3.org/TR/2002/REC-xhtml1-20020801/#C_16

Photolysis

Answer 3

Web.Config is nothing but a XML file. So instead of special characters you should use its relevant replacement values. Actually "%" is not allowed because XML will read it as some other special character..Thats why it is replacing "%F9" with some other value.

Grazier