CODEWITHSUNDEEP
sql-servernode.js
jtlindsey
jtlindsey

Reputation: 4863

How do you configure SQL Server to allow a database connection locally or from a remote machine?

How do you configure SQL Server to allow a database connection locally or from a remote machine? I have a simple test with user=sa and password=123456. I can login to the server instance using SQL Management Studio with SQL Server and Windows Authentication mode enabled. I tried to login from another computer (Linux) using mssql module from npm to connect and I get a message: 'Login failed for user \'sa\'.', code: 'ELOGIN'.

That was after opening up port 1433 in windows firewall (Windows 10). Here is my config and i get the same results from remote machine if i use computer name for server or IP address for server. Note I'm using the demo database that can be downloaded for SQL Server 2016.

const config = {
    user: 'sa',
    password: '123456',
    // server: '192.168.12.14',
    server: 'DESKTOP-AAFERR',
    // port: '1433',
    database: 'AdventureWorks2016CTP3',
    pool: {
        max: 10,
        min: 0,
        idleTimeoutMillis: 30000
    }
}

What i tried to do was open SQL Server Configuration Manager and enable TCP/IP for the server instance. However when I did that, I was not able to login locally from SQL Server Management Studio anymore using SQL Server authentication. I would get message Login failed (Microsoft SQL Server, Error: 18456). But I also still could not login remotely. So i disabled TCP/IP again so i could login locally.

How do you configure SQL Server to allow a database connection locally or from a remote machine? I opened port 1433 in the firewall. What else do I need to configure in SQL Server Management Studio or SQL Server Configuration Manager?

Upvotes: 1

Views: 2036

Answers (2)

Javier Segarra Martinez
Javier Segarra Martinez

Reputation: 46

Probably this is the best method 

var config = {
    user: '',
    password: '',
    server: '', 
    database: '' 
};
var sql = require('mssql')
var conn = new sql.Connection(configBD.config)


sql.connect(configBD.config, function (err) {


if (!err) {


 console.log('Database  is connected ... \n\n')
  } else {
    console.log('Error connecting database ... \n\n')
  }
})

    var request = new sql.Request()
request.query(

Upvotes: 0

jtlindsey
jtlindsey

Reputation: 4863

After going through all settings in SQL Server Configuration Manager, I noticed that the machine I'm working on has another database instance that is not the default instance but was using the default port 1433. I found this in the logs for the default instance Server TCP provider failed to listen on 1433, TCP port is already in use. And confirmed this by looking at the settings for the other database instance. So i changed the default instance I was working with to run on a different port and everything worked.

Steps To Configure SQL Server to Allow a Database Connection Locally Or From a Remote Machine?

Follow the steps microsoft outlines in this link. Summary:

  1. Enable TCP/IP
  2. Configure Fixed Port (this is where my error was, I had configured a port that was already in use)

Note: You will need to restart SQL Services for the changes to take effect. Visit this link to see how to accomplish that with SQL Server Configuration Manager, SQL Server Management Studio, net Commands from a Command Prompt window, Transact-SQL, or PowerShell.

  1. Open Ports in Firewall
  2. Connecting to the Database Engine from Another Computer
  3. Connecting Using the SQL Server Browser Service

Note that I replaced steps 4 and 5 with the following steps on a Linux machine running a NodeJS application:

  1. npm install mssql
  2. Follow the custom configurations based on your preferences. You can look at my example from the original post. Note you will need to uncomment the port and replace 1433 with the custom port you specified when you configured the fixed port.

If you run into problems, check the server log for the SQL Server database instance you are working with. That's where the clues were for my problem.

  1. In the Microsoft SQL Server Management Studio, expand the SQL Server.
  2. In the Object Explorer, expand Management → SQL Server Logs.

Upvotes: 1

Related Questions