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-19T08:47:08.143

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 '18 at 08:47

answered Sep 19 '18 at 08:33

Skaranjit

764
9
26

1

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**](https://learn.microsoft.com/en-us/sql/tools/configuration-manager/tcp-ip-properties-ip-addresses-tab?view=sql-server-ver16#static-vs-dynamic-ports) in `Dynamic Ports` and `1433` in `Port`. – davetapley Jun 13 '23 at 18:25

score 1 · Answer 2 · answered Feb 19 '20 at 08:20

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

Database connection failed for local MSSQL server with pymssql

2 Answers2

Linked