1

Recently I'm trying to connect to a SQL Server though pyodbc but I'm having some troubles with the connection string. I already tried as suggested on this previous question: Pyodbc error Data source name not found and no default driver specified paradox, creating a .dsn file and trying to implement the procedure's output on the string, but stil get the same error message: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

This is what I'm doing so far:

conn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};
                      'WSID={BRRIO-xxxx};'
                      'APP={Microsoft® Windows® Operating System};'
                      'Trusted_Connection=Yes;'
                      'SERVER=BRRIO-xxxx\xxx;'
                      'Database=xxx_Data;'
                      'UID="xxxx";'
                      'PWD="xxxx";'
                      )

and this is what my .dsn file looks like:

DRIVER={ODBC Driver 13 for SQL Server};
WSID={BRRIO-xxxx};
APP={Microsoft® Windows® Operating System};
Trusted_Connection=Yes;
SERVER=BRRIO-xxxx\xxx

Any help is really appreciated!

Pedro de Sá
  • 760
  • 6
  • 19
  • 1
    Check the list returned by `pyodbc.drivers()` to see what ODBC drivers are available to your Python app. – Gord Thompson Feb 04 '21 at 14:20
  • ['SQL Server', 'SQL Server Native Client 11.0', 'ODBC Driver 17 for SQL Server'] Do I need to manually install 13? – Pedro de Sá Feb 04 '21 at 14:24
  • On the virtual machine where the server is located, the ODBC drivers are ['SQL Server', 'SQL Server Native Client 11.0', 'ODBC Driver 13 for SQL Server'] and on my machine the drivers are listed on my comment above. Perhaps the problem is realted to that? – Pedro de Sá Feb 04 '21 at 14:49
  • Changing the driver to a common one between both environments ("SQL Server", for example), I get this error: `OperationalError: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB] SQL Server does not exist or access is denied. (17) (SQLDriverConnect); [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (67)')` – Pedro de Sá Feb 04 '21 at 14:51
  • In your connection string try `'SERVER=BRRIO-xxxx\\xxx;'` instead of `'SERVER=BRRIO-xxxx\xxx;'` – Gord Thompson Feb 04 '21 at 15:01

1 Answers1

3

For anyone who's having some troubles with this, I found the solution following the steps here: https://www.sqlserverlogexplorer.com/database-does-not-exist-access-denied/

Basically for me it was a firewall problem, where the port 1433 was blocked. Also, make sure you are using the correct driver for you case (pyodbc.drivers(), as suggested by @Gord Thompson) and check for remote server connections on yours SQL Server (SQL Server Management Studio > Go to SQL Server instance property > Connections > check Allow remote connection to this server).

Pedro de Sá
  • 760
  • 6
  • 19