(也许)ODBC SQL Server连接字符串PWD =中的非法字符 [英] (Maybe) Illegal character in ODBC SQL Server Connection String PWD=

问题描述

根据我的研究，SQL Server连接字符串的PWD =字段中没有非法字符.

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

但是，使用SQL Server Express 2008，我将SA密码更改为GUID，具体是:

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

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

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

因此，当通过ODBC连接时，我使用以下连接字符串:

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

但是由于SA登录失败而返回.

But it comes back as Login failed for SA.

但是，如果我将SA密码更改为一样长但没有 {}-的密码，它将成功！PWD =中是否有某些字符需要转义?我没有运气就尝试了所有不同的组合.

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.

推荐答案

作为

ODBC使用的连接字符串具有以下语法:

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.

我在Microsoft的网站上进行了进一步的挖掘，发现了一些

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

...

如果需要 Value 包含 LCB ， RCB 或 EQ .当 Value 包含 SC 或以 LCB 开头时，必须使用 ValueFormat1 .

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 必须包含在 LCB 和 RCB 中.封闭的 LCB 之前和封闭的 RCB 之后的空格必须被忽略.

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

ValueContent1 必须包含在 ValueFormat1 中.如果 ValueContent1 中有一个 RCB ，则它必须使用两个字符的序列 ESCAPEDRCB 来表示一个单字符值 RCB.

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.

所有这些归结为...我相信以下连接字符串应该为您工作(请注意，PWD值上有2个左/右大括号和3个右/右大括号)-

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

这篇关于(也许)ODBC SQL Server连接字符串PWD =中的非法字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！