pydobc连接sql server,Python Pyodbc使用SQL Server身份验证连接到Sql Server

The window user details is different from the Sql Server user I log in. So I had tried to use pyodbc connect to the database using the username(Admin_JJack) and password. But the connection show fails for the Window User(Jack) and I don't know where goes wrong.

my connection string :

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

How to connect to the database using sql server authentication ?

解决方案

When you use "Trusted_Connection=yes" both the UID and PWD keys are ignored and the Windows account is used for authentication.

If you want to use the UID and PWD values for authentication instead of the Windows NTLM account you must use "Trusted_Connection=No" or remove this option from the connection string.

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