如何使用Windows身份验证通过sqlalchemy连接到SQL Server？

sqlalchemy, a db connection module for Python, uses SQL Authentication (database-defined user accounts) by default. If you want to use your Windows (domain or local) credentials to authenticate to the SQL Server, the connection string must be changed.

sqlalchemy是Python的数据库连接模块，默认情况下使用SQL身份验证（数据库定义的用户帐户）。如果要使用Windows（域或本地）凭据对SQL Server进行身份验证，则必须更改连接字符串。

By default, as defined by sqlalchemy, the connection string to connect to the SQL Server is as follows:

默认情况下，由sqlalchemy定义，连接到SQL Server的连接字符串如下所示：

sqlalchemy.create_engine('mssql://*username*:*password*@*server_name*/*database_name*')

This, if used using your Windows credentials, would throw an error similar to this:

如果使用您的Windows凭据，这将抛出类似于此的错误：

sqlalchemy.exc.DBAPIError: (Error) ('28000', "[28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for us
er '***S\\username'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for us
er '***S\\username'. (18456)") None None

In this error message, the code 18456 identifies the error message thrown by the SQL Server itself. This error signifies that the credentials are incorrect.

在此错误消息中，代码18456标识SQL Server自身引发的错误消息。此错误表示凭据不正确。

1 个解决方案

#1

In order to use Windows Authentication with sqlalchemy and mssql, the following connection string is required:

要使用sqlalchemy和mssql进行Windows身份验证，需要以下连接字符串：

OBDC Driver:

OBDC驱动程序：

engine = sqlalchemy.create_engine('mssql://*server_name*/*database_name*?trusted_connection=yes')

SQL Express Instance:

SQL Express实例：

engine = sqlalchemy.create_engine('mssql://*server_name*\\SQLEXPRESS/*database_name*?trusted_connection=yes')

#1