Node.js is an exciting technology that has been widely adopted. For those starting out, one of the key requirements is the ability to connect node.js with an enterprise RDBMS such as MS SQL Server.
In this post, I will guide you through the process of connecting your Node.js app with SQL Server successfully, and hopefully, without any confusion or errors.
1. Download
If you don’t have SQL Server Management Studio and Node.js installed on your system, you can download and install them from the links below:
- SQL Server Express Edition
- SQL Server Management Studio (SSMS)
- Node.js
- Version number might differ.
(Skip the below step if you’re able to login to SQL Server in SQL Server Authentication mode)
2. Fixing the Server authentication mode for new SQL Server login
There is a very good chance that you will be able to log in with windows authentication mode in SQL server but you will be denied access when you try to login in SQL Server authentication mode. This is because SQL Server was not configured to allow mixed authentication. Here are the steps to fix:
- Right-click on SQL Server instance at the root of Object Explorer
2. click on Properties
3. Select Security from the left pane
4. Select the SQL Server and Windows Authentication mode radio button, and click OK.
5. You need to restart the SQL Service (SQLEXPRESS) Windows service for the changes made to be applied
Check Step 4 to restart the SQL service
6. Now you should be able to login in SQL Server Authentication mode with the user id and password that you’ve created for the user.
3. Configuring TCP/IP protocol
Once you are able to login into SQL Server in SQL Server Authentication mode, it’s time to do some configurations.
- Press (Windows key + R) and type compmgmt.msc in the box, and click Ok
2. Now click on Services and Applications in the left pane and then double click on SQL Server Configuration Manager to open it
3. Now double click on SQL Server Network configuration on next page
4. Now double click on Protocols for SQL Express
5. The TCP/IP protocol should be enabled if we want to connect Node.js to SQL Server if it’s not enabled then you can enable it by right-clicking the TCP/IP protocol and select Enable
6. Once you have enabled TCP/IP, right-click on it and select Properties
7. In the Pop-up window opened select IP Addresses
Make sure all the TCP Dynamic Ports are set to 0 except for IPAll TCP Dynamic Port. All the TCP ports should be set blank except for IPAll TCP port which needs to be set to 1433
These ports can be edited manually by double-clicking the values
8. Click Apply
4. Restarting the SQL Service (SQLEXPRESS) Windows service
- Type Services on windows start bar and open Services
2. Look for SQL Server (SQLEXPRESS), select and restart it from the restart button at the left pane
3. Now you also need to make sure that SQL Server Browser service is also running, if it isn’t running then follow the below steps:
a) Right click on SQL Server Browser service and click on Properties
b) Select Automatic from Startup type dropdown and click Ok . Now try to restart the service, it should work fine
5. Checking if all services required are up and running
1. Go to SQL Server Configuration Manager
Windows+R > Type compmgmt.msc in the box > Ok > Services and Application > SQL Server Configuration Manager
2. Click on SQL Server Services and make sure SQL Server Browser and SQL Server (SQLEXPRESS) services are running. If any of the services is stopped you can start them by right-clicking the service and selecting Start
You can try reconnecting to SQL Server now with Node.js app. Below is a code snippet to configure MSSQL Server in Node.js
var mssql = require(“../node_modules/mssql”);
// mssql config
var dbConfig = {
server:”DESKTOP-3NE93V7\\SQLEXPRESS”,
database:”WideWorldImportersDW”,
user:”nick”, // Update it
password:”user123”, // Update it
port:1433
}
// Connection to DB for Mysql
var conn;
connectToDB=function(){
conn = new mssql.ConnectionPool(dbConfig);
conn.connect(function (err){
if (err){
console.log(err);
return;
} }); }