Cannot establish connection to sql-server using pyodbc on Windows 7

Question

I'm using ActivePython 2.7.2.5 on Windows 7.

While trying to connect to a sql-server database with the pyodbc module using the below code, I receive the subsequent Traceback. Any ideas on what I'm doing wrong?

CODE:

import pyodbc
driver = 'SQL Server'
server = '**server-name**'
db1 = 'CorpApps'
tcon = 'yes'
uname = 'jnichol3'
pword = '**my-password**'

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=server;DATABASE=db1;UID=uname;PWD=pword;Trusted_Connection=yes')
cursor = cnxn.cursor()
cursor.execute("select * from appaudit_q32013")
rows = cursor.fetchall()
for row in rows:
    print row

TRACEBACK:

Traceback (most recent call last):
  File "pyodbc_test.py", line 9, in <module>
    cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=server;DATABASE=db1;UID=uname;PWD=pword;Trusted_Connection=yes')
pyodbc.Error: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect); [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (53)')

Pardue

In my case following driver names worked for me. 'SQL Server Native Client 11.0' and 'ODBC Driver 11 for SQL Server' – Francie

@HammadHassan: Those are not listed as datasources when I use pyodbc.dataSources(). Do you know how to import/add data sources? – Parsimonious

@Parsimonious As far as I remember you have to install something to make ODBC/SQL Server... drivers available. – Francie

Answer 1

You're using a connection string of 'DRIVER={SQL Server};SERVER=server;DATABASE=db1;UID=uname;PWD=pword;Trusted_Connection=yes', you're trying to connect to a server called server, a database called db1, etc. It doesn't use the variables you set before, they're not used.

It's possible to pass the connection string parameters as keyword arguments to the connect function, so you could use:

cnxn = pyodbc.connect(driver='{SQL Server}', host=server, database=db1,
                      trusted_connection=tcon, user=uname, password=pword)

Cunha

@JamesNicholson You'll also want to remove the trusted_connection attribute from your connection string since you are supplying a user name and password. You can use either Windows authentication (trusted_connection attribute), or SQL Server authentication (user and password attributes), but not both. In my testing, if both are supplied, the connection defaults to Windows authentication. – Dani

@beargle can you submit an answer that provides code examples? Would be very helpful to see how you construct the connection string in both cases. Thanks. – Pardue

@JamesNicholson Please see this existing answer showing pyodbc.connect examples for both SQL and Windows authentication. – Dani

@beargle thanks. Are there security risks associated with using one or the other? – Pardue

I would also suggest checking which driver you are using. In my case I had to specify {ODBC Driver 17 for SQL Server} – Sarilda

Answer 2

I had the same error message and in my case the issue was the [SQL Server] drivers required TLS 1.0 which is disabled on my server. Changing to the newer version of the SNAC, SQL Server Native Client 11.0 fixed the problem.

So my connection string looks like:

cnxn = pyodbc.connect(driver='{SQL Server Native Client 11.0}', 
                      host=server, database=db1, trusted_connection=tcon,
                      user=uname, password=pword)

Decamp

Answer 3

I had faced this error due to another reason.
It was because my server had a "port" apart from the address.
I could fix that by assigning the following value to "Server" parameter of the connection string.

"...;Server=<server_name>,<port#>;..."

Note that it is a 'comma' and not 'colon'/'period'

Franconia

Answer 4

I had the same issue today. I was using localhost in the connectionstring. Got rid of the issue by replacing localhost woth 'server name',. My db and application are running in the same machine.

If you don't have server name go to Sql server management studio and execute below query, which will give you the server name.

SELECT @@SERVERNAME

The connection string look as below

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

Raphaelraphaela

Answer 5

cnxn = pyodbc.connect(driver='{SQL Server}', host=server, database=db1,
                       user=uname, password=pword)

print(cnxn)

I removed "Trusted_Connection" part and it worked for me.

Thoma

Answer 6

Different security risks exist with either method. If you use Sql Server authentication you expose your userid/password in the code. But at least you process with the same credentials. If you use Windows authentication you have to insure all the possible users are setup with the right permission in the Sql server. With Sql authentication you can setup just one user but multiple people can use that one Sql User permissions wise.

Morena

Answer 7

First we need to be clear with the syntax-

cnxn = pyodbc.connect('DRIVER={SQL server};Server=server_name;DATABASE=database_name;Port=Port_Number;UID=username;PWD=password;')

Few Points-

• Driver- You can check by printing print(pyodbc.drivers()) and from there you can pick.
• Server- For server name you can check by running query select @@Servername
• Database- For database you can check in your database to which you want to connect.
• Port- Mostly for MS SQL Server it would be 1433.
• UID- Consider key should be UID, don't change with other key I got failures due to this reason. Paste the user name.
• PWD- Paste the password.

NOTE- Don't change the KEY otherwise it will give error.

And Trusted_Connection=yes is used for Windows Authentication.

Fiction