ODBC连接到突触连接到突触专用SQL池db通过pyodbc的Spark Notebook

发布于 2025-01-22 16:09:20 字数 1908 浏览 2 评论 0原文

我有一个专用的Synapse SQL池，我想在其中连接到数据库。我想使用在同一Synapse实例中的Pyspark笔记本电脑上运行的Python连接到数据库。

我正在使用Microsoft文档在这里进行此操作，我的代码是以下内容：

import pyodbc 
cnxn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};Server=tcp:SYNAPSENAME.sql.azuresynapse.net,1433;Database=DBNAME;Uid=XXXX-XXXXX-XXXXX-XXXX;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryIntegrated')
cursor = cnxn.cursor()

#Sample select query
cursor.execute("SELECT @@version;") 
row = cursor.fetchone() 
while row: 
    print(row[0])
    row = cursor.fetchone()

我在连接字符串下的Azure的专用SQL池页面中获得了连接字符串/odbc/odbc（包括node.js）（Azure Active Directory集成身份验证），

但是，当我运行它时，我会收到以下错误：

OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

trackback（最新呼叫最后）：

我尝试使用早期版本运行相同的代码在驱动程序（13）中，但会得到以下错误：

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL 
Server' : file not found (0) (SQLDriverConnect)")
Traceback (most recent call last): pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server' : file not found (0) (SQLDriverConnect)")

我相信这至少表明V17的驱动程序已正确安装。我还将UID更改为我的用户名 [emagy＆nbsp;与第一个相同的错误代码相同。

我想知道问题是什么？

我需要密码auth吗？突触肯定应该处理所有身份验证吗？
SQL池无法使用此连接吗？

任何帮助都会很棒， joao

原文

I have a dedicated Synapse SQL pool, within which I want to Connect to a database. I want to connect to the database using Python running on a pySpark Notebook inside the same Synapse Instance.

I am using the Microsoft documentation here to go about this and my code is the following:

import pyodbc 
cnxn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};Server=tcp:SYNAPSENAME.sql.azuresynapse.net,1433;Database=DBNAME;Uid=XXXX-XXXXX-XXXXX-XXXX;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryIntegrated')
cursor = cnxn.cursor()

#Sample select query
cursor.execute("SELECT @@version;") 
row = cursor.fetchone() 
while row: 
    print(row[0])
    row = cursor.fetchone()

I obtained The connection string from the Dedicated SQL pool page in Azure Under Connection Strings/ODBC/ODBC (Includes Node.js) (Azure Active Directory integrated authentication)

However, when I run it I'm getting the following error:

OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

Traceback (most recent call last):

I have Tried running the same code with an earlier version of the Driver (13), but get the following error:

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL 
Server' : file not found (0) (SQLDriverConnect)")
Traceback (most recent call last): pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server' : file not found (0) (SQLDriverConnect)")

I believe this at least indicates the driver for v17 is properly installed. I have also changed the UID to be my username [email protected] but get the same error code as the first one.

I'm wondering what could be the issue?

Do I need Password Auth? Surely Synapse should handle all the authentication alread?
Is this Connection not possible for SQL pools?

Any help would be great,
Joao

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

你对谁都笑 2025-01-29 16:09:20

请检查您的ODBC驱动程序版本。

如果您的ODBC驱动程序的版本为17.1或以后，则可以使用
ODBC驱动程序的Azure Active Directory交互模式通过
pyodbc。

如果版本不错，请尝试在给定的连接字符串中使用密码：

Driver={SQL Server Native Client 11.0};Server=tcp:{your_server}.sql.azuresynapse.net,1433;Database={your_database};Uid={your_user_name};Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

请参阅下面的有用链接：

odbc连接字符串示例示例

Microsoft odbc odbc驱动程序for windows

Please check your ODBC driver version.

If your version of the ODBC driver is 17.1 or later, you can use the
Azure Active Directory interactive mode of the ODBC driver through
pyODBC.

If the version is fine, try to use the Password in the given connection string:

Driver={SQL Server Native Client 11.0};Server=tcp:{your_server}.sql.azuresynapse.net,1433;Database={your_database};Uid={your_user_name};Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

Refer below helpful links:

ODBC connection string example

Microsoft ODBC Driver for SQL Server on Windows

回复收藏 0 原文

~没有更多了~