Database connection failed for local MSSQL server with pymssql

Question

I had been working with pyodbcfor database connection in windows envirnment and it is working fine but now I want to switch to pymssql so that it is easier to be deployed to Linux machine as well. But I am getting this error:

(20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (localhost:1433)\nNet-Lib error during Unknown error (10060)\n')

My connection code for using both pyodbc and pymssql is:

    import pyodbc
    import pymssql

    def connectODSDB_1():
        conn_str = (
            r"Driver={SQL Server};"
            r"Server=(local);"
            r"Database=populatedSandbox;"
            r"Trusted_Connection=yes;"
        )
        return pyodbc.connect(conn_str)

    def connectODSDB_2():
        server = '(local)'
        database = 'populatedSandbox'
        conn = pymssql.connect(server=server, database=database)
        return conn

What could be the problem? And solution?

Skaranjit · Accepted Answer · 2018-09-19 08:47:08Z

6

Well after browsing internet for a while, it seems pymssql needs TCP/IP be enabled for communication.

Open Sql Server Configuration Manager
Expand SQL Server Network Configuration
Click on Protocols for instance_name
Enable TCP/IP

edited Sep 19, 2018 at 8:47

answered Sep 19, 2018 at 8:33

Skaranjit

81410 silver badges29 bronze badges

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

1 Comment

davetapley Over a year ago

If you want a static port e.g. 1433 then also verify: Right click TCP/IP, Properties, check Listen All is Yes and IP Addresses tab scroll to bottom and verify IPAll is blank in Dynamic Ports and 1433 in Port.

Ramesh Ponnusamy · Accepted Answer · 2020-02-19 08:20:14Z

1

I have faced the same issue while using RDS(AWS database instance). We should configured the inbound outbound rules. Do following steps to configure.

Services->RDS->DB Instances -> Select DB-> Connectivity&Security

Under Security Section

VPC security groups -> click on security group

Change the inbound rules.

Check the source IP and change into anywhere or specific IP

answered Feb 19, 2020 at 8:20

Ramesh Ponnusamy

1,81716 silver badges24 bronze badges

Collectives™ on Stack Overflow

Database connection failed for local MSSQL server with pymssql

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related