Hello Dear Readers,

My friend Mr. Talha Ansari and I came across the above statement as a requirement and which had to implemented in Testing Environment and then in Production Environment too. The best strategy was the Divide and Conquer ... so we divided the statement in two portions!

The statement consists of two parts:

1. Using only TCP/IP for connecting to SQL Server 2008and
2. Changing the SQL Server 2008's default TCP/IP port

By default Named Pipes are used to establish connection with SQL Server, we have to manually update the protocol setting to allow only one protocol for SQL Server connection.

1. To disable client protocols in SQL Server Native Client Configurations

1. In SQL Server Configuration Manager, expand SQL Server Native Client Configuration, right-click Client Protocols, and then click Properties
2. Click a protocol in the Enabled Protocols box, and then click Disable, to disable a protocol (Repeat step 2 for Shared Memory, Named Pipes and VIA)

2. To change the TCP/IP port in SQL Server Native Client Configurations

1. In SQL Server Configuration Manager, expand SQL Server Native Client Configuration and click Client Protocols,
2. Right-click the TCP/IP protocol in the list and then click Properties, update the Default Port to desired Port Number in my case say 1533
3. Click Apply and then OK button to apply settings

3. To disable client protocols in SQL Server Network Configurations

1. In SQL Server Configuration Manager, expand SQL Server Network Configuration, and select Protocols for <Instance Name>
2. Right-click a protocol in the list, and then click Disable, to disable a protocol. (Repeat step 2 for Shared Memory, Named Pipes and VIA)

4. To change the TCP/IP port in SQL Server Network Configurations

1. In SQL Server Configuration Manager, expand SQL Server Network Configuration and click Protocols for <Instance Name>,
2. Right-click the TCP/IP protocol in the list and then click Properties
3. In IP Addresses tab, for system's IP Address and Loop Back Address do the following activities
4. Set enabled to Yes
5. Update the TCP Port to desired Port Number in my case; say 1533
6. In IP Addresses tab, for system's IPAll update TCP Port to desired Port Number in my case; say 1533
7. Click Apply and then OK button to apply settings
8. Make sure the restart the SQL Server instance before moving forward

5. Establish connection with SQL Server using SQL Server Management Studio

1. Execute the SQL Server Management Studio
2. Select Database Engine in Server Type dropdown list
3. Click the Options button in the bottom of the Connect to Server form
4. Provide Server Name in Server Name textbox
5. Server Name will consist of: <Server Name>, <Domain>and <TCP/IP Port> (where <Server Name> is separated from <Domain> with a dot "." and <Domain> is separated from <TCP/IP> port with a comma ","). Final Server Name will be like <Server Name>.<Domain>,<TCP/IP Port>
6. Select Authentication Type in Authentication dropdown list
7. Provide Login Name is Login textbox
8. Provide Password in Password textbox
9. 
10. Click on Connection Properties tab
11. Under the Network section, select TCP/IP in Network protocol dropdown list
12. 
13. Click the Connect button to connect to SQL Server

6. Update Application Connection String to establish connection with SQL Server

1. Locate the web.config or app.config file of the application
2. Open file in notepad or any text editor
3. Update the existing connection string by
   1. Updating the Data Source. Data Source will consist of <Server Name>, <Domain> and <TCP/IP Port> (where <Server Name> is separated from <Domain> with a dot "." and <Domain> is separated from <TCP/IP> port with a comma ",". Final Server Name will be like <Server Name>.<Domain>,<TCP/IP Port>)
   2. Adding Network Library (refer to Network Protocol for SQL Server Connection in references)
   3. 
4. Save the file, restart the IIS or Application and you are ready to go ...

References

1. Configuring Client Network Protocols (http://technet.microsoft.com/en-us/library/ms190425(v=sql.100).aspx)
2. Choosing a Network Protocol (http://technet.microsoft.com/en-us/library/ms187892(v=sql.105).aspx)
3. How to configure SQL server to listen on different ports on different IP addresses? (http://blogs.msdn.com/b/sqlblog/archive/2009/07/17/how-to-configure-sql-server-to-listen-on-different-ports-on-different-ip-addresses.aspx)
4. SQL Server 2008 connection strings (https://www.connectionstrings.com/sql-server-2008/)
5. Network Protocol for SQL Server Connection (https://www.connectionstrings.com/define-sql-server-network-protocol/)