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

Question

sqlalchemy是一个用于Python的数据库连接模块，默认情况下使用SQL身份验证（也就是数据库里定义的用户账户）。如果你想用自己的Windows（无论是域账户还是本地账户）凭据来连接SQL Server，就需要修改连接字符串。

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

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

如果你用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

在这个错误信息中，代码18456表示这是SQL Server自己抛出的错误信息。这个错误说明你的凭据不正确。

Answer 1

pyodbc

我觉得你需要在 mssql 后面加上：

"+pyodbc"

试试这个：

from sqlalchemy import create_engine

engine = create_engine("mssql+pyodbc://user:password@host:port/databasename?driver=ODBC+Driver+17+for+SQL+Server")

cnxn = engine.connect()

对我来说是有效的

祝好运！

Answer 2

从你的 pyodbc 连接字符串或已知连接参数创建 SqlAlchemy 连接 URL

我发现其他的回答都很有帮助，同时也觉得 SqlAlchemy 文档关于连接字符串的部分很实用，但我一直无法成功连接到 MS SQL Server Express 19，因为我没有使用用户名和密码，而是设置了 trusted_connection='yes'（这时候只是进行开发）。

后来我在 SqlAlchemy 文档中发现了 这个方法，它讲述了如何从 pyodbc 连接字符串（或者说连接字符串）构建连接 URL，这也可以看作是从已知连接参数生成的连接字符串（也就是说，这个连接字符串不一定要在 pyodbc 中使用）。因为我知道我的 pyodbc 连接字符串是有效的，所以这个方法对我来说似乎可行，结果也确实成功了！

这个方法让你不再需要猜测如何正确格式化你传给 SqlAlchemy 的 create_engine 方法的内容。如果你知道你的连接参数，只需按照文档中的示例，把这些参数放入一个简单的字符串中，sqlalchemy.engine 模块中的 URL 类的 create 方法会为你处理好格式。

下面的示例代码可以直接运行，假设有一个名为 master 的数据库和一个名为 table_one 的表，表的结构如下所示。此外，我还使用 pandas 来导入我的表数据。否则，我们需要使用上下文管理器来管理连接数据库的过程，然后再关闭连接，具体可以参考 这里的 SqlAlchemy 文档。

import pandas as pd
import sqlalchemy
from sqlalchemy.engine import URL

# table_one dictionary:
table_one = {'name': 'table_one',
    'columns': ['ident int IDENTITY(1,1) PRIMARY KEY',
        'value_1 int NOT NULL',
        'value_2 int NOT NULL']}

# pyodbc stuff for MS SQL Server Express
driver='{SQL Server}'
server='localhost\SQLEXPRESS'
database='master'
trusted_connection='yes'

# pyodbc connection string
connection_string = f'DRIVER={driver};SERVER={server};'
connection_string += f'DATABASE={database};'
connection_string += f'TRUSTED_CONNECTION={trusted_connection}'

# create sqlalchemy engine connection URL
connection_url = URL.create(
    "mssql+pyodbc", query={"odbc_connect": connection_string})

""" more code not shown that uses pyodbc without sqlalchemy """

engine = sqlalchemy.create_engine(connection_url)

d = {'value_1': [1, 2], 'value_2': [3, 4]}
df = pd.DataFrame(data=d)

df.to_sql('table_one', engine, if_exists="append", index=False)

更新

假设你在 Linux 机器上安装了 SQL Server Express。你可以使用以下命令来确保你使用的是正确的字符串：

1. 查看驱动程序：odbcinst -q -d
2. 查看服务器：sqlcmd -S localhost -U <username> -P <password> -Q 'select @@SERVERNAME'

Answer 3

如果你想用一个和你在Windows上登录的用户不同的用户来连接MSSQL数据库，这里有一个更新的回答。如果你是从安装了FreeTDS的Linux机器上连接，这个方法也同样有效。

我在Windows 10和Ubuntu 18.04上用Python 3.6和3.7都成功了，下面是我用的代码：

import getpass
from sqlalchemy import create_engine
password = getpass.getpass()
eng_str = fr'mssql+pymssql://{domain}\{username}:{password}@{hostip}/{db}'
engine = create_engine(eng_str)

需要注意的是，连接时要在\username前面加上Windows的domain。另外，你还需要安装pymssql这个包。

Answer 4

如果你在使用一个可信的连接或者活动目录（AD），并且没有使用用户名和密码，或者你看到以下内容：

SAWarning: 没有指定驱动程序名称；当使用无DSN连接时，PyODBC会出现这个警告 "没有指定驱动程序名称；"

那么这个方法应该可以解决问题：

from sqlalchemy import create_engine

server = <your_server_name>

database = <your_database_name>

engine = create_engine('mssql+pyodbc://' + server + '/' + database + '?trusted_connection=yes&driver=ODBC+Driver+13+for+SQL+Server')

Answer 5

为了在使用sqlalchemy和mssql时进行Windows身份验证，你需要以下的连接字符串：

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

---

更新：

@DoloMike指出之前提供的解决方案已经过时，可能无法按预期工作。以下是根据他评论的修正方案：

engine = create_engine('mssql+pyodbc://@' + servername + '/' + dbname + '?trusted_connection=yes&driver=ODBC+Driver+13+for+SQL+Server')

根据你安装的sql服务器，调整ODBC驱动程序的版本。