如何使用Pyodbc通过证书连接SQL Server，无需密码及Windows凭据

完全可以实现用证书身份验证连接SQL Server，不用Windows集成认证，也不用在连接字符串里写明文密码。不过需要先在SQL Server端和客户端做一些配置，下面一步步来：

一、SQL Server端的必要配置 #

首先得在服务器上开启证书身份验证的支持：

1. 启用TLS加密：打开SQL Server配置管理器，找到"SQL Server网络配置" -> 你的实例的"协议"，右键属性，在"标志"选项卡设置"强制加密"为"是"，然后在"证书"选项卡选择服务器要用的TLS证书（如果没有的话，需要先申请或生成服务器证书）。
2. 映射客户端证书到SQL登录名：
   • 把你的客户端证书（.crt或.pem）导入到SQL Server所在机器的本地计算机存储的"受信任的根证书颁发机构"（如果是自签名证书的话）。
   • 在SQL Server中创建关联该证书的登录名，比如：

     CREATE CERTIFICATE ClientCert
     FROM FILE = 'C:\path\to\client.crt';
     
     CREATE LOGIN ClientCertLogin
     FROM CERTIFICATE ClientCert;
     
     -- 给登录名分配数据库权限
     USE YourDatabase;
     CREATE USER ClientCertUser FOR LOGIN ClientCertLogin;
     ALTER ROLE db_datareader ADD MEMBER ClientCertUser;
     

3. 确保SQL Server服务账户有权限访问证书：在证书管理器里，右键客户端证书 -> 所有任务 -> 管理私钥，添加SQL Server服务账户并赋予"读取"权限。

二、客户端pyodbc连接代码 #

你需要使用ODBC Driver 17及以上版本（这个版本开始支持客户端证书参数），连接字符串里不需要指定UID/PWD或者Trusted_Connection=yes，而是通过指定客户端证书和私钥来完成身份验证。

示例代码如下：

serverName = "your_server_name"
dbName = "your_database_name"
client_cert = "/path/to/your/client.crt"  # 客户端证书路径
client_private_key = "/path/to/your/client.key"  # 客户端私钥路径（如果证书和私钥分离的话）

# 建立连接
cnx = pyodbc.connect(
    f"DRIVER={{ODBC Driver 17 for SQL Server}};"
    f"SERVER={serverName};"
    f"DATABASE={dbName};"
    f"Encrypt=yes;"  # 必须启用加密，证书验证依赖TLS
    f"TrustServerCertificate=no;"  # 验证服务器证书（如果是自签名服务器证书，可临时设为yes，但生产环境不建议）
    f"ClientCertificate={client_cert};"
    f"ClientKey={client_private_key};"
)

# 测试连接
cursor = cnx.cursor()
cursor.execute("SELECT @@VERSION")
print(cursor.fetchone()[0])
cursor.close()
cnx.close()

注意事项 #

• 如果你的客户端证书和私钥合并在一个.pfx文件里，直接用ClientCertificate指定pfx路径即可；如果pfx设置了密码，还是需要ClientKeyPassword参数，但你可以生成无密码的pfx来避免。
• 确保客户端机器信任SQL Server的服务器证书：要么用CA签发的证书，要么把服务器证书导入到客户端的"受信任的根证书颁发机构"存储里。
• 一定要用ODBC Driver 17+，旧版本不支持ClientCertificate和ClientKey参数。

内容的提问来源于stack exchange，提问作者user8701560