13

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?

MrSmith42
  • 9,961
  • 6
  • 38
  • 49
Christopher
  • 5,806
  • 7
  • 31
  • 41
  • for low-level debugging use wireshark. – mo. Nov 11 '10 at 01:27
  • Thanks! I ran wireshark and it provided some data - a simple "ack" it appears. There doesn't seem to be much to go on however. Info says "ms-sql-s > 50051 [RST, ACK] Seq=1 Ack=1 Win=0 Len=0. – Christopher Nov 11 '10 at 02:44
  • I see further details. The "Expert Info" states "Connection reset (RST)" - not sure why. – Christopher Nov 11 '10 at 03:20
  • I found I cannot telnet 127.0.0.1 1433. I get "connection failed". I verified the server is running and the port #. I will keep trying. – Christopher Nov 11 '10 at 04:26
  • do u have the "sql server management studio" ? – mo. Nov 11 '10 at 14:57
  • please have a look here: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476 .this is what i get, when i leave out the port – mo. Nov 11 '10 at 14:59
  • I have SQL Server Management Studio. I can connect locally via SQL Authentication instead of Windows Authentication. However, I cannot telnet locally to port 1433. – Christopher Nov 11 '10 at 18:41

3 Answers3

14

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.

Christopher
  • 5,806
  • 7
  • 31
  • 41
  • I don't seem to have a freetds.conf on my Windows OS. I'm getting this same error with connection string: `host=10.43.32.211:1433, database=v000001, trusted=True` – IAbstract Mar 24 '15 at 19:42
  • oh, maybe `pymssql` does not use a connection string but named params (unlike some other libraries out there) ... a bit odd, but okay. – IAbstract Mar 24 '15 at 19:46
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.

daOnlyBG
  • 595
  • 4
  • 20
  • 49
notatoad
  • 21
  • 1
0

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.

mo.
  • 3,474
  • 1
  • 23
  • 20
  • Thanks! It appears I do need to specify port 1433 specifically. Wireshark shows some other response without the port. With the port it shows "ms-sql-s". – Christopher Nov 11 '10 at 02:45