SQLServer Machine Learning Python : unable to open an ODBC connection to another server from script

Question

A previously working stored procedure isn't working anymore.

Server_A is running SQLServer 2019 Std 64-bit. Python Machine Learning extension is installed as well as "ODBC Driver 17 for SQL Server ODBC v17.5.1.1".

I run this code in one of its stored procedure:

exec sp_execute_external_script
       @language = N'Python'
       , @script = N'
import pyodbc

con = pyodbc.connect(server="server_B", driver="ODBC Driver 17 for SQL Server", user="reports", password="***") 

OutputDataSet = pandas.DataFrame()';

and get this error:

Traceback (most recent call last):   
File "<string>", line 5, in <module>   
File "D:\ProgramData\MSSQLSERVER\Temp-PY\Appcontainer1\E517C0B7-A0B6-477B-9318-4A1BEF11D721\sqlindb_0.py", line 35, in transform     con = pyodbc.connect(server="server_B", driver="ODBC Driver 17 for SQL Server", user="reports", password="***") 

pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [65].  (65) (SQLDriverConnect); 
[08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (65)')

Server_B is running SQL Server 2019 Dev 64-bits

I expected the code to be able to open the ODBC connection since:

• running the same Python code from the prompt connects without issue (same user, password as in the SP). This also means that the server_B is accepting connections.
• I'm able to run the same code from a SQL client application as well without any error (located on server_A) --> no firewall exceptions are reported & port 1433 is open.

How to investigate further ?

PS: I did reboot both servers to réinit processes.

Answer 1

Hi @Oli

A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible.

This error usually means that the client can't find the SQL Server instance. This issue occurs when at least one of the following problems exists:

• The name of the computer hosting SQL Server is incorrect.
• The instance doesn't resolve the correct IP.
• The TCP port number isn't specified correctly.

Please refer to this doc for more details: A network-related or instance-specific error occurred while establishing a connection to SQL Server.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.