1

I am trying to access tables in SQL database in Azure Managed Instance (with IP: xxxx.database.windows.net) from a python script in Azure VM machine but I am getting the Operational Error below. I have tried with 2 different ways below.

Error:

OperationalError: ('08001', '[08001] [Microsoft][SQL Server Native Client 11.0]TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.\r\n (10060) (SQLDriverConnect); [08001] [Microsoft][SQL Server Native Client 11.0]Login timeout expired (0); [08001] [Microsoft][SQL Server Native Client 11.0]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. (10060)')

1st way with connectionString:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mssql+pyodbc://<username>:<password>@<server>/<database>?driver=SQL+Server+Native+Client+11.0")
query = "select * from table"
df=pd.read_sql(query,engine)

2nd way with connectionString:

import pyodbc

server = 'xxx.database.windows.net'
database = 'database'
username = 'username'
password = 'password'   
driver= '{SQL Server Native Client 11.0}'
with pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT TOP 3 name, collation_name FROM sys.databases")
        row = cursor.fetchone()
        while row:
            print (str(row[0]) + " " + str(row[1]))
            row = cursor.fetchone()

Besides, I have also tried to change the driver to drivers below, still no luck.

{ODBC Driver 11 for SQL Server}
{ODBC Driver 13 for SQL Server}
{ODBC Driver 17 for SQL Server}
{SQL Server Native Client 11.0}

Interesting part is, if I try the connect with the same connection string from on-premise machine which is not Azure VM (ex: my local machine or other servers I can RDP to), I can access the database. But when I try on a Azure VM machine, it is timing out. Do you have any ideas how to fix this problem?

Thank you for inputs.

scudarian
  • 13
  • 2
  • Well... ref: [SQL Server Native Client](https://learn.microsoft.com/en-us/sql/relational-databases/native-client/sql-server-native-client) _The SQL Server Native Client (SQLNCLI) remains deprecated and it is not recommended to use it for new development work. Instead, use the new Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) which will be updated with the most recent server features._ – AlwaysLearning Jan 28 '21 at 13:11
  • Try to get the `{ODBC Driver 17 for SQL Server}` working. – AlwaysLearning Jan 28 '21 at 13:12
  • @AlwaysLearning Thank you for your answer. I've already tried {ODBC Driver 17 for SQL Server} as driver in my connectionstring. Sadly, no luck. I've updated the driver from link you sent, too. I think the problem arises from Azure side but can't address where. – scudarian Jan 28 '21 at 14:41
  • Is this [documentation](https://learn.microsoft.com/azure/azure-sql/managed-instance/connect-vm-instance-configure) helpful for you? – Joseph Xu Jan 29 '21 at 09:17
  • I'd be inclined to start with `Driver={ODBC Driver 17 for SQL Server};Server=tcp:xxx.database.windows.net,1433;...` and work from there. – AlwaysLearning Jan 29 '21 at 09:39
  • Do you configed any NSG or firewall rules? – Joseph Xu Feb 01 '21 at 08:58
  • 1
    @JosephXu yeah you are right, firewall caused this problem. Thank you mate. – scudarian Feb 04 '21 at 15:28
  • @scudarian, Glad to help you! Can I post it? – Joseph Xu Feb 05 '21 at 01:34
  • @JosephXu sure man, go ahead. – scudarian Feb 06 '21 at 08:16

1 Answers1

0

So in the end we foud out that firewall caused this problem. We need to check firewall rules first.

Joseph Xu
  • 5,607
  • 2
  • 5
  • 15