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

Community · Accepted Answer · 2016-12-06 05:40:20Z

23

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:

Start -> All Programs -> Microsoft SQL Server 20XX -> Configuration Tools -> SQL Server Configuration Manager
SQL Server Network Configuration -> Protocols for MSSQLSERVER
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.

edited Dec 6, 2016 at 5:40

CommunityBot

11 silver badge

answered May 8, 2015 at 8:56

Hainan Zhao

2,11020 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

τεκ Over a year ago

Also check the port is 1433 as pymssql expects, the default seems to be randomized. Protocols for MSSQLSERVER -> TCP/IP -> Properties -> IP Addresses -> Default Port

AlphaCR Over a year ago

Hi, I'm having the same situation as Mona, but I encountered this problem in a Gitlab CI whenever I try to run a test on the CI. would you happen to know how I'll be able to open up a TCP/IP access for a gitlab runner?

Aminah Nuraini · Accepted Answer · 2016-08-13 17:48:39Z

5

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

answered Aug 13, 2016 at 17:48

Aminah Nuraini

19.4k9 gold badges98 silver badges113 bronze badges

1 Comment

tbone Over a year ago

Does this work for data frames though?

Paul Roub · Accepted Answer · 2017-11-27 18:41:48Z

4

check installed python version(32 bit or 64 bit).
Download freetds from below based on your OS architecture.
https://github.com/ramiro/freetds/releases
Unzip it to C:\ drive (like c:\freetds)
Add Bin (from c:\freetds\bin directory) to the environment variables(both).
Make sure port 1433 is open and TCP/IP enabled in SQL Configuration.
Restart your PC and everything should be fine.

edited Nov 27, 2017 at 18:41

Paul Roub

36.5k27 gold badges88 silver badges95 bronze badges

answered Nov 27, 2017 at 18:24

Talley Ouro

3293 silver badges5 bronze badges

Comments

Edward Renton · Accepted Answer · 2023-05-08 05:36:46Z

2

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

answered May 8, 2023 at 5:36

Edward Renton

816 bronze badges

Comments

Community · Accepted Answer · 2020-08-04 07:42:27Z

0

I had similar issue. pymssql.OperationalError: DB-Lib error message 20009, ....

Try the following steps:

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

Hope this helps.

edited Aug 4, 2020 at 7:42

CommunityBot

11 silver badge

answered Sep 11, 2014 at 11:21

forrestfeng

1

Comments

Reinaldo Aguiar · Accepted Answer · 2022-01-05 03:36:11Z

0

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

answered Jan 5, 2022 at 3:36

Reinaldo Aguiar

1

1 Comment

pringi Over a year ago

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review

Anupam Sharma · Accepted Answer · 2022-09-06 11:27:49Z

0

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.

answered Sep 6, 2022 at 11:27

Anupam Sharma

1

1 Comment

user11717481 Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

podiluska · Accepted Answer · 2013-10-13 18:05:46Z

-1

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

answered Oct 13, 2013 at 18:05

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

1 Comment

Mona Jalal Over a year ago

I added a new user in localhost with password but 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

Collectives™ on Stack Overflow

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

8 Answers 8

2 Comments

1 Comment

Comments

Comments

Comments

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

2 Comments

1 Comment

Comments

Comments

Comments

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related