pymssql.OperationalError: DB-Lib error message 200

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 had similar issue. pymssql.OperationalError: DB-Lib error message 20009, ....

Try the following steps.

1. Do not start MSSQLSERVER and SQLEXPRESS at the same time. Stop the one you do not needed.
2. Be sure the right SQL Server service is started
3. Be sure the ritht SQL Server Agent is started.
4. Check the user name and password is correct or not with Microsoft SQL Server Management Studio on the server.

Hope that helps

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

You should create a login in MS SQL, and associate that with a specific user in the relevant database.

See here for an introduction to users and logins : http://blogs.msdn.com/b/lcris/archive/2007/03/23/basic-sql-server-security-concepts-logins-users-and-principals.aspx

Answer 4

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 5

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.