0

I am trying to connect to SQL Server using pyodbc to query some tables for work. I am able to connect to one of our servers and run a SQL query, but the same code does not work for a different server. The error message I get is this:

Error: ('008001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]
SQL Server does not exist or access denied. (17) (SQLDriverConnect)')

This is the code that I'm using:

import pyodbc
import pandas as pd

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=servername;'
                      'Database=databasename;'
                      'Trusted_Connection=yes;')

[rest of code]

The code works for one server, but when I change the server & database names to the other, I get the error message (there are no spelling errors in the server/database names). I am able to access this server through SQL. Additionally, I am able to access this server and to query/update its tables using Excel VBA (even though it will not connect in Python).

Based on my research, there are many possible issues that I do not fully understand. I have tried changing the driver name in the code, and that failed. Since this code is for work, there are security permissions that prevent me from trying out certain solutions.

What could be causing this issue? If somebody could point me in the right direction, that would help me ask better questions of our IT team, if I'm not able to solve it myself.

(I'm using Microsoft SQL Server Management Studio 17 and Python 3.6 with Spyder.)

Clare
  • 1
  • 2
  • Have you tried to connect through Microsoft SQL Server Management Studio to the servers that are not working in your python code? Since it is for your work, maybe other Sql Servers have some IP restrictions have you checked that? – Darko Sep 04 '20 at 14:30
  • Yes, I am able to connect to the server through Microsoft SQL Server Management Studio, just not python. I have updated the question to reflect that. I'll look into IP restrictions, I haven't looked into that. – Clare Sep 04 '20 at 14:36
  • If you're able to connect with Sql management studio, then it's not ip restricted. Also try it with one liner, so without new lines in your code... If it's a non local server it may need tcp: prefix. pyodbc.connect('Driver=SQL Server Native Client 11.0;Server=tcp:{server};UID=username;PWD=password;Database=database;TIMEOUT=5;') – Darko Sep 04 '20 at 14:47
  • I get the same error message combining it in one line. Then I also tried that code by changing the driver & adding my username/pw, and it gave me this error message: "Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) (SQLDriverConnect)" Adding the "tcp" prefix did not seem to help either. Normally I don't need to enter my username or pw to connect to the server, so would I need it in the python code? – Clare Sep 04 '20 at 15:07

1 Answers1

0

Try with this driver:

 SQLserver_settings = {'server': 'servername', 'database': 'database', 'username': 'username', 'password': 'password', 'driver': '{ODBC Driver 17 for SQL Server}'}

cnxn = pyodbc.connect('DRIVER=' + SQLserver_settings['driver'] + ';SERVER=' + SQLserver_settings['server'] + ';PORT=1433;DATABASE=' + SQLserver_settings['database'] + ';UID=' + SQLserver_settings['username'] +';PWD=' + SQLserver_settings['password'])
Hamall
  • 283
  • 3
  • 13
  • I tried changing it to that driver, and I get this error message instead: "'IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)'" – Clare Sep 04 '20 at 15:09
  • 1
    @Clare You need the msodbcsql17 installed to work. https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15 – Damião Martins Sep 04 '20 at 15:17
  • @Damião Martins I installed msodbcsql17 and tried the code, now I get the following error: "[Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53] (53) (SQLDriverConnect)". Do you have any ideas why? – Clare Sep 04 '20 at 21:03
  • @Clare Seen to be something wrong related to connection info, such server name or port number. Check this answer: https://stackoverflow.com/a/29078480/3010548 – Damião Martins Sep 04 '20 at 22:03