如何修复Node/Express连接Azure SQL Server时出现的"ConnectionError: failed to connect to <server>:<port> in 15000ms"错误？

First off, let's get straight to your questions:

• Is this timeout normal when connecting to Azure SQL Server? No, it's definitely not. Azure SQL connections should typically establish in a few seconds at most. A 15-second timeout indicates there's a blocking issue preventing your app from reaching the server, not just a minor delay.
• Will extending the timeout fix it? Almost certainly not as a permanent solution. While a temporary timeout extension might work for rare network blips, your error points to underlying configuration or environment issues that need to be addressed first.

Let's break down the potential problems in your setup and how to fix them:

1. Critical Configuration Errors in dbconfig.js #

Looking at your config file, there are a few red flags that are likely causing the connection failure:

a. Conflicting Authentication Setting #

You're using a SQL Server username/password, but you have trustedConnection: true enabled. This setting is for Windows Integrated Authentication, which conflicts with SQL login credentials. Change it to:

trustedConnection: false

b. Typo in Configuration Key #

enableArithAort is a typo—it should be enableArithAbort. Incorrect config keys can throw off the connection process:

enableArithAbort: true

c. Unusual Port Number #

Azure SQL Server defaults to port 1433. Unless you specifically configured your SQL instance to use port 49678 (and updated firewall rules to match), this is likely wrong. Revert to the default port:

port: 1433

Here's the corrected dbconfig.js for reference:

const config = {
  user : "username",
  password : "password",
  server : "server-name.database.windows.net",
  database : "database-name",
  options: {
    trustedConnection: false,
    enableArithAbort: true,
    encrypt: true
  },
  port: 1433
}
module.exports = config;

2. Azure SQL Firewall Restrictions #

This is the most common cause of connection timeouts to Azure SQL:

• Log into the Azure Portal, navigate to your SQL Server instance, and go to Networking settings.
  • Ensure Allow Azure services and resources to access this server is enabled if your app is hosted on Azure.
  • Add your local public IP address to the Firewall rules section (you can find your public IP via a quick search, or Azure will suggest it directly in the portal).
  • Check if any Network Security Groups (NSGs) associated with your SQL Server are blocking inbound traffic on port 1433.

3. Local Network Blockages #

Even if Azure's firewall is set correctly, your local environment might be blocking the connection:

• Test connectivity using PowerShell:

  Test-NetConnection server-name.database.windows.net -Port 1433
  

  If this fails, check your local firewall, VPN, or proxy settings to ensure they aren't restricting outbound traffic to Azure on port 1433.

4. Dependency Version Compatibility #

Outdated versions of the mssql package might have compatibility issues with newer Node.js versions or Azure SQL features. Try updating the package:

npm update mssql

5. Minor Code Improvement (Unrelated to Timeout, But Good Practice) #

Your /testconnect route calls sql.getdata() without awaiting the async operation. While this isn't causing the timeout, it means you won't see the connection result in time for the response. Update it to:

router.get('/testconnect', async function(req, res, next) {
  await sql.getdata();
  res.render('index', { title: 'Express' });
});

Final Notes #

If after fixing all the above you still see occasional timeouts, you can add a longer connection timeout to your config as a last resort:

options: {
  // ... other options
  connectionTimeout: 30000 // 30 seconds
}

But remember, this is a band-aid—focus on resolving the root causes first.

内容的提问来源于stack exchange，提问作者Todd Brannon