Configuring SQL Server 2016 Express On LAN For C# Connection String

Introduction

In this article, I will share my experience about configuring SQL Server 2016 Express on LAN for SQL connection using my C# application’s connection string. I’m sure there are many guides out there on this topic already. But, despite reading those while configuring it myself, I still hit some errors that need minutes or hours to resolve. So, I decided to document the process here for my own documentation, and for others who would like to do the same thing.

Background

I was developing a C# WPF desktop application that connects to the local instance of SQL Server 2016 Express. There was no problem connecting to the local instance of SQL Server 2016 Express using standard SQL Server connection string:

Server=.\SQLEXPRESS;Database=MyDatabase;User ID=MyUser;Password=MyPassword

But when my application went for testing, and it needed to connect to an SQL Server 2016 Express instance to a computer located on my LAN with the following connection string -

Server=192.168.1.100\SQLEXPRESS;Database=MyDatabase;User ID=MyUser;Password=MyPassword

- there was an error message saying that I couldn’t connect to the SQL Server instance because it was not found on my LAN. I was sure the root cause of the error is the SQL Server 2016 Express configuration, and also, the computer configuration where my SQL Server 2016 Express was installed.

Software, OS, and LAN Configuration

Before we continue further, let’s define the scopes. In any computer system configuration, there are many possibilities that can cause some cases easy to resolve, but not for some others. In other words, it depends on the specific scenario on hand. In my case, the following software, OS, and LAN configuration are used as scopes:

• Local LAN address 192.168.1.0, with subnet mask 25.255.255.0.
• My PC uses Windows 10 operating system, with Windows Defender Firewall activated. Its IP address is 192.168.1.125.
• My PC is my primary development environment, with Visual Studio 2017 on it. My PC is connected to the LAN without any Windows Server as a domain controller (and no group policies of course).
• SQL Server 2016 Express instance is installed on both my PC and another PC that acts as a database server. During development, I always use my local SQL Server Express instance. The database server is used only for testing purpose. IP address of my database server is 192.168.1.100.

Configuring SQL Server 2016 Express Database Server

So, here is my step by step guide for configuring SQL Server 2016 Express on my database server so, that my C# application in the network can use that database.

1. Make sure the Server properties are set to “Allow remote connections to this server”. Usually, this step is unnecessary since SQL Server 2016 Express already sets this value by default. Nevertheless, if we want to make sure, we use SQL Server Management Studio (SSMS) on the local database server.
   
   Just click on the database instance, for example (local)\SQLEXPRESS, and then click Properties. On the Server Properties page, select Connections page, and then make sure the setting for “Allow remote connections to this server“ is checked.
   
   
   

2. From the Start menu, choose “SQL Server 2016 Configuration Manager”. Expand the “SQL Native Client 11.0 Configuration (32 bit)”, click on “Client Protocols”, on the right pane, right-click on “TCP/IP”, and then choose "Enable" from the context menu.
   
   
   

3. In the same window, expand the “SQL Server Network Configuration”, click on “Protocols for SQLEXPRESS”, choose “TCP/IP”, and then choose "Enable" from the context menu.
   
   
   

4. After enabling TCP/IP in “Protocols for SQLEXPRESS”, from the same context menu, choose Properties to open “TCP/IP Properties” window. Click on the “IP Addresses” tab, then scroll down to the bottom of the page. In “IPAll” section, clear the value for “TCP Dynamic Port”, and fill the value of “TCP Port” with 1433. (You can use other port number for SQL Server, but 1433 is the standard one).
   
   
   

5. Back to “SQL Server Configuration Manager” window, on the left pane, choose “SQL Server Services”. Then, on the right pane, right-click on “SQL Server (SQLEXPRESS)” service and choose "Restart" on the context menu.
   
   
   

6. From Windows Start menu, type “services” to open Services window. Scroll down until you find “SQL Server Browser” service. Change the startup-type of the service to "Automatic", and then, start the service. This step is necessary if we want to use connection string to connect to a named instance of SQL Server service without specifying the port number.
   
   
   

7. The next step is to configure the firewall. If you turn off Windows Defender Firewall on your database server, then you don’t have to configure this setting. Otherwise, open “Windows Defender Firewall” from Windows Control Panel, then choose "Advanced settings" on the left pane. Note that in previous Windows version, the name of Windows Defender Firewall is only Windows Firewall.
   
   The objective of this step is to allow inbound connections to port 1433 TCP used by SQL Server service, and port 1434 UDP used by SQL Server Browser service.
   
   
   

8. In “Windows Defender Firewall with Advanced Security” window, add two new rules in “Inbound Rules” section. In the first step of “New Inbound Rule Wizard”, choose “Port”.
   
   
   

9. Then, in the second step, choose TCP and fill the value for “Specific local ports” to 1433. Then, click the "Next" button until you reach the last step of the wizard. In the last step, you are required to give a name for this new rule. Give it any name you like, for example: “SQL Server port”.

10. Repeat step 9 for UDP protocol, and port number 1434.

11. Now, you should be able to use SQL Server Management Studio from another server, and also, use it in C# code connection strings.

Troubleshooting

If you have already performed all the steps above but still not able to connect to the SQL Server, what you should do? Here are the troubleshooting steps that I performed to resolve such situation.

1. Make sure that our client PC is located in the same network with the database server machine. Use "ipconfig" command to enquire local PC IP address, and then, use ping command to inquire database server’s IP address. When doing ping command, it’s better to turn off Windows Defender Firewall on database server machine temporarily.

2. Make sure that the database server’s port 1433 (TCP) and 1434 (UDP) are up and running. We can use network debugging tool such as Nmap to do port scanning on database server machine.

3. It’s also worth trying to turn off Windows Defender Firewall on the database server while we are troubleshooting. This temporary step eliminates the possibility of Windows Defender Firewall preventing inbound connections to the database server.

4. Use SQL Server Management Studio (SSMS) to connect to the database server before using it in a connection string. If SSMS can detect database server, you should have no problem using it in a connection string.

5. Specify port in the connection string. This port part is necessary if we didn’t enable the SQL Server Browser service.
   
   Server=192.168.1.100\SQLEXPRESS,1433;Database=MyDatabase;User ID=MyUser;Password=MyPassword

Summary

In this article, I have explained the steps necessary to configure SQL Server 2016 Express for C# connection string.