unable to connect ms sql server from python

Question

Hey, while trying to connect the SQL server from python I'm getting an operational error.

import pyodbc

conn = pyodbc.connect("DRIVER={SQL Server Native Client 11.0};"
"SERVER=g1_mssql0;PORT=23001;"
"DATABASE=***;"
"UID=sa;""PWD=********")

OperationalError: ('08001', '[08001] [Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) (SQLDriverConnect); [08001] [Microsoft][SQL Server Native Client 11.0]Login timeout expired (0); [08001] [Microsoft][SQL Server Native Client 11.0]Invalid connection string attribute (0); [08001] [Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (53)')

can you please help me out how to fix this

Answer 1

Hi @Pentyala, Avinash C ,

Welcome to Microsoft Q&A!
From your error message, I've put together a few areas to help you do troubleshooting.

1. Enter wrong server details by mistake. Make sure to enter correct SQL Server instance name while making database connection.
2. Try to connect using ip address and port number instead of putting server name in connection string.
3. SQL Server Instance is not accessible due to firewall or any reason. Check firewall details. Your port 23001 and UDP port (1434) of SQL Server browser should be enabled. These ports might be blocked.
4. SQL Server service is not running on SQL Server. You can try to start or restart SQL Server services
5. Make sure to enable TCP/IP and Named Pipes, which you can check from SQL Server Configuration Manager.
6. Remote connection must be enabled to connect to databases remotely. Right click on SQL Server instance name in SSMS and choose Properties. You will get Server properties window. Click on Connections from left side pane and tick the check box on Allow remote connections to this server option from right side pane.
7. SQL browser service should not be stopped if your security policy allows it.
   Hope this could give you some help.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

"SERVER=g1_mssql0;PORT=23001;"

Is your SQL Server really setup with IP port 23001? That's unusally, default is 1433.

"DATABASE=;"

An empty database name? Huh? Where should the connect go thru?

Answer 3

You can try the below script

#connect the db server
conn = pyodbc.connect(
    "Driver={SQL Server Native Client 11.0};"
    "Server=yourserver;"
    "Database=yourdb;"
    "UID=username;"
    "PWD=password;"
    "Trusted_Connection=no"
)

#To execute queries, the “cursor()” object of the connection object is used. The following script stores the cursor object in the “cursor” variable:
cursor = conn.cursor()
select_record = '''select 
convert(date,create_t) as create_t,count(ordernumber) as ordercount
from yourtable
where create_t>='2023-03-01'
group by convert(date,create_t) '''
sql_query =pd.read_sql_query(select_record,conn)
cursor.execute(select_record)
df = pd.DataFrame(sql_query, columns = ['create_t', 'ordercount'])
print (df)