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

vfh0ocws 于 2023-05-16 发布在其他

关注(0)|答案(8)|浏览(310)

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

sql-server

来源：https://stackoverflow.com/questions/19348255/pymssql-operationalerror-db-lib-error-message-20009-severity-9

8条答案

按热度按时间

zbsbpyhn1#

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.

赞(0）回复(0）举报 2023-05-16

nfs0ujit2#

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

赞(0）回复(0）举报 2023-05-16

iszxjhcz3#

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.

赞(0）回复(0）举报 2023-05-16

amrnrhlw4#

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.

赞(0）回复(0）举报 2023-05-16

mccptt675#

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

赞(0）回复(0）举报 2023-05-16

k97glaaz6#

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.

赞(0）回复(0）举报 2023-05-16

bogh5gae7#

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