68

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.

kenorb
  • 155,785
  • 88
  • 678
  • 743
user403830
  • 907
  • 1
  • 6
  • 11

1 Answers1

113

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>
hunter
  • 62,308
  • 19
  • 113
  • 113
  • I already tried that, as noted in my question. It seems that the wrong password is sent along to the Entity Framework. – user403830 Sep 29 '10 at 17:53
  • Also, make you've tried logging into that SQL Server instance using the credentials in your connectionstring outside of your application – hunter Sep 29 '10 at 18:11
  • Right on about testing the credentials. I was led to believe that these were SQL server credentials, but they are, in fact, Active Directory credentials. Oooops. Thanks for the "make sure the computer is plugged in" suggestions. We all need that sometimes. – user403830 Sep 29 '10 at 20:04
  • 2
    Additionally, let me just comment to say that escaping XML characters usually does work fine. For example, in an tag in a web.config file. However, I don't think you are allowed to put a CDATA section inside an XML attribute (XML 1.0 spec section 3.3.3). – user403830 Sep 29 '10 at 20:12
  • OK. I tried this and got this error: Parser Error Message: '<', hexadecimal value 0x3C, is an invalid attribute character – Denis M. Kitchen Oct 02 '13 at 20:27
  • 7
    The CDATA thing doesn't appear to work. At least, Visual Studio's XML editor complains when you try to put that into the web.config. – Micah Zoltu Oct 17 '15 at 20:50
  • I can confirm that escaping the ampersand as jack&jill in the connection string fixes the problem with saving the xml file and the application reads the connection string and automatically unescapes the pw back to jack&jill and login occurs just fine (I used a sql user with a password = jack&jill). – Jeff Mergler Apr 15 '20 at 21:29
  • 1
    Just a note: If your special character is the last character take care that you've 2 semicolons at the end. One for the escape sequence and one to separate your connection parameters. – Sven May 19 '20 at 11:18