Python 错误 28000：用户 DOMAIN\\user with pyodbc 登录失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37692780/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error 28000: Login failed for user DOMAIN\\user with pyodbc
提问by Alex
I am trying to use Python to connect to a SQL database by using Window authentication. I looked at some of the posts here (e.g., here), but the suggested methods didn't seem to work.
我正在尝试使用 Python 通过使用 Window 身份验证连接到 SQL 数据库。我查看了这里的一些帖子（例如，这里），但建议的方法似乎不起作用。
For example, I used the following code:
例如，我使用了以下代码：
cnxn = pyodbc.connect(driver='{SQL Server Native Client 11.0}',
server='SERVERNAME',
database='DATABASENAME',
trusted_connection='yes')
But I got the following error:
但我收到以下错误：
Error: ('28000', "[28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]
Login failed for user 'DOMAIN\username'. (18456) (SQLDriverConnect); [28000] [Microsoft]
[SQL Server Native Client 11.0][SQL Server]Login failed for user 'DOMAIN\username'.
(18456)")
(Note that I replaced the actual domain name and user name with DOMAINand usernamerespectively, in the error message above.)
（请注意，我在上面的错误消息中分别用DOMAIN和替换了实际域名和用户名username。）
I also tried using my UIDand PWD, which led to the same error.
我也尝试使用 my UIDand PWD，这导致了同样的错误。
Lastly, I tried to change the service account by following the suggestion from the link above, but on my computer, there was no Log Ontab when I went to the Propertiesof services.msc.
最后，我试图通过以下从上面的链接的建议，更改服务帐户，但我的电脑上，没有Log On标签，当我去Properties的services.msc。
I wonder what I did wrong and how I can fix the problem.
我想知道我做错了什么以及如何解决问题。
回答by Gord Thompson
Connecting from a Windows machine:
从 Windows 机器连接：
With Microsoft's ODBC drivers for SQL Server, Trusted_connection=yestells the driver to use "Windows Authentication" and your script will attempt to log in to the SQL Server using the Windows credentials of the user running the script. UIDand PWDcannot be used to supply alternative Windows credentials in the connection string, so if you need to connect as some other Windows user you will need to use RUNASto run the Python script as that other user..
使用 Microsoft 的 SQL Server ODBC 驱动程序，Trusted_connection=yes告诉驱动程序使用“Windows 身份验证”，您的脚本将尝试使用运行脚本的用户的 Windows 凭据登录 SQL Server 。UID并且PWD不能用于在连接字符串中提供替代 Windows 凭据，因此如果您需要以其他 Windows 用户身份进行连接，您将需要使用RUNAS该其他用户身份运行 Python 脚本。
If you want to use "SQL Server Authentication" with a specific SQL Server loginspecified by UIDand PWDthen use Trusted_connection=no.
如果要将“SQL Server 身份验证”与指定的特定SQL Server 登录名一起使用UID，PWD然后使用Trusted_connection=no.
Connecting from a non-Windows machine:
从非 Windows 机器连接：
If you need to connect from a non-Windows machine and the SQL Server is configured to only use "Windows authentication" then Microsoft's ODBC drivers for SQL Server will require you to use Kerberos. Alternatively, you can use FreeTDS ODBC, specifying UID, PWD, and DOMAINin the connection string, provided that the SQL Server instance is configured to support the older NTLM authentication protocol.
如果您需要从非 Windows 机器连接并且 SQL Server 配置为仅使用“Windows 身份验证”，那么 Microsoft 的 SQL Server ODBC 驱动程序将要求您使用 Kerberos。或者，您可以使用 FreeTDS ODBC，在连接字符串中指定UID、PWD和DOMAIN，前提是 SQL Server 实例配置为支持较旧的 NTLM 身份验证协议。
回答by Merlin
Try this cxn string:
试试这个 cxn 字符串：
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;PORT=1433;DATABASE=testdb;UID=me;PWD=pass')
回答by user1761806
I tried everything and this is what eventually worked for me:
我尝试了一切，这最终对我有用：
import pyodbc
driver= '{SQL Server Native Client 11.0}'
cnxn = pyodbc.connect(
Trusted_Connection='Yes',
Driver='{ODBC Driver 11 for SQL Server}',
Server='MyServer,1433',
Database='MyDB'
)
回答by Deepak Venugopal
I had similar issue while connecting to the default database (MSSQLSERVER). If you are connecting to the default database, please remove the
我在连接到默认数据库 (MSSQLSERVER) 时遇到了类似的问题。如果您连接到默认数据库，请删除
database='DATABASENAME',
数据库='数据库名'，
line from the connection parameters section and retry.
连接参数部分中的行并重试。
Cheers, Deepak
干杯，迪帕克
回答by JAFER
The first option works if your credentials have been stored using the command prompt. The other option is giving the credentials (UId, Psw) in the connection.
如果您的凭据已使用命令提示符存储，则第一个选项有效。另一个选项是在连接中提供凭据（UId、Psw）。
The following worked for me:
以下对我有用：
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=yourServer;DATABASE=yourDatabase;UID=yourUsername;PWD=yourPassword')
回答by Rakesh Dara
import pyodbc #For python3 MSSQL
cnxn = pyodbc.connect("Driver={SQL Server};" #For Connection
"Server=192.168.0.***;"
"PORT=1433;"
"Database=***********;"
"UID=****;"
"PWD=********;")
cursor = cnxn.cursor() #Cursor Establishment
cursor.execute('select site_id from tableName') #Execute Query
rs = cursor.fetchall()
print(rs)
回答by marksy_91
A slightly different use case than the OP, but for those interested it is possible to connect to a MS SQL Server database using Windows Authentication for a different user account than the one logged in.
与 OP 的用例略有不同，但对于那些感兴趣的人，可以使用 Windows 身份验证连接到 MS SQL Server 数据库，以获取与登录用户不同的用户帐户。
This can be achieved using the python jaydebeapi module with the JDBC JTDS driver. See my answer herefor details.
这可以通过使用带有 JDBC JTDS 驱动程序的 python jaydebeapi 模块来实现。有关详细信息，请参阅我的答案here。
回答by tanay sit
Trusted_connection=nodid not helped me. When i removed entire line and added UID, PWDparameter it worked. My takeaway from this is remove
Trusted_connection=no没有帮助我。当我删除整行并添加UID,PWD参数时它起作用了。我的结论是删除
