escape space in connection string

Question

I am trying to connect to a SQL server database using ADO. The password contains a space as the last character. When building the connection string, how should I escape the password?

MSDN says put it in single or double quotes but that does not seem to be working...

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

This is what my connection string looks like:

Provider=SQLOLEDB;Persist Security Info=False;Initial Catalog=master;Data Source=test;uid=john;pwd="123 "

Thanks

Answer 1

I can tell you what does work:

Provider=SQLOLEDB;Password="123 ";Persist Security Info=True;User ID=john;Initial Catalog=mydbname;Data Source=127.0.0.1

1. When pwd keyword is used instead of Password it fails to connect - You need to use Password keyword which should be enclosed with double-quotes.
2. uid instead of User ID works just fine - Id'e still use User ID because the connection builder creates it by default.
3. Persist Security Info keyword (was mentioned in one of the comments) has no impact on connection - As @TLama well commented "You can connect, but you don't get the password back from the IDBProperties interface, if you keep it False."
4. The order of keywords in the connection-string is not important. the IDBProperties interface parses this string into an internal "Properties" collection

---

Tested with SQL Server 2008 R2.