2

I'm trying to connect to a remote SQL Server from my machine, I'm working with node. I can connect to it through SQL Management Studio, and I could connect with a C# application as well (both using Windows Authentication). I've already searched in several forums (in some questions here as well) and couldn't find a solution.

Below you can find the error I got:

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

This is the code I'm using:

'use strict';
var http = require('http');
var express = require('express');
var port = process.env.PORT || 1337;

var app = express();

app.get('/', (req, resp) => {
    var sql = require("mssql");

    var config = {
        driver: 'msnodesqlv8',
        server: 'MyServer/Instance',
        database: 'MyDB',
        options: {
            trustedConnection: true
        }
    };

    sql.connect(config, (error) => {
        if (error)
            console.log(error);

        var request = new sql.Request();
        request.query('select * from MyTable', (err, recordset) => {
            if (err)
                console.log(err);

            resp.send(recordset);
        });

    });

});

var servidor = app.listen(port, function () {
    console.log("Server running");
});

I looked other questions (here and here for example), but no luck.

Fernando Paz
  • 532
  • 6
  • 19
  • Is server running on default port 1433 or on 1337. If it is on 1337, put port in config (port : 1337) – MRsa Nov 17 '17 at 12:02
  • Yes, I put port on config but the error still here. – Fernando Paz Nov 17 '17 at 12:09
  • Could you try with SQL account credentials if mixed mode is enabled. I would also try to see if there is connection request with SQL profiler, ... – MRsa Nov 17 '17 at 12:15
  • Con profiler you could monitor existing connections, not FAILED – sepupic Nov 17 '17 at 12:27
  • 1
    To find out if it's "server not found" or "login failed" just check your SQL Server error log. Any "login failed" is logged there. If there is no error, your server cannot be reached (connection string is wrong) – sepupic Nov 17 '17 at 12:28
  • If he can connect with Management Studio, it looks he can reach the server. It also means his credentials are fine, I believe it's something else. – Alisson Reinaldo Silva Nov 17 '17 at 13:19
  • Some hours ago I answered one question where someone was able to connect using SSMS but got the error from its app. And the error was "login failed".Since he was not able to find the error in error log, I suspected **another** server and I was right. He connected to named instance from SSMS but to default using his app. https://stackoverflow.com/questions/47345244/cannot-open-a-sql-server-database/47346775?noredirect=1#comment81647155_47346775 – sepupic Nov 17 '17 at 13:23
  • It makes sense, though OP could connect with a C# app. If the connection string for the app isn't specifying a port nor a specific instance, then it should work with node. But we should not discard this possibility. – Alisson Reinaldo Silva Nov 17 '17 at 13:28
  • I tried using the instance name in the config, but no success yet. I edited the question and added the instance name as a sample. – Fernando Paz Nov 17 '17 at 13:31
  • So did you checked SQL Server error log if there is any login error? – sepupic Nov 17 '17 at 14:17
  • There was no error log. – Fernando Paz Nov 17 '17 at 15:26

1 Answers1

0

I got resolve this problem. First of all I created an user on my db and after include in my config the user and password. I needed to specify the instance and db in options, cause node was not recognizing when put all in the same string.

var config = {
    user: 'user_name',
    password: '****',
    server: 'MyServer',
    options: {
        instance: 'MyInstance',
        database: 'MyDB'
    }
};
Fernando Paz
  • 532
  • 6
  • 19