Connect to database in Remote machine - python - SQL Server

1
votes

I have a database server which can be accessed by Remote desktop login to the server machine. This is what we do manually:

Login with Remote desktop to the machine from local. Open Database client in the connected machine. Then connect to database.

Now, I need to connect to this DB using python.

What I already tried?.. below works for all DB that I connect without remote.

conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};SERVER=<server name>;database=<DB name>;UID=<user>;PWD=<pwd>")

Error:

pyodbc.OperationalError: ('08001', u'[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) (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. (53)')

2
pythonsql-server
[link]stackoverflow.com/questions/33725862/… – Pawan kumar
I can't connect to it directly. I need to take a remote login to a machine and then open the SQL Assistant client there to access it manually. – Indrajit Dutta
In most cases the two servers/computers would be in the same network and can talk to each other over IP. I have a similar setup where i access the SQL server over RDP, but since they are in the same network and no firewalls between I can run a python script to reach the SQL server by its hostname and run queries remotely – Pawan kumar

2 Answers

1
votes

before you use below code in python, you have to follow this guide to configure your SQL server https://knowledgebase.apexsql.com/configure-remote-access-connect-remote-sql-server-instance-apexsql-tools/

note: 1434 is my UDP port in Inbound Rules

conn = pyodbc.connect('DRIVER={SQL Server};SERVER = your_server_ipv4,1434;DATABASE=B_SQL;UID=sa;PWD=123456;')

cursor = conn.cursor()
#cursor.execute("DELETE FROM my_table") 
for index, row in df.iterrows():
    #print(row)
    cursor.execute("INSERT INTO my_table([Name],[Volume]) values(?,?)", row['Name'], row['Volume']) 

conn.commit()
cursor.close()
conn.close()

it works very well for me!

0
votes

Can you connect to your SQL Server from another application, including Excel?

If you cannot, I would check the following:

  1. Remote into the server and open the SQL Server Configuration Manager.

    There should be a section labeled SQL Server Network Configuration that will have an entry for "Protocols for ". If you click on that entry, you will see which protocols are enabled for your database.

  2. Click on TCP/IP and select properties. Under the IP addresses, each IP listed may need to have a port listed.

  3. Once that is done, make sure that port is enabled in your firewall on the server for both Inbound and Outbound.

When I managed a SQL-driven application, we normally got the Named Pipes error when there was other connection issues such as firewall issues or the SQL Server Browser or instance not running.

If this is not sufficient to resolve the issue, there are a ton of other options on MSSQL Tips.