CODEWITHSUNDEEP
odbcconnection-stringsql-server-express
Neil Weicher
Neil Weicher

Reputation: 2502

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

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.

Upvotes: 3

Views: 4006

Answers (2)

mnemotronic
mnemotronic

Reputation: 1026

According to this page, the only legal "special character" in a name (I think they're talking about the DSN) is the UNDERSCORE:

The ODBC specification (and the SQL specification) states that names must be in the format of " letter[digit | letter | _]...". The only special character allowed is an underscore.

There was no reference to "the ODBC Specification". This page says it's the the ODBC 4.0 Spec.

Upvotes: 0

TallTed
TallTed

Reputation: 9434

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}}};"

Upvotes: 4

Related Questions