CODEWITHSUNDEEP
pythonsql-server
Indrajit Dutta
Indrajit Dutta

Reputation: 41

Connect to database in Remote machine - python - SQL Server

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)')

Upvotes: 1

Views: 7309

Answers (2)

PyBoss
PyBoss

Reputation: 631

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!

Upvotes: 1

SQ LoBue
SQ LoBue

Reputation: 11

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.

Upvotes: 0

Related Questions