Connect to SQL Server with mssql for node.js

Question

I got a error message when I connect to SQL Server with mssql module for node.js.

[Error: connection to 192.168.1.101\sql:1433 - failed Error: getaddrinfo ENOENT]

var config = {
    //driver: 'msnodesql',
    user: '...',
    password: '...',
    server: '192.168.1.101\\sql',
    //TCP/IP 127.0.0.1
    database: 'ACCOUNTDB'
};

Answer 1

You should be able to connect to named instance when using tedious driver with this config:

var config = {
    user: '...',
    password: '...',
    server: '192.168.1.101',
    driver: 'tedious',
    database: 'ACCOUNTDB',
    options: {
        instanceName: 'sql'
    }
};

Documentation also says:

The SQL Server Browser service must be running on the database server, and UDP port 1444 on the database server must be reachable.

Config for msnodesql driver is little more complicated because it's connection string doesn't support named instances by default (should change in future):

var config = {
    user: '...',
    password: '...',
    server: '192.168.1.101',
    driver: 'msnodesql',
    database: 'ACCOUNTDB',
    connectionString: "Driver={SQL Server Native Client 11.0};Server=#{server}\\sql;Database=#{database};Uid=#{user};Pwd=#{password};"
};

Answer 2

Something that caught me is the fact that port and instanceName are mutually exclusive. Meaning that you only need one or the other.

Answer 3

I recently encountered this problem, I was trying to connect the MSSQL that is hosted on a remote server. The config that I had to use is-

let config = {
    user: 'user',
    password: 'password',
    server: 'server',
    database: 'database',
    "options":{
        instanceName: 'instanceName',
        "encrypt":true,
        "enableArithAbort":true,
        "trustServerCertificate": true,
       }
};
module.exports=config;

For getting the instance name use SELECT @@servicename in SSMS

Answer 4

Ok, I had the same issue, will try to help. this is my config exemple

const config = {
    user: 'sa',
    password: '****',
    server: 'DESKTOP-Q5TO47P',
    database: 'dbname',
    options: {           
        encrypt: false
    }
};

You need to turn on the SQL Server Browser. Go to start up menu or the search and look for SQL Server Configuration Manager. Run it! (Im using 2018 version)

• In the left Tab click on SQL Server Services
• now in the right tab double click on SQL Server Browser
• will open a window, you will see 3 tabs, go for the Service tab
• change start mode to Automatic and apply
• left click on SQL Server Browser and click restart
• Back to the right tab click on SQL Server Network Configuration
• then Client Protocols
• change TCP/IP to enable