Working with Nodejs + MSSQL at Linux / Ubuntu

Question

I've my nodejs app connected to MSSQL server, worked perfectly in my Windows laptop, sing mssql@npm

The same app is not able to see the database in my Ubuntu laptop.

I defined the mssql connectivity in Ubuntu as below, am I missing any thing?

1. Updated my ~/.profile, as:

  ~$ export ODBCINI=/etc/odbc.ini
  ~$ export ODBCSYSINI=/etc
  ~$ export FREETDSCONF=/etc/freetds/freetds.conf

2. Logged-out the laptop, to get the above active, and the profile refreshed.

3. Installed the required connectivity packages.

  ~$ sudo apt-get install unixodbc unixodbc-dev freetds-dev sqsh tdsodbc -y

4. Configured FreeTDS

  ~$ sudo gedit /etc/freetds/freetds.conf

   [ACUMENSERVER]
   host = 192.168.0.10
   port = 1433
   tds version = 7.0

5. Tested the FreeTDS connection with sqsh, and it works fine:

  ~$ sqsh -S ACUMENSERVER -U mssql-username -P mssql-password

6. Configured ODBC - odbcinst.ini:

  ~$ sudo gedit /etc/odbcinst.ini

     [FreeTDS]
     Description     = TDS driver (Sybase/MS SQL)
     Driver          = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
     Setup           = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
     CPTimeout       =
     CPReuse         =
     FileUsage       = 1

7. Configured ODBC - odbc.ini:

~$ sudo gedit /etc/odbc.ini

   [ACUMENSERVER]
      Driver          = FreeTDS
      Description     = ODBC connection via FreeTDS
      Trace           = No
      Servername      = ACUMENSERVER
      Database        = myDataBase

8. Tested the ODBC connection with isql, and it works fine:

isql -v ACUMENSERVER mssql-username mssql-passward

When I run my nodejs app (that is working fine in Windows), I got th below error in Ubuntu, considering all the above got done, and checked:

{ name: 'ConnectionError',
  message: 'Failed to connect to ACUMENSERVER:1433 - getaddrinfo ENOTFOUND',
  code: 'ESOCKET' }

What could be the wrong/missing thing here, I'm opened to use another npm package for mssql connectivity.

Behr

Answer 1

I found this can work if the IP address of the server is used, server name is not working!

below what worked with me:

1 Instal mssql:

npm install mssql

2 The index.js file:

var sql = require('mssql');

var config = {
    user: 'sa',
    password: 'sql@123',
//  server: 'myServername',  --> Not Working
//  server: 'ACUMENSERVER',  --> Not Working
    server: '6192.168.0.10', // WORKED
    database: 'myDB'
}

sql.connect(config).then(function() { 
// Query 

new sql.Request().query('select top 1 itemcode from OITM').then(function(recordset) {
    console.dir(recordset);
    }).catch(function(err) {console.log(err); /* ... query error checks ... */ });


// Stored Procedure 

new sql.Request()
    .input('input_parameter', sql.Int, value)
    .output('output_parameter', sql.VarChar(50))
    .execute('procedure_name').then(function(recordset) {
         console.dir(recordset);
     }).catch(function(err) {console.log(err); /* ... execute error checks ... */ });



}).catch(function(err) {console.log(err); /* ... connect error checks ... */ });

UPDATE by Michael J. Ryan

You probably need a DNS resolver for myServer .. Outside of windows, you won't get netbios name resolution... whatever you put as the servername, if you can't ping myserver from the command prompt, you won't be able to connect via node

Then:

Either use the IP as noted above (which means no need at all to install neither ODBCunix nor FreeTDS), or have an internal DNS for the local network and use the full ADS name... myserver.domain.name which should resolve (assuming ADS is used as dns host for local resolution).

Behr