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

问题描述：

sqlalchemy 是 Python 的数据库连接模块，默认使用 SQL 身份验证(数据库定义的用户帐户).如果要使用 Windows(域或本地)凭据对 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的定义，连接到SQL Server的连接字符串如下:

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

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

如果使用您的 Windows 凭据，会抛出类似以下的错误:

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

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

在此错误消息中，代码 18456 标识了 SQL Server 本身抛出的错误消息.此错误表示凭据不正确.

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.

答

为了在 sqlalchemy 和 mssql 中使用 Windows 身份验证，需要以下连接字符串:

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

ODBC 驱动程序:

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

SQL Express 实例:

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

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

相关推荐