SQL Server Issues | Login Failed for user sa | Refused to connect

Scenario:

Recently I got a requirement where I have to use sql server database in my node application. So I buckle up and end up installing SQL Server Management studio and sql server express, they were fulfilling all my requirements. But when I start my development I encoutered following 2 issues:

• Failed to login in SQL Server Management Studio as sql server authenticator.
• Failed to connect to Sql server in my application when using default port.

Issue 1:

Login Failed for user sa
We often face this login issue when we create a user in Sql Server Management Studio. Follow these steps to successfully create and use that user to login.

Step 1:

Connect as windows authentication. After connecting you have a side nav with option security. Right click on security and choose option new Login.

Step 2:

Now create new user as follow.

Step 3:

Your user is created in step 2 but it wont login and give the error “Login Failed for user sa” or it could be any pipe error too. Dont get stress with the error, just follow this step and your error will be resolved.

Right click on the windows authentication user >> goto properties >> click on security. Enable the sql server and windows authentication mode.

After following these three steps, restart your sql server service from services and now login with the username and password you specified while creating a user.

Voila! your issue is resolved.

Issue 2:

Encountered a database connection failure
Every database has a default port like mySQL has 3306, sql server 1433. When we connect any database other than sql server, they connect on default port but sql server refused to connect on default port.

To resolve this issue we have to enable the TCP/IP option in sql configuration manager and it will give you the local port on which your db will connect locally.

Checkout the demonstration below for clarity.

In above screenshot, TCP/IP option is disabled. So we will enable this and restart the sql server service in windows services.

To get your port, double click on TCP/IP and window pop up. Go to IP addresses tab, scroll down and you’ll find the TCP dynamic port property and it holds the port you have to use for connection in your code.

Just by enabling the TCP/IP option, my issue resolved and now I can easily connect to sql server via code.

CONCLUSION:

These are the major 2 problems which I faced while connecting sql server to my node application. Sometimes configurations consume most of our development time due to such minor issues.

Hope this piece of information save your time and if you have relevant issues, do share your feedback. We definitely work on those issues too and serve you with the solution.