12

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

TLama
  • 75,147
  • 17
  • 214
  • 392
Z .
  • 12,657
  • 1
  • 31
  • 56
  • Can we see a similar version of your connection string, please? – Paul Mar 20 '13 at 15:49
  • should the pwd contain a space? – RBA Mar 20 '13 at 16:19
  • 3
    `The password contains a space as the last character.` Why, why, why, why, why? Many interfaces will ltrim/rtrim strings. This seems silly to try to work around this when you should simply use a better password. – Aaron Bertrand Mar 20 '13 at 16:20
  • 2
    @Aaron, what workaround ? You should fix such trimming interfaces instead. I would definitely let user choose whatever password he/she wants, however it's crazy. I haven't met this (since I'm not using such password), but how this system would work ? After you press register or create user, a popup with *"Sorry, you can't use spaces around your password"* would popup ? Yes, forbid the space character might be a workaround. – TLama Mar 20 '13 at 16:27
  • @TLama you don't have control over all of those interfaces. And when you don't, yes, you should validate and prevent such characters from being included in a password. In this case it's not a real user's password, it's the application password, so I don't understand why the password needs to end with a trailing space. – Aaron Bertrand Mar 20 '13 at 16:29
  • 6
    @AaronBertrand: Why not? It's up to the user to decide what the password will be. The real question is WHY these "many interfaces" do trim? Also I don't see any problem with a password with blank(s) at the end or beginning. – iPath ツ Mar 20 '13 at 16:42
  • @iPath so are you up in arms with Apple because iTunes won't let you have a password with three consecutive characters that are the same? What about services that max out the password at 12 characters, or don't allow # or $ or _? Would you like to also allow carriage returns, line feeds, tabs and non-printing boxes into passwords? Different services have different reasons for their password rules. I didn't come up with them, but I know that if I had some reason to have a trailing space, and a service didn't allow it, I would get over it and use something better. Period. – Aaron Bertrand Mar 20 '13 at 16:54

1 Answers1

15

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.

Community
  • 1
  • 1
kobik
  • 21,001
  • 4
  • 61
  • 121
  • You changed two things. You changed the name of the value from `pwd` to `Password`, but you also moved the value so it's not at the end of the string anymore. Are both changes necessary? – Rob Kennedy Mar 20 '13 at 17:59