pyodbc connecting to SQL Server 2012 problems

Question

Below is the traceback. I've read all the other SO threads, googled for over two hours, and cannot figure this out. Here is what I have tried:

• Both SQL Authentication and Windows Authentication versions of the connection string.
• Using the SQL Server name (text) and also the IP Address of the server
• Including and Excluding port 1443 (the default tcp/ip port for the SQL server)
• Creating new rules in Windows Firewall to allow both inbound/outbound TCP at port 1443

• List item

  Traceback (most recent call last): File "pythonscript.py", line 75, in conn = pyodbc.connect(driver='{SQL Server}', server='ipaddress,1443', database='master', uid='XYZ\login', pwd='password') 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)'

here are some examples of what I've tried for the connection string:

conn = pyodbc.connect('Trusted_Connection=yes', driver = '{SQL Server}',server = '1.1.1.1,1443', database = 'master')

then

conn = pyodbc.connect(driver='{SQL Server}', server='1.1.1.1,1443', database='master', uid='xyz\login', pwd='pwd'

then I also tried both of the above with the name of the server (text) rather than the IP address. I have no idea how to get this to work at this point.

Answer 1

Have you confirmed you have connectivity between the servers? Try telnet -

telnet serverName 1433

If that connects then you can focus on issues with Python or the connection string.

In your connection string change it to use the PORT parameter instead of the ,1433. Something like -

SERVER=1.1.1.1;PORT=1433;

I would also say you might be better off passing the whole string. Here is what I do on Linux using FreeTDS typically -

self.db_connection = pyodbc.connect("DRIVER=FreeTDS;SERVER=1.1.1.1;PORT=1433;DATABASE=myDB;UID=myUser;PWD=myPass;TDS_Version=8.0;")

Answer 2

CONNECTION FROM WINDOWS TO MS SQL SERVER DATABASE:

Here you have an example I use myself to connect to MS SQL database table with a Python script:

import pyodbc
server = 'ip_database_server'
database = 'database_name'
username = 'user_name'
password = 'user_password'
driver = '{SQL Server}' # Driver you need to connect to the database
port = '1433'
cnn = pyodbc.connect('DRIVER='+driver+';PORT=port;SERVER='+server+';PORT=1443;DATABASE='+database+';UID='+username+
                 ';PWD='+password)
cursor = cnn.cursor()

If you are trying to connect from a Windows device to the DB, go to ODBC Data Source Administrator from Windows, and check if you have installed the driver:

Where is the ODBC data source administrator in a Windows machine.

The image is in spanish, but you only have to click on 'Drivers' tab, and check if the driver is there as in the image.

CONNECTION FROM LINUX/UNIX TO MS SQL SERVER DATABASE:

If you are working in Linux/Unix, then you shoud install a ODBC manager like 'FreeTDS' and 'unixODBC'. To configure them, you have some examples in the following links:

Example: Connecting to Microsoft SQL Server from Linux/Unix

Example: Installing and Configuring ODBC