Fixing the Microsoft.Data.SqlClient.SqlException in my ASP.NET Application

It was all a good feeling working on an application for some 15 days until I suddenly hit an error wall.

Exception caught by the Debugger

This would be my first proper to-be-deployed full-stack application since I picked up C# and .NET development about two months ago. As part of the learning process, I’ve had a few small demo apps that did basic CRUD and API functionalities before now.

I spent the next few hours fiddling with it before I went to bed. The next day, I did a few google searches to understand how database connections worked and read about similar errors in the Microsoft forums and other sites. I asked a few experienced .NET developers I know about it.

Today, I sat down at it and dealt with it.

How did the error come about?

Before the error showed up, I’d tried to switch from an in-memory database to Microsoft SQL by adding the DbContext as a service in the Program.cs file of the project.

...

// Adding db context
var builder = WebApplication.CreateBuilder(args); 
builder.Services.AddDbContext<Entities>(options =>
    options.UseSqlServer(        
        "Data Source=localhost,54002; Database=Flights;User ID=lead; Password=9876$Secret;"
        ));

...

In the above code:

• The connection string is passed as a parameter in the UseSqlServer() function
• localhost is assumed to be the name of the database server, and 54002 is the connection port.
• Database is the database that I created in SSMS.
• User ID and Password are the credentials of the SQL Server Authentication login I created while setting up SSMS. You can read more about that in the guide on creating a login in SQL Server.

Running the project in debug mode, then flagged the above portion of the code with the following exception log:

Microsoft.Data.SqlClient.SqlException: 'A network-related or
 instance-specific error occurred while establishing a connection
 to SQL Server. The server was not found or was not accessible. 
 Verify that the instance name is correct and that SQL Server is 
 configured to allow remote connections. 
 (provider: Named Pipes Provider, error: 40 - Could not open a 
connection to SQL Server)'
Inner Exception:Win32Exception: The network path was not found.

A network-related or instance-specific error occurred while 
establishing a connection to SQL Server. The server was not 
found or was not accessible. Verify that the instance name is correct 
and that SQL Server is configured to allow remote connections. 
(provider: SNI_PN11, error: 25 - Connection string is not valid)

The following section explains how I fixed the error.

How did I fix the error?

• The key phrase in the error logs is ‘connection string is not valid.’
• It also advises confirming the instance server name is correct.

Therefore, I must find the correct connection string for the project database and cross-check the server instance properties.

SQL Server Instance tab

Steps

1. I opened the SQL Server Object Explorer tab in the View menu of Visual Studio.

2. I searched for my server in the SQL Server Object Explorer. One can always check the name of one’s server in the SQL Server Management Studio SSMS when one connects to a database engine.

3. Sometimes, the server shows automatically in the SQL Server Object Explorer. Sometimes it doesn’t show. In that case, I could easily add it from the top left of the panel by clicking the + Add SQL Server button’s icon, then going to Browse > Local, where I select the server in the menu list.

4. Once I could see my server in the SQL Server Object Explorer, I right-clicked and selected Properties. The properties tab opens up, showing the SQL server instance properties. See the image above this section. The first property there is the connection string. The connection string here consists of Data Source, User ID, Password, and other parameters.

5. I copied the connection string to the UseSqlServer function of the DbContext part of my Program.cs file.

"Data Source = SQL_SERVER_NAME; User ID = lead; Password = 9876$Secret; Connect Timeout = 30; Encrypt = False; TrustServerCertificate = False; ApplicationIntent = ReadWrite; MultiSubnetFailover = False"

6. The server instance name in the copied connection string is in the Data Source variable. The Data Source variable holds the server instance name, the name of the server I created. It wasn’t the local database server and its port.

6. I also noted that the Data Source value in the copied connection string has spaces in between. My PC named the server with the spaces in the SQL Server by default. I had to remove the spaces here.

7. Running the .NET project with the spaces will flag the same error from above.

7. I saved the code and ran the project in both debugging and non-debugging modes, and it ran successfully. 😊

Finally

I hope this solution will be helpful if you encounter the same error in a similar scenario. ASP.NET has been fun. I’ve had my fair share of mistakes and blockers, and I’ve been able to deal with them.

I will try to keep you abreast of things I learn going forward. You can follow and subscribe to get an email when I publish. 😎

It is noteworthy that the Microsoft dotnet documentation is an excellent repository of information on ASP.NET. Try to spend more time there. The team at Tutorials.eu also does a good job teaching courses in C# development. The dotnet channel on YouTube is another good resource point.

To see other ways of debugging this error, see the official Microsoft documentation on a network-related or instance-specific error occurred while establishing a connection to SQL Server