user403830 user403830 - 1 month ago 14
ASP.NET (C#) Question

How to include ampersand in connection string password when using Entity Framework?

I'm using Entity Framework 4 for a simple app and would like to bake my connection credentials into the following connection string:

<connectionStrings>
<add name="MyEntities"
connectionString="metadata=res://*/MyDataModel.csdl|res://*/MyDataModel.ssdl|res://*/MyDataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=localhost\DEV;Initial Catalog=MyDB;UserId=myUser;Password=jack&jill;MultipleActiveResultSets=True&quot;"
providerName="System.Data.EntityClient" />
</connectionStrings>


However, the password (which I cannot change) contains an ampersand. ASP.NET throws:
Configuration Error: An error occurred while parsing EntityName. Line XX, position YYY.


If I replace the ampersand in the password with
&amp;
, I get a
SqlException: Login failed for user 'myUser'.
Usually this trick works, but I'm guessing that something is failing because this is technically a connection string inside a connection string.

What should I do here? Most of my classes include code like:

using (var context = new MyEntities()) {
// do work
}





Update: It turns out that the credentials I am using are a domain account, so what I really need is
Integrated Security=True
in the connection string rather than a password.

Encoding the ampersand as indicated in the accepted answer should work fine, though I haven't tested it.

Answer

You'll need to use escape sequences like you would for any XML document, which is all the .config files are.

  • Ampersand = & = &amp;
  • Greater Than = > = &gt;
  • Less Than = < = &lt;
  • Apostrophe = ' = &apos;
  • Quote = " = &quot;

You can also use the CDATA tag so that you can use these illegal characters

<![CDATA[ and ends with ]]>

<connectionStrings>
    <add name="MyEntities" connectionString="
        metadata=res://*/MyDataModel.csdl|res://*/MyDataModel.ssdl|res://*/MyDataModel.msl;
        provider=System.Data.SqlClient;
        provider connection string=&quot;
        Data Source=localhost\DEV;
        Initial Catalog=MyDB;UserId=myUser;
        Password=<![CDATA[jack&jill]]>;
        MultipleActiveResultSets=True&quot;" 
        providerName="System.Data.EntityClient" />
</connectionStrings>