Cannot establish connection to sql server using pyobbc on Windows 7

Question

I am using ActivePython 2.7.2.5 on Windows 7.

When I try to connect to the sql server database with the pyobbc module using the code below, I get the following 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 

trace of TRACEBACK calls:

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

Answer 1

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

You can pass the connection string parameters as keyword arguments to the connect function so that you can use:

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

Answer 2

I had the same error message, and in my case, the problem was that the [SQL Server] driver needed TLS 1.0, which was disabled on my server. Upgrading to the new version of SNAC, SQL Server Native Client 11.0 fixed the problem.

So my connection string looks like this:

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

Answer 3

Various security threats exist with any method. If you use Sql Server authentication, you expose your user code / password in the code. But at least you are processing the same credentials. If you use Windows authentication, you need to ensure that all possible users are configured with permission on the Sql server. With Sql authentication, you can configure only one user, but several people can use these single Sql user permissions.

Answer 4

I encountered this error for another reason.
This is because my server had a "port" separate from the address.
I could fix this by assigning the following value to the Server parameter of the connection string.

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

Note that this is a “comma” and not a “colon” ​​/ “period”