Node.js+Docker环境下MSSQL数据库登录失败求助（密码正确但无法登录）

Let's walk through the possible issues and fixes for your login problem, starting with the most likely culprits:

1. First, Fix the Obvious Typo (Not Login-Related But Critical) #

Your SQL query has a typo: selct should be select. This won't fix your login issue, but it will prevent a query failure once you get connected.

2. Verify SQL Server Authentication Mode #

The most common reason for sa login failures is that SQL Server is set to Windows Authentication Only mode. Here's how to check and fix it:

• Open SQL Server Management Studio (SSMS) and connect using Windows Authentication.
• Right-click your server → Properties → Security.
• Select SQL Server and Windows Authentication Mode.
• Restart the SQL Server service (you can do this in SSMS under Services or via Windows Services).

3. Ensure the sa Account is Enabled and Password is Correct #

Even if you're sure the password is right, double-check these:

• In SSMS, expand Security → Logins → Right-click sa → Properties.
• Under the General tab, confirm the password matches what's in your config (note that passwords are case-sensitive if your SQL Server is configured that way).
• Under the Status tab, make sure Login is set to Enabled. If not, toggle it and save.

4. Check Connection Configuration Details #

• Port Number: If your SQL Server isn't using the default port (1433), add it to your config:

  const config = { 
    user: 'sa', 
    password: 'Super_Duper_Password', 
    server: 'localhost', 
    database: 'testdb', 
    port: 1433, // Add your actual port here
    debug: true, 
    options: { enableArithAbort: true } 
  }
  

• Remote Connections: Even for localhost, ensure your server allows remote connections:
  Right-click server → Properties → Connections → Check Allow remote connections to this server.
• SQL Server Browser Service: If you're using a named instance (e.g., localhost\SQLEXPRESS), make sure the SQL Server Browser service is running in Windows Services.

5. Fix Potential Issues in Your Connection Code #

Your current code has a risk: if the connection fails, pool will be undefined, and calling pool.close() in the finally block will throw another error. Here's the corrected version:

let pool 
const run = async () => { 
  try { 
    console.log('Connection opening...') 
    pool = await sql.connect(config) 
    const { recordset } = await sql.query`select * from users;`; // Fixed typo
    console.log(recordset) 
  } catch (err) { 
    console.error('Error during connection/query:', err) // Use console.error for clearer error logging
  } finally { 
    if (pool) { // Only close the pool if it was successfully created
      await pool.close() 
      console.log('Connection closed') 
    }
  } 
} 
run()

Also, make sure you're using a stable version of the mssql npm package. If you're on an old version, update it with npm install mssql@latest.

6. Check Firewall Settings #

Even for localhost, Windows Firewall might block SQL Server traffic. To allow it:

• Open Windows Defender Firewall → Advanced Settings → Inbound Rules.
• Add a new rule for port 1433 (or your custom port) allowing TCP traffic.

Start with the authentication mode and sa account checks first—those are the most frequent causes of this exact issue. Once you get past the login error, the typo fix will ensure your query runs correctly.

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