使用Express+npm连接本地MSSQL数据库报错排查求助

Hey there, let's work through your connection issue and answer your username/password question clearly.

First: Fix the Server Name Parsing Issue #

Looking at your error message:

ConnectionError: Failed to connect to WIN10-LAP-HJPMSSQLSERVER1:1433 - getaddrinfo ENOTFOUND WIN10-LAP-HJPMSSQLSERVER1

Notice how your server name WIN10-LAP-HJP\MSSQLSERVER1 got turned into WIN10-LAP-HJPMSSQLSERVER1? That's because JavaScript treats backslashes as escape characters in regular strings. So the \M was being interpreted as a single escaped character, not a literal backslash plus "M"—this is the root cause of your "could not find server" error.

Here are two easy fixes:

• Use double backslashes to escape the backslash:

  server: 'WIN10-LAP-HJP\\MSSQLSERVER1'
  

• Or use an ES6 template literal (backticks) which doesn't require escaping backslashes:

  server: `WIN10-LAP-HJP\MSSQLSERVER1`
  

You can also try localhost\MSSQLSERVER1 for local connections, which often avoids name resolution quirks.

Your Question: Do I Still Need a Username/Password if the Database is "Connected"? #

Great question! It all depends on your SQL Server's authentication mode:

• SQL Server Authentication: If your server is set to allow SQL logins (like the sa account you're using), then yes, you need to provide user and password in your config. This is standard for mixed-mode authentication (allowing both SQL and Windows logins).
• Windows Authentication: If you want to use your current Windows user account to connect (instead of a dedicated SQL login), remove the user and password fields and add this to your config:

  integratedSecurity: true
  

  Note: You’ll need to install the msnodesqlv8 package for Windows auth to work properly (npm install msnodesqlv8).

To check your server's authentication mode:

• Open SQL Server Management Studio (SSMS)
• Right-click your server → Properties → Security
• Look for "Server authentication" to see if it's set to "Windows Authentication mode" or "SQL Server and Windows Authentication mode"

Additional Fixes & Best Practices #

1. Move the sql require to the top: Don’t load the mssql module inside your route handler—load it once at the start of your file to avoid redundant overhead:

   const express = require('express');
   const sql = require("mssql"); // Move this here
   const app = express();
   

2. Handle errors properly: Right now, if the connection fails, you log the error but still try to run the query (causing the "Connection is closed" error). Add a check to exit early:

   sql.connect(config, function (err) {
     if (err) {
       console.log(err);
       return res.status(500).send('Failed to connect to database');
     }
     // Rest of your code...
   });
   

3. Verify SQL Server is listening on port 1433: Open SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for MSSQLSERVER1 → TCP/IP → Properties → Confirm TCP Port is set to 1433 and TCP/IP is enabled.

Modified Server.js Example #

Here's your code with the server name fix and basic error handling:

const express = require('express');
const sql = require("mssql");
const app = express();

// Config for your database
const config = {
  user: 'sa',
  password: 'mypassword',
  server: 'WIN10-LAP-HJP\\MSSQLSERVER1', // Fixed backslash
  database: 'test',
  port: 1433 // Explicitly set port to avoid issues
};

app.get('/', function (req, res) {
  // Connect to your database
  sql.connect(config, function (err) {
    if (err) {
      console.log(err);
      return res.status(500).send('Database connection failed');
    }
    // Create Request object
    const request = new sql.Request();
    // Query to the database and get the records
    request.query('select * from users', function (err, recordset) {
      if (err) {
        console.log(err);
        return res.status(500).send('Query failed');
      }
      // Send records as a response
      res.send(recordset);
      // Close the connection after query
      sql.close();
    });
  });
});

const server = app.listen(5000, function () {
  console.log('Server is running..');
});

Start with fixing the server name issue first—that's the immediate cause of your "getaddrinfo ENOTFOUND" error. Once that's resolved, adjust the authentication settings if needed based on your SQL Server configuration.

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