Tedious: HELP: Unable to connect to SQL Server running locally

[sdtacoma]

I am at a loss trying to get connected to a local SQL 2019 Server. Below are my settings and what I have tried / turned on. I can connect to the DB through SMSS via locahost.

What am I doing wrong?

• TCP/IP is enabled
• SQL Server and Windows Authentication mode is on
• TCP Port 1433 and TCP Dynamic Port is Blank
• Named Pipes is enabled
• SQL Server is running
• SQL Browser is running
• SQL Server Agent is running

Error:
ConnectionError: Failed to connect to localhost:1433 - Could not connect (sequence)

var Connection = require('tedious').Connection;
    var Request = require('tedious').Request;
    var config = {
        server: 'localhost',
        authentication: {
          type: 'default',
          options: {
            userName: 'UN',
            password: 'PW'
          }
        },
        options: {
            database: 'School',
            port: 1433
        }
      };

      const connection = new Connection(config);

      connection.on('connect', (err) => {
        if (err) {
          console.log('Connection Failed', err);
          throw err;
        }
        executeStatement();
      });

      connection.connect();

      function executeStatement() {
        const request = new Request('select * from Course', (err, rowCount) => {
          if (err) {
            throw err;
          }

          console.log('DONE!');
          connection.close();
        });

        // Emits a 'DoneInProc' event when completed.
        request.on('row', (columns) => {
          columns.forEach((column) => {
            if (column.value === null) {
              console.log('NULL');
            } else {
              console.log(column.value);
            }
          });
        });

        request.on('done', (rowCount) => {
          console.log('Done is called!');
        });

        request.on('doneInProc', (rowCount, more) => {
          console.log(rowCount + ' rows returned');
        });

        // In SQL Server 2000 you may need: connection.execSqlBatch(request);
        connection.execSql(request);
      }

[IanChokS]

TCP Port 1433 and TCP Dynamic Port is Blank

Can you try explicitly setting the TCP port to be 1433?

[sdtacoma]

@IanChokS

I have done that...

[IanChokS]

Do you have another service running in the background? Perhaps localhost is conflicting with another service. Try explicitly connecting to the server IP address

[sdtacoma]

@IanChokS

Different error when I specify an IP.

ConnectionError: Failed to connect to 192.168.1.42:1433 in 15000ms

[arthurschreiber]

@sdtacoma Based on what you describe, this should just work. 🤔

Have you tried not specifying the port and specifying the instancename instead to connect via a SQL server browser lookup? 🤔

[sdtacoma]

@arthurschreiber

When I specify the instance name I get the following error.

connection.on('connect', (err) => {
        if (err) {
          console.log('Connection Failed', err);
          throw err;     // line 33
        }

        executeStatement();
      });

Edit:
If I just supply the instanceName:

[IanChokS]

What version of tedious are you running?

[sdtacoma]

@IanChokS "tedious": "^11.0.6"

[sdtacoma]

Does it have anything to do with the SQL Server version I am running? It is 2019.

[IanChokS]

I was able to connect and run queries on the SQL Server 2019-latest in a docker image. Can you try to run the server in a docker image?

• Docker images

Steps I took in powershell:

• Pull the image: docker pull mcr.microsoft.com/mssql/server:2019-latest
• Start the SQL Server image: docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=password_01" -p 1533:1433 -d mcr.microsoft.com/mssql/server:2019-latest
• Set the config in examples/minimal.js:

var config = {
  "server": "0.0.0.0",
  "authentication": {
    "type": "default",
    "options": {
      "userName": "sa",
      "password": "password_01"
    }
  },
  "options": {
    "port": 1533,
    "database": "master",
    "trustServerCertificate": true
  }
}

• Ran the query:

const connection = new Connection(config);

connection.on('connect', (err) => {
  if (err) {
    console.log('Connection Failed');
    throw err;
  }

  executeStatement();
});

connection.connect();

function executeStatement() {
  const request = new Request("select 42, 'hello world'", (err, rowCount) => {
    if (err) {
      throw err;
    }

    console.log('DONE!');
    connection.close();
  });

  // Emits a 'DoneInProc' event when completed.
  request.on('row', (columns) => {
    columns.forEach((column) => {
      if (column.value === null) {
        console.log('NULL');
      } else {
        console.log(column.value);
      }
    });
  });

  request.on('done', (rowCount) => {
    console.log('Done is called!');
  });

  request.on('doneInProc', (rowCount, more) => {
    console.log(rowCount + ' rows returned');
  });

  // In SQL Server 2000 you may need: connection.execSqlBatch(request);
  connection.execSql(request);
}

• Output:

hello world
1 rows returned
DONE!

[sdtacoma]

Hi @IanChokS

That worked and I got the same output as you did. What does that tell us exactly?

42
hello world
1 rows returned
DONE!

Edit:

Thank you for telling me how to get the Docker image up and running too. That was very helpful.

[IanChokS]

Np! Glad to hear it worked. I think the original problem was probably something to do with your SQL Server set up 🤷‍♂️ Perhaps try reinstalling again. Are you planning on using the docker instance instead? If so I can close this issue.

[sdtacoma]

I can even connect to the docker instance through SSMS. That's great!

Yes, close the issue I will use the docker image. Thank you for all of your help and guidance.

[IanChokS]

Btw I would recommend looking into setting up volume mounts and database backups with docker, so that if the docker instance stops or needs to be restarted, you won't lose your data