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?
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:
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.
Note that I replaced steps 4 and 5 with the following steps on a Linux machine running a NodeJS application:
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.
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(