Connection timeout for Azure SQL when waiting for Azure Authenticate to Database Window (2 Viewers)

[paulgrossman]

Hi All,

(see screen shots below)

I am migrating an existing Access app to Azure SQL. While it works very well, I get unhandled connection timeouts (when waiting for the Azure Authenticate to Database Window) which are not influenced by any ODBC timeout settings in the connection string. The following environment applies

• Access 2021
• ODBC 17
• Applies to Linked tables and passthrough queries
• Machines are user owned "work from home" machines which are not connected to a domain and running fully patched Windows 11
• Connection string ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=DBServerName.database.windows.net;UID=username@domainname.onmicrosoft.com;APP=Microsoft Office;DATABASE=LLProperty;ConnectRetryCount=10;Authentication=ActiveDirectoryInteractive;;TABLE=dbo.PropertyD
• ODBC 64 bit driver
• Authenticator app validation using a two digit number works as expected

Sometimes I get the following error when I establish a connection to the database, usually for the first connection of the day. It looks like Azure SQL has spun down and there is a pause while it spins up. Once connected, re-connecting works flawlessly, but symptoms occur again if there is no activity against the database and the user tries to log in again after closing down the app.

As far as I can tell just using the ODBC app to connect to the database does not reproduce this result and there is no timeout.

Any assistance in handling this error would be greatly appreciated.

Hitting OK gives

Hitting OK again after waiting a few seconds gives the Azure authenticate to database window:-

 

[GPGeorge]

I'm pretty sure you have the diagnosis right here:

It looks like Azure SQL has spun down and there is a pause while it spins up. Once connected, re-connecting works flawlessly, but symptoms occur again if there is no activity against the database and the user tries to log in again after closing down the app.

Azure SQL can be configured to pause during idle periods to save you costs associated with running the database. I'm not sure whether you can configure it otherwise, but you probably want to investigate thoroughly before doing so. There is likely to be a cost/benefit tradeoff.

Investigate the configuration set up for your Azure SQL database and the service tier you are on. The Azure portal provides a lot of tools for optimizing and balancing costs.