Azure SQL server initial connectivity issue

Question

• Using MS Access 2021 F/E
• Linked to Azure SQL Server tables via ODBC (Driver 18)

The first attempt to open a linked table times out with "Server [server_name] failed."
After one or two more attempts, the link will then connect and the table opens.

If the app sits idle for some minutes (not sure how many - maybe 10?), the connection is lost.
I can repeat the attempts to connect and it will finally reconnect.

Any ideas?

Answer 1

Please try to increase connection timeout of at least 30 seconds. In addition, Azure SQL expects cloud-connected applications to use retry logic.

For the linked tables, I would use a DSN or connection string similar to this:

ODBC;
DRIVER=ODBC Driver 18 for SQL Server;
SERVER=tcp:<server_name>.database.windows.net,1433;
DATABASE=<database_name>;
UID=<user_name>;
PWD=<password>;
Encrypt=yes;
TrustServerCertificate=no;
Connection Timeout=30;
ConnectRetryCount=3;
ConnectRetryInterval=10;
APP=MS Access 2021;

The important additions are:

Connection Timeout=30;

ConnectRetryCount=3;

ConnectRetryInterval=10;

Another thing you may consider o try is considering to dd a lightweight “keep-alive” in Access. This is because Access linked tables are not a modern connection-pooled application with robust retry handling. SO consider to add a small hidden form that runs a timer every 3–5 minutes and executes a lightweight query against the Azure SQL database.

Take in consideration that Azure SQL Serverless option goes to sleep after an hour without activity and Serverless is not intended for Production. So if you are using Serverless try to move to vCore or DTU models that are always available, but you can scale them down during non-business hours or low usage periods.

Answer 2

What service tier do you have for the database? (You can check this on the overview page in the Portal). It sounds like you have a Serverless. A serverless database is rolled out after some time of inactivity. Rolled out means that the database is not mounted on any server, but only exists on disk. When you reconnect, the database is rolled in again, but this takes 30-60 seconds, which explains the connection errors you get.

Is Serverless bad or not? Serverless is great for databases which are only used occasionally, because when they are rolled out, you only pay for disk. But if they are used frequently, it is not such a good deal, because when they are active, they are more expensive than a provisioned database.

If you don't like the behaviour of Serverless, you can change to a provisioned service tier. But I recommend that you keep an eye on the costs, so that the bill does not come as a suprise.

Answer 3

The behavior matches transient connectivity and idle-connection drop characteristics between Access (via ODBC Driver 18) and Azure SQL Database. There are several mitigations that can be applied at the driver and application level.

1. Enable and tune ODBC connection resiliency
   • Use the connection resiliency options in the ODBC driver so that idle connections are automatically restored instead of failing on the first attempt:
     • ConnectRetryCount – number of reconnection attempts (1–255; default 1). For example, set to 3–5.
     • ConnectRetryInterval – seconds between retries (1–60; default 10). Ensure the total reconnect time stays within the connection timeout.
   • These can be set either:
     • In the DSN (ODBC Data Source Administrator → select the DSN → Configure → set Connection Retry Count and Connect Retry Interval), or
     • In the DSN-less connection string used by Access, for example:

              Driver={ODBC Driver 18 for SQL Server};Server=tcp:yourserver.database.windows.net,1433;Database=yourdb;Authentication=ActiveDirectoryPassword;ConnectRetryCount=5;ConnectRetryInterval=10;
       

   • This allows the driver to transparently re-establish idle connections instead of requiring multiple manual attempts.
2. Implement retry behavior in the application pattern
   • Azure SQL guidance recommends that client applications use retry logic for transient connectivity issues, with an initial delay of about 5 seconds and exponential backoff up to 60 seconds. While Access macros/VBA are limited compared to full application code, the pattern can still be approximated:
     • When a linked table open fails, catch the error in VBA and re-try the operation after a short delay.
     • Avoid immediate repeated opens without delay, which can exacerbate transient issues.
3. Verify basic connectivity configuration
   • Ensure the connection string is correct and uses the fully qualified server name and port 1433.
   • Increase the connection timeout to at least 30 seconds in the connection string so that initial handshakes have enough time, especially over slower or more latent networks.
   • Test connectivity from the same client using SSMS or a UDL file to confirm that the issue is specific to the Access/ODBC combination and not a general network problem.
4. Use the latest supported drivers
   • Confirm that the latest Microsoft ODBC Driver for SQL Server (Driver 18) is installed, and consider testing with the latest OLE DB driver as well. If another driver version behaves more reliably in the same environment, that can help isolate driver-specific issues.
5. Avoid loading very large result sets unnecessarily
   • When opening large linked tables, Access may attempt to load many rows, which can increase the chance of timeouts or connection drops. Where possible, open filtered queries or forms that limit the number of rows retrieved instead of opening entire tables directly.

These steps together reduce the likelihood of initial timeouts, allow the driver to automatically recover idle connections, and align the Access front end with Azure SQL’s recommended retry and connectivity patterns.

---

References:

• Connection resiliency in the ODBC driver
• Troubleshoot connectivity issues and other errors
• Connect Access to SQL Server
• Migrate an Access database to SQL Server
• Consistent SQL Server network connectivity issues