Secured Connection Between SQL Server and C# Using Specific Port

In this article we will discuss how get the port no of our own SQL Server instance and how to use that port in SQL Server setting and at the end how use that port in your C Sharp application.

A few days ago I experienced the situation in which my client neeeded to shift his database server to a static IP. This choice makes the database server very insecure. So we decided to close all our unnecessary ports on the router. After closing the unnecessary ports, connections between C# and SQL Server creates problems. We even tried to use the default port of SQL Server, 1433, but it's not working because of we are not using a default instance of the SQL Server. In this article we will discuss how to get the port number of our own SQL Server instance and how to use that port in a SQL Server setting and at the end how to use that port in your C# application.

Step 1

We are assuming that we have installed SQL Server with the instance name CRMIS and first of all we need to find the specific port of that instance using regedit.

So click on "Start" -> "Run" and enter "regedit" and click "Ok". Then in the Registry Editor navigate to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MicrosoftSQLServer\MSSQL.<InstanceNumber>\MSSQLServer\SuperSocketNetLib\TCP\

SQL1.jpg

Step 2

In this step we will click "Start" - > "Programs" -> "Microsoft SQL Server 2005" -> "Configuration Tools" -> "SQL Configuration Manger".

SQL2.jpg

Step 3

In this step we will select CRMIS from the left window and then double-click on TCP/IP. That will open TCP/IP Properties.

SQL3.jpg

From the TCP/IP windows select the IP Addresses table as shown in the following picture:

SQL4.jpg

From the IP Addresses select IP ALL property and enter the port number in front of the TCP Dynamic Port as shown in the picture.

Step 4

In this step we will create a new C# Application that will build the connection between C# and SQL Server using a specific port. Our project name is "Specific port" as shown in the following picture:

SQL5.jpg

Step 5

In this section we will create a new class with the name "Operation" using "Project" -> "Add class" as shown in the picture.

SQL6.jpg

Now we will add code for the class Operation. Please note that we have created a text file with the name "IPCONFG.txt". Using that file, we can pass an IP address and port number to our connection string. This looks like:

SQL7.jpg

SQL8.jpg

Step 6

In this section we will add the following code in the program class.

SQL9.jpg

Step 7

Now add the label on Frmlogin and add the text on Connection Build Successfully.

Step 8

Press F5 to execute the program.

SQL10.jpg