CODEWITHSUNDEEP
pythonmysqlsqlsql-serversql-server-2008
Mona Jalal
Mona Jalal

Reputation: 38135

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

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

Upvotes: 20

Views: 91497

Answers (8)

Edward Renton
Edward Renton

Reputation: 51

Specify the port you are using in your host name: host='localhost:11433'

Upvotes: 1

Anupam Sharma
Anupam Sharma

Reputation: 1

for Python + Unix, you can try adding one more export before trying to connect with the DB via pymssql -

export TDSVER=7.0

This worked for me in similar scenario where connection to DB through isql worked using kerberos, but pymssql threw db-lib 20017 error.

Upvotes: 0

Reinaldo Aguiar
Reinaldo Aguiar

Reputation: 1

For me when I got that error, it was missing just the port in the connection parameters.

Upvotes: 0

forrestfeng
forrestfeng

Reputation: 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 right 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 this helps.

Upvotes: 0

Talley Ouro
Talley Ouro

Reputation: 319

  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.

Upvotes: 4

Hainan Zhao
Hainan Zhao

Reputation: 2092

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.

Upvotes: 22

Aminah Nuraini
Aminah Nuraini

Reputation: 19146

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

Upvotes: 5

podiluska
podiluska

Reputation: 51494

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

Upvotes: -1

Related Questions