Unable to connect to SQL Server via pymssql

问题:

I am attempting to connect to SQL Server running on Windows XP system from a *nix system on a local server via pymssql. However, the connection fails as shown below

db = pymssql.connect(host='192.168.1.102',user='www',password='test',database='TestDB')
Traceback (most recent call last):

File "<stdin>", line 1, in <module>
File "pymssql.pyx", line 457, in pymssql.connect (pymssql.c:6041)
raise InterfaceError(e[0])
pymssql.InterfaceError: Connection to the database failed for an unknown reason.

Things I've tried:

1. Set SQL Server and browser to run as a network server.
2. Setup a user 'www'. I also tested this user locally in SQL Studio.
3. Turned off Windows firewall (temporarily of course).

I am missing SOMETHING - I just don't know what it is. I tried all of the infinite menu options on Windows to no avail. One thing I noticed is that if the Windows Firewall is on (I setup an exception for SQL Server) python pauses a long time and then gives the error. If the firewall is off the error is instant.

Are there any logs I can look at in SQL Server?

回答1:

Got it! I think the source of the problem was not giving Free TDS the attention it needs. Free TDS is apparently the driver behind pymssql and provides for connectivity to other databases - SQL Server being one of them.

The freetds.conf file is located in /usr/local/etc on my system (Mac Book Pro).

This file contains the defaults from the install. However, I had previously added a definition so that I could connect but forgot about it and unfortunately did not take notes on it.

Anyway, here is an example of what I appended to freetds.conf:

[SomeDB]
    host = 192.168.1.102
    port = 1219
    tds version = 7.0

However, what is puzzling is that I set the port to 1219. I had it set manually to 1433 in SQL Studio. Also, I am using TDS version 0.82 so I don't know how 7.0 fits in.

Next, I tested connectivity using 'tsql' as follows:

tsql -S SomeDB -U www

I enter the password and get a command-line which allows for SQL queries.

Next, I tested connecting using pymssql as follows:

db = pymssql.connect(host='SomeDB',user='www',password='cylon',database='TestDB')

As you can see, I needed to use the host name from the freetds.conf file and NOT the IP directly. I then tested a simple query with additional python code to insure I could read from the database.

I hope this helps someone else in the future.

回答2:

It looks like you've got this solved, but for anybody else from google that lands here: check to make sure mixed-mode authorization is turned on on your MS SQL Server. It defaults to only allowing Windows authorization, and that will cause this error in pymssql.

回答3:

is it a windows machine u working on? specify the port 1433. it seems to be a bug in the mssql client api, which tries to use Namedpipes instead of TCP/IP.