Connection to Azure database fails only at the first attempt in a series

Question

Connection to Azure database fails only at the first attempt in a series

Smail Kozarcanin 1

I am running a very simple SQL-call through python on windows 10. This SQL-call is a part of a larger tool. When this tool is ran for the first time after, say 1 day, then the very first SQL-call that is connecting to an Azure database is failing with the error as shown in the attachment. If then the tool is ran immediately afterwards there are no issues and the connection and data extraction is successful. It is also very hard to provoke this error and therefore difficult to assess. I am not sure if it makes sense to attach the SQL connection, as it functions well. But, for easier reference it is shown below. "?" indicates hidden info.

from sqlalchemy import create_engine  
import urllib  
import pandas as pd  
  
database_dict = {'?': '?'}  
  
uids = ['?']  
pwds = ['?']  
  
connections = database_dict  
  
for i in range(len(database_dict)):  
  
    server = list(database_dict.values())[i]  
    database = list(database_dict.keys())[i]  
    uid = uids[i]  
    pwd = pwds[i]  
      
    params = urllib.parse.quote_plus("Driver={ODBC Driver 17 for SQL Server};"  
                                          f"Server={server};"  
                                          f"Database={database};"  
                                          f"uid={uid};"  
                                          f"pwd={pwd}")  
      
    con = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)  
      
    connections[database] = con

4 answers

Your answer

Answer 1

Lorran Pedrosa Leite 5

Good morning, I came across the same problem. I did the following, I created a loop of 3 connection attempts, with a brief sleep. If the 3 attempts fail, the process is not executed and an email is sent.

I hope this tip helps anyone who needs it!

Scott H 0 Reputation points

2024-05-03T15:21:50.4533333+00:00

I have same issue with VBA connection from Excel. Will try the delayed loops. Sounds promising

Answer 2

Pablo Gabriel Mederos Caballero 1

This usually happens when the connection was too long without being used, so it exceeds the time defined by the server to wait for connections, and it is closed.
Ideally, before using the connection, you should test it and if it's not active, create another one.

Answer 3

Hi @Smail Kozarcanin Thank you for posting your query on Microsoft Q&A and for using Azure services.
My understanding is that you are not able to connect to Azure SQL Database using Python. Please let me know if my understanding is not correct.
Please try to use pyodbc

import pyodbc
server = 'your_server.database.windows.net'
database = 'your_database'
username = 'your_user'
password = 'your_password'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

Here is another example https://github.com/azure-samples/mssql-django-samples/tree/main/

Please let me know if that was helpful.

Do not forget to mark as accept answer if the reply was helpful as this will be Beneficial for other community members with the same question.

Regards,
Oury

Oury Ba-MSFT 20,931 Reputation points Microsoft Employee Moderator

2022-06-03T16:30:52.823+00:00

Hi @Smail Kozarcanin Checking if the answer I provided helped. Please let me know about the progress.

Regards,
Oury

Answer 4

Aditi Jain 0

Hi, you can try the following steps -

Go to your database service in azure portal
Go to Settings->Compute + Storage
Scroll down and find Auto-pause delay
Disable it

Share via

Connection to Azure database fails only at the first attempt in a series

4 answers

Your answer