(Maybe) Illegal character in ODBC SQL Server Connection String PWD=

Question

According to what I have researched there are no illegal characters in the PWD= field of a SQL Server Connection String.

However, using SQL Server Express 2008 I changed the SA password to a GUID, specifically:

{85C86BD7-B15F-4C51-ADDA-3B6A50D89386}

So when connecting via ODBC I use this connection string:

"Driver={SQL Server};Server=.\\MyInstance;Database=Master;UID=SA;PWD={85C86BD7-B15F-4C51-ADDA-3B6A50D89386};"

But it comes back as Login failed for SA.

However, if I change the SA password to something just as long but without {}- it succeeds! Are there certain characters in PWD= that need to be escaped? I tried all different combinations with no luck.

Answer 1

As Microsoft's documentation states (emphasis added) --

Connection strings used by ODBC have the following syntax:

connection-string ::= empty-string[;] | attribute[;] | attribute; connection-string

empty-string ::=

attribute ::= attribute-keyword=[{]attribute-value[}]

attribute-value ::= character-string

attribute-keyword ::= identifier

Attribute values can optionally be enclosed in braces, and it is good practice to do so. This avoids problems when attribute values contain non-alphanumeric characters. The first closing brace in the value is assumed to terminate the value, so values cannot contain closing brace characters.

I would suggest you simply remove the braces when you set the password, and then the connect string you provided above should work fine.

ADDITION

I dug a bit further on Microsoft's site, and found some ABNF rules which may be relevant --

 SC           = %x3B         ; Semicolon 
 LCB          = %x7B         ; Left curly brackets 
 RCB          = %x7D         ; Right curly brackets  
 EQ           = %x3D         ; Equal sign 
 ESCAPEDRCB   = 2RCB         ; Double right curly brackets 
 SpaceStr     = *(SP)        ; Any number (including 0) spaces
 ODBCConnectionString =  *(KeyValuePair SC) KeyValuePair [SC]
 KeyValuePair = (Key EQ Value / SpaceStr)
 Key = SpaceStr KeyName
 KeyName = (nonSP-SC-EQ *nonEQ)
 Value = (SpaceStr ValueFormat1 SpaceStr) / (ValueContent2)
 ValueFormat1 = LCB ValueContent1 RCB
 ValueContent1 = *(nonRCB / ESCAPEDRCB)
 ValueContent2 = SpaceStr / SpaceStr (nonSP-LCB-SC) *nonSC
 nonRCB = %x01-7C / %x7E- FFFF                                 ; not "}"
 nonSP-LCB-SC = %x01-1F / %x21-3A / %x3C-7A / %x7C- FFFF       ; not space, "{" or ";"
 nonSP-SC-EQ = %x01-1F / %x21-3A / %x3C / %x3E- FFFF           ; not space, ";" or "="
 nonEQ = %x01-3C / %x3E- FFFF                                  ; not "="
 nonSC = %x01-003A / %x3C- FFFF                                ; not ";"

...

ValueFormat1 is recommended to use when there is a need for Value to contain LCB, RCB, or EQ. ValueFormat1 MUST be used when the Value contains SC or starts with LCB.

ValueContent1 MUST be enclosed by LCB and RCB. Spaces before the enclosing LCB and after the enclosing RCB MUST be ignored.

ValueContent1 MUST be contained in ValueFormat1. If there is an RCB in the ValueContent1, it MUST use the two-character sequence ESCAPEDRCB to represent the one-character value RCB.

All of which comes down to... I believe the following connect string should work for you (note that there are 2 left/open braces and 3 right/close braces on the PWD value) --

"Driver={SQL Server};Server=.\\MyInstance;Database=Master;UID=SA;PWD={{85C86BD7-B15F-4C51-ADDA-3B6A50D89386}}};"