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

Question

默认情况下，Python的数据库连接模块sqlalchemy使用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本身抛出的错误消息。这个错误表示凭据不正确。

- vbiqvitovs

Answer 1

为了在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 Server调整ODBC驱动程序版本。

- vbiqvitovs

---

如果我们是在使用Windows凭据登录的计算机上运行脚本，那么上述连接字符串可能会很有用。如果我需要从其他主机运行脚本呢？ - Ravi Chandra

如果使用pymssql怎么办？尝试在“mssql”后面添加“+pyssmql”，但这并不起作用。 - CutePoison

很遗憾，这对我没有起作用，但它指引了我走向正确的道路。这是我必须使用的内容：sqlcon = create_engine('mssql+pyodbc://@' + servername + '/' + dbname + '?trusted_connection=yes&driver=ODBC+Driver+13+for+SQL+Server') - DoloMike

@DoloMike 是的，这个答案已经过时了。 - NoName

@DoloMike，我希望我能再给你的帖子点赞10,000次。 - Chicken Sandwich No Pickles

Answer 2

如果您正在使用受信任的连接/ AD并且没有使用用户名/密码，或者出现以下情况：

SAWarning：未指定驱动程序名称；当使用> DSN-less连接时，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')

- Ray's Web Presence

Answer 3

如果您想使用不同于Windows登录用户的用户连接到MSSQL DB，以下是一个较新的解决方案。如果您从已安装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之前添加Windowsdomain。
您需要安装pymssql 软件包。

- hyamanieu

Answer 4

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

我发现其他答案都很有教育意义，我也发现了关于连接字符串的 SqlAlchemy文档，但我一直无法连接到MS SQL Server Express 19，因为我没有使用用户名或密码，而是使用trusted_connection ='yes'（目前只是在开发阶段）。

然后我在这篇SqlAlchemy文档中找到了通过pyodbc连接字符串（或仅连接字符串）构建连接URL的方法，这也可以通过已知的连接参数构建连接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 <用户名> -P <密码> -Q 'select @@SERVERNAME'

- Thom Ives

---

为了解释您的URL.create函数，可能值得添加更多细节。例如，您可以使用以下内容：url = URL.create("mssql+pyodbc"，host ='servername'，database ="database Name"，query = {"Trusted_Connection":'yes',"driver":"ODBC Driver 17 for SQL Server"}) - brian_ds

请注意，查询仅适用于URL中“？”后的所有内容。命名参数与此之前所需的信息一起使用。 - brian_ds

Answer 5

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

对我来说是有效的

祝好运！

- Rodrigo Peña

---

这并没有回答关于使用“Windows身份验证”的问题，尽管你需要在连接字符串中包含pyodbc。 - user8128167

Answer 6

如果您尝试连接：

1. 没有使用DNS

2. Windows身份验证，用于未在本地托管的服务器。

3. 不使用ODBC连接。

请尝试以下方法：

import sqlalchemy

engine = sqlalchemy.create_engine('mssql+pyodbc://' + server + '/' + database + '?trusted_connection=yes&driver=SQL+Server')

这避免了使用ODBC连接，从而避免了由DPAPI2与DBAPI3冲突引起的pyodbc接口错误。

我建议使用URL创建工具而不是从头开始创建URL。

connection_url = sqlalchemy.engine.URL.create("mssql+pyodbc",database=databasename, host=servername, query = {'driver':'SQL Server'})

engine = sqlalchemy.create_engine(connection_url)

请点击此链接了解如何创建包含SQL Server身份验证的连接字符串（非域，使用用户名和密码）

- Isaacnfairplay