在Jupyter Notebook中连接到SQL DB时出现错误 [英] I am getting an error while connecting to an sql DB in Jupyter Notebook

问题描述

在与SQL DB连接时，在jupyter上遇到以下代码时遇到的问题.

Facing issue with below code on jupyter while connecting with SQL DB.

    con = pyodbc.connect('Driver={ODBC Driver 13 for SQL 
    Server};Server='+server+';Database='+database+';Uid='+username+'
    ;Pwd='+passwor 
    d+';Encrypt=yes;TrustServerCertificate=no;Connection 
    Timeout=30;Authentication=ActiveDirectoryPassword')

    Error Description Below:
    OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 13 for SQL 
    Server]SSL Provider: [error:140A90F1:lib(20):func(169):reason(241)] (-1) 
    (SQLDriverConnect)')

推荐答案

假定您正在使用 Microsoft Azure笔记本，我尝试成功重现您的问题，如下所示.

Assumed that you are using Microsoft Azure Notebooks, I tried to reproduce your issue successfully, as below.

这是由您使用的连接字符串引起的.

It was caused by the connection string you used.

在Azure门户上，您将看到三个ODBC连接字符串，如下图.

On Azure portal, you will see three ODBC connection string, as the figure below.

很明显，您正在尝试使用第二个，该第二个需要Azure Active Directory帐户的用户名和密码，如官方文档

Obviously, you are trying to use the second one which required the username and password of an Azure Active Directory account, as the figure said from the offical document Using Azure Active Directory with the ODBC Driver

并且根据同一文档，ODBC驱动程序版本13.1似乎不支持此身份验证方式，请更改为将ODBC驱动程序版本17与{ODBC Driver 17 for SQL Server}一起使用.

And according to the same document, the ODBC Driver version 13.1 seems to not support this authentication way, please change to use ODBC Driver version 17 with {ODBC Driver 17 for SQL Server}.

首先，请检查已安装的msodbcsql17.

And first, please check msodbcsql17 which has been installed.

但是，我尝试使用msodbcsql17进行连接，但由于登录超时错误而失败.甚至我也尝试通过!pip install pyodbc --upgrade

However, I tried to connect using msodbcsql17, it failed by login timeout error. Even I tried to upgrade pyodbc from 3.1.1 to 4.0.26 via !pip install pyodbc --upgrade

因此，如果必须对SQL数据库连接的AAD进行身份验证，请尝试并可能获得成功的连接.或者切换到第一种连接方式，使用SQL数据库用户名和密码通过pyodbc==3.1.1(升级到4.0.26会导致另一个错误)和{ODBC Driver 13 for SQL Server}进行连接，

So please try and may get the successful connection, if you have to authenticate AAD for SQL Database connection. Or switch to the first connection way, to use SQL Database username and password to connect by pyodbc==3.1.1(upgrade to 4.0.26 that will cause another error) and {ODBC Driver 13 for SQL Server}, it works for me.

希望有帮助.

这篇关于在Jupyter Notebook中连接到SQL DB时出现错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！