pyodbc connection within SQL Server 2017

This is my first post on stackoverflow, so please bear with me if I doing something wrong.

I'm currently trying to achieve a Python script which reads data from a CSV file, transforms it into a JSON Object and stores it in an SQL Server table. Everything is working fine if I do this directly in Python, I have a fully working Python script which reads the CSV and stores the data via pyodbc on SQL Server.

Unfortunately, when I try to use a similar script in sp_execute_external_script I get an error that the connection could not get established.

My T-SQL code:

DECLARE @Python as nvarchar(max)

SET @Python = N'
import pyodbc

import datetime as datetime

conn_str = (
    r''DRIVER={ODBC Driver 17 for SQL Server};''
    r''SERVER=xxx.xxx.xxx.xxx;''
    r''DATABASE=xxxx;''
    r''UID=xxxxxx;''
    r''PWD=xxxx;''
)

cnxn = pyodbc.connect(conn_str)
'

EXEC sp_execute_external_script
@language = N'Python',
@script = @Python ,
@input_data_1 = N'',
@input_data_1_name = N''

Error message

Meldung 39004, Ebene 16, Status 20, Zeile 2 Unerwarteter "Python"-Skriptfehler beim Ausführen von "sp_execute_external_script" mit HRESULT 0x80004004. Meldung 39019, Ebene 16, Status 2, Zeile 2 Externer Skriptfehler:

Error in execution. Check the output for more information. Traceback (most recent call last): File "", line 5, in File "E:\Program Files\Microsoft SQL Server\MSSQL14.CWDEV\MSSQL\ExtensibilityData\CWDEV01\6F73A5E0-4F82-4FEA-A5DA-7A8E7D8778D2\sqlindb.py", line 53, in transform cnxn = pyodbc.connect(conn_str) pyodbc.Error: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes-Anbieter: Es konnte keine Verbindung zu SQL Server hergestellt werden [1326]. (1326) (SQLDriverConnect)')

SqlSatelliteCall error: Error in execution. Check the output for more information. STDOUT-Meldung(en) aus dem externen Skript: SqlSatelliteCall function failed. Please see the console output for more information. Traceback (most recent call last): File "E:\Program Files\Microsoft SQL Server\MSSQL14.CWDEV\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 406, in rx_sql_satellite_call rx_native_call("SqlSatelliteCall", params) File "E:\Program Files\Microsoft SQL Server\MSSQL14.CWDEV\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 291, in rx_native_call ret = px_call(functionname, params) RuntimeError: revoscalepy function failed.

At the moment I'm just trying to make a connection to the destination server. Btw, the code is not running on the destination server, it will be executed on a different server. My idea is to use sp_execute_external_script with Python on a particular SQL Server to migrate data out of flat files and to store it on different destination SQL Servers.

Any advice will highly appreciated.

Many thanks

I figured it out.

There was a outgoing rule in windows firewall which blocks the network access for pyodbc connection.

firewall outgoing rules

After disabling it, everything worklike a charm.

Firewall rules for machine learning services is described here:

https://docs.microsoft.com/de-de/sql/machine-learning/security/firewall-configuration?view=sql-server-2016

Regards,