4

I'm unable to connect to SQL Server from Python (3.4.4 64 bit) on Windows 10. This is what I did:

  1. I found this nice library.
  2. Then, I followed this page and installed FreeTDS
  3. After that I installed pymssql with this command: easy_install pymssql
  4. In SQL Server Network Configuration I enabled Named Pipes and TCP/IP for my SQLEXPRESS instance

So, at this moment I can run SQL Server and connect to my database, using SQL Server Management Studio. When I log in, I use DESKTOP-1JA5E9F\SQLEXPRESS as Server Name, sa as Login and 123 as Password. Besides, in Python shell I can import pymssql like:

>>> import pymssql

It does not raise any error. However, I can not connect to my database instance. I tried dozens attempts like:

 conn = pymssql.connect(host=r'DESKTOP-1JA5E9F\SQLEXPRESS', 
                        user=r'sa', password=r'123', database=r'reestr')

The code above ^^^ never completes (I see just blinking _ in the shell, that is blinking for ever). I also tried this:

conn = pymssql.connect(host=r'SQLEXPRESS', user=r'sa', password=r'123', database=r'reestr')

This results in pymssql.InterfaceError: Connection to the database failed for an unknown reason.. I tried this:

conn=pymssql.connect(host=r'SQLEXPRESS:1433',user=r'sa',password=r'123', database=r'reestr')

It also results in the same error message. So, I if anybody knows those magic voodoo spells that can do the trick, you are welcome.

Improve this question
7
  • Do you have a freetds.conf file? pymssql.org/en/v2.1.2/freetds.html#configuration Commented May 2, 2016 at 11:47
  • Yes, I have it in C:\ folder with exactly the same contents. Commented May 2, 2016 at 11:50
  • 2
    stackoverflow.com/questions/9165031/… According to this user the 'SQL Server Browser' service must be running on the SQL server's host. Commented May 2, 2016 at 11:52
  • For some reason, it is impossible to enable it. I can only enable SQL Server and SQL Server VSS Writer Commented May 2, 2016 at 11:55
  • In configuration manager Start, Pause and other command are disabled for some reason for SQL Server Browser. Commented May 2, 2016 at 11:57

1 Answer 1

Reset to default
5
+250

Acording to the docs, there's no host keyword arg anymore, but server. Also it should be server name, not instance name, or full instance name (with server name). See connect() description , and examples of name construction for Connection class.

In your case server name is DESKTOP-1JA5E9F, also . and (local) should work since you do it all on local machine; your named instance name is SQLEXPRESS.

Try like these:

import pymssql

#for instance with known name 'SQLEXPRESS'
conn = pymssql.connect(server=r'DESKTOP-1JA5E9F\SQLEXPRESS', 
                       user=r'sa', password=r'123', database=r'reestr')
#on localhost this should work too
conn = pymssql.connect(server=r'.\SQLEXPRESS', 
                       user=r'sa', password=r'123', database=r'reestr')

#for default instance with port taken from freetds.conf
#(this probably won't work for your case, because you use named instance
#instead of default instance, which is named MSSQLSERVER)
conn = pymssql.connect(server=r'DESKTOP-1JA5E9F', user=r'sa', password=r'123',    
                       database=r'reestr')

#for instance on known port '1433'
conn = pymssql.connect(server=r'DESKTOP-1JA5E9F:1433', user=r'sa', password=r'123',
                       database=r'reestr')

If this won't help, test the connection with tsql as described in the docs, e.g.:

tsql -H DESKTOP-1JA5E9F -p 1433 -U sa -P 123 -D reestr

or if you have freetds.conf:

tsql -S 'DESKTOP-1JA5E9F\SQLEXPRESS' -U sa -P 123 -D reestr
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thank you, Nikita! I will check it today. As for tsql, on my Windows machine this command does not work, even though I installed freetds following the instruction. If you can provide a little more information about tsql and the way to use it on Windows, it would be great.
@Jacobian, looks like tslq is not on system PATH, as there's a mention in the docs, that you might want to add some folders from your installation to the PATH manually. But you can run it directly from the command line without modifying the PATH variable, just use full path scpecification. tsql should be somewhere in the FreeTDS installation directory, probably in src/apps. Just use Windows search, to find tsql.
Well, I tried first two and the last one example from your answer, but what I get is just infinite blinking of _ in the console. I do not get any error message, but I do not get any result either. The very same happends when I try to connect to the server with tsql - the last line in the console which never changes is Setting reestr as default database in login packet ....
Probably, I need some extra manipulation in SQL Server Configuration Manager. What I have know is 1) Named Pipes and TCP/IP enabled 2) SQL Server, SQL Server Agent and SQL Server browser running. Probably there should be some additional IP and ports tuning. I do not know.
Well, I did some manipulations on IP addresses tab in SQL Server Configuration Manager and now it works. Thank you for help!
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.