en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
ספט4

Written by: ronen ariely
04/09/2014 09:41 RssIcon

Introduction

SQL Server can listen to request which come to specific IP address on specific port, or to all request regardless the IP, It can listen to request which come to a Named pipe, as well. By default, SQL server uses port 1433 in TCP/IP, and default instance of Microsoft SQL Server listens on "\\.\pipe\sql\query" named pipe.

In this short article I will show how we can configure those options.

Configure Listening IP, Port

1. Open "SQL Server Configuration Manager".

2. Expand "Protocols for "

3. Double-click TCP/IP

4. Move to the Protocol tab and make sure to set Enabled=Yes

5. In the TCP/IP Properties dialog box, move to the IP Addresses tab

Several IP addresses appear in the window named as IP1, IP2, up to IPAll. One of these is for the local IP address 127.0.0.1. Additional IP addresses appear for IPv4 and for IPv6 Addresses.

If your machine IPs were changed you might find the old IP's address. You can change the IP address to relevant address.6. Set Enabled=Yes for each IP address that you want to use.

7. If you want to specify a specific port, then delete the "Dynamic Ports" value, and type the port to use in the "TCP Port" field.

TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports

Configure Listening Named pipe

1. Open "SQL Server Configuration Manager".

2. Expand "Protocols for "

3. Double-click Named-pipes

Just like IP address/port for incoming requests listening, a server can also set up a named pipe which can listen for requests. Named-pipes, provide a mechanism for among process communication running on the same machine. Named pipes usually much faster, and frees up network stack resources.

Important! Restart the SQL Server service

In the console pane, click SQL Server Services -> right-click "SQL Server " and then click Restart.


Note! Trace Flags 4010 allows only shared memory connections to the SQL Server! This Mean that you will only be able to connect from the server machine itself. Client connections over TCP/IP or named pipes will not happen.

Note! You can use the command line in oder to compare the information r to check the corrent IP adresses

ipconfig /all

Resources & More Information

How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)
http://msdn.microsoft.com/en-us/library/ms177440%28v=sql.105%29.aspx

Configure a Server to Listen on an Alternate Pipe (SQL Server Configuration Manager)
http://msdn.microsoft.com/en-us/library/ms189321.aspx

Trace Flags in SQL Server
http://social.technet.microsoft.com/wiki/contents/articles/13105.trace-flags-in-sql-server.aspx