Click here to Skip to main content
11,575,921 members (58,560 online)
Click here to Skip to main content

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

, 9 Apr 2014 CPOL 12.5K 8
Rate this:
Please Sign up or sign in to vote.
Implementing security in SQL Server by only allowing TCP/IP Network Protocol and changing default TCP port

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. Click on Connection Properties tab
  10. Under the Network section, select TCP/IP in Network protocol dropdown list
  11. 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)
  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/)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Aqeeel
Software Developer
Pakistan Pakistan
No Biography provided

You may also be interested in...

Comments and Discussions

 
QuestionWhat changes do I make if it is connected through dynamic IP Pin
Shivangi_K8-Jun-15 22:54
professionalShivangi_K8-Jun-15 22:54 
QuestionHi Pin
Member 1061297721-Aug-14 10:09
memberMember 1061297721-Aug-14 10:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150603.1 | Last Updated 9 Apr 2014
Article Copyright 2014 by Aqeeel
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid