pymssql.OperationalError: DB-Lib error message 20009, severity 9

Question

I am trying to run this program

conn = pymssql.connect(host='localhost',  user='notsa', password='notsayly', database='TallicaTweets')

but I am receiving the following errors:

Traceback (most recent call last):
  File "harvester_of_tweets.py", line 11, in <module>
    conn = pymssql.connect(host='localhost',  user='username', password='password!', database='Database')
  File "/usr/local/lib/python2.7/dist-packages/pymssql.py", line 607, in connect
    raise OperationalError, e[0]
pymssql.OperationalError: DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist
Net-Lib error during Connection refused Error 111 - Connection refused

Searching Google wasn't really helpful and the few tuts out there weren't really thorough. Should I add my credential into a specific configuration file? And how to create credentials in mssql? (I have done these things in psql but not familiar with mssql.

I am using Ubuntu 13.04 ad my python version is 2.7.

After I created a user login I am receiving this error:

Traceback (most recent call last):
  File "harvester_of_tweets.py", line 11, in <module>
    conn = pymssql.connect(host='localhost',  user='tweet', password='1234', database='tweet_db')
  File "/usr/local/lib/python2.7/dist-packages/pymssql.py", line 607, in connect
    raise OperationalError, e[0]
pymssql.OperationalError: DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist
Net-Lib error during Connection refused Error 111 - Connection refused

Answer 1

I encountered the same issue. It turns out that I didn't open up TCP/IP access for my local SQL server.

Do a quick test to check whether it's caused by this, you can try to connect to a remote server with the same code. If it works, it means something is wrong with your local server.

So just open the access to your 127.0.0.1:1433 in the SQL server Configuration Manager.

Steps:

1. Start -> All Programs -> Microsoft SQL Server 20XX -> Configuration Tools -> SQL Server Configuration Manager
2. SQL Server Network Configuration -> Protocols for MSSQLSERVER
3. TCP/IP -> Properties -> IP Addresses. Find 127.0.0.1 and change the "Enabled" to "Yes". You can do it for all the IPs if you want.

Answer 2

I solved my problem by using a different library instead. I use pyodbc

import pyodbc
cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=SERVERNAME;DATABASE=testdb;UID=me;PWD=pass')
cursor = cnxn.cursor()
cursor.execute("select user_id, user_name from users")
rows = cursor.fetchall()
for row in rows:
    print row.user_id, row.user_name

Answer 3

1. check installed python version(32 bit or 64 bit).

2. Download freetds from below based on your OS architecture.
   https://github.com/ramiro/freetds/releases

3. Unzip it to C:\ drive (like c:\freetds)

4. Add Bin (from c:\freetds\bin directory) to the environment variables(both).

5. Make sure port 1433 is open and TCP/IP enabled in SQL Configuration.

6. Restart your PC and everything should be fine.