Python pyodbc使用Sql Server身份验证连接到sqlserver

Question

窗口用户详细信息与我登录的Sql Server用户不同。所以我尝试使用pyodbc连接到数据库，使用用户名（Admin_JJack）和密码。但是窗口用户（Jack）的连接显示失败了，我不知道哪里出错了。

我的连接字符串：

connection = pyodbc.connect(
    "Driver={"SQL Driver"};"
    "Server= "ServerName";"
    "Database="DatabaseName";"
    "UID="UserName";"
    "PWD="Password";"
    "Trusted_Connection=yes"
)

pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'Jack'. (18456) (SQLDriverConnect);

如何使用sql server身份验证连接到数据库？

Answer 1

使用“Trusted_Connection=yes”时，将忽略UID和PWD密钥，并使用Windows帐户进行身份验证。

如果要使用UID和PWD值进行身份验证，而不是使用Windows NTLM帐户，则必须使用“Trusted_Connection=No”或从连接字符串中删除此选项。

Trusted_Connection

Specifies whether a user connects through a user account by using either Kerberos [RFC4120] or another platform-specific authentication as specified by the fIntSecurity field (for details, see [MS-TDS] section 2.2.6.4).

The valid values are "Yes", "1", or empty string, which are equivalent, or "No". If the value "No" is not specified, the value "Yes" is used.

If the value is "No", the UID and PWD keys have to be used to establish a connection with the data source.

If the DSN key and the UID key are not included in the connection string or if the value of the UID key is an empty string, the value of the Trusted_Connection key has to be "Yes". If the Trusted_Connection key is not specified in the connection string, the value has to be obtained from the contents of the settings in the DSN key. If the Trusted_Connection key is not specified in DSN or if the given DSN does not exist, the default value is "No".

If the value of the Trusted_Connection key is "Yes", both the UID and PWD keys are ignored. Otherwise, the UID key has to be specified.

In Microsoft implementations, this user account is a Windows user account and NTLM authentication [MSDN-NTLM] is used when the value of the Trusted_Connection key is "Yes".

来源：https://msdn.microsoft.com/