The web.config connection string changes when published

Question

I have a connection string in my web.config that includes a password with a% symbol, as shown 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 publishing to IIS8 web server through VS2013 and Web Deploy, something in this process controls the XML and changes the password section of the string as follows:

 password=abcdeùabcd 

Thus, it turns %F9 into ù (Unicode conversion).

I tried to encode% to  that does not solve the problem.

Is the problem a problem that I can solve, either with some way or with configuring? Unfortunately, I can’t control the password itself, it is provided by a third party.

Answer 1

At the time of publishing, .NET assumes that you put the encoded values ​​into parameters, and during the publishing process, it decodes those values ​​that cause the problem. So, the encoded value of your connection string is "abcde% 25F9abcd". Use this value in the web.config file or place it separately on the settings tab of the Publish dialog box, as I did in the linked image. This solves the problem.

Hope this works for you.

Publish Website - Settings Tab

Answer 2

You must follow XML standards.

According to the specifications of the World Wide Web Consortium (w3C), there are 5 characters that should not be displayed in their literal form in an XML document, unless they are used as markup delimiters or in a comment, processing instruction, or CDATA section. In all other cases, these characters must be replaced either using the appropriate object or using a numerical reference in accordance with the following table:

A named character, (apostrophe, U + 0027) was introduced in XML 1.0 but is not displayed in HTML. Therefore, authors should use 'instead' to work as expected in HTML 4 user agents. Please visit - http://www.w3.org/TR/2002/REC-xhtml1-20020801/#C_16

Answer 3

Web.Config is nothing but an XML file. Therefore, instead of special characters, you should use the appropriate replacement values. In fact, "%" is not allowed, because XML will read it as some other special character. Why does it replace "% F9" with a different value.