Everything You Always Wanted To Know About Port Configuration But Were Afraid To Ask
דף הבית  >>  >>  הרשם  |  התחבר
מאמרים

Everything You Always Wanted To Know About Port Configuration But Were Afraid To Ask 

מאת    [ 05/07/2009 ]
מילים במאמר: 814   [ נצפה 2730 פעמים ]

 
 


The port that SQL Server listens to is divided into two cases, depending on the Instance type:


Default Instance - In this case, SQL Server listens by default to port 1433.  As this port is very well known and commonly used, it is subject to many attacks.


Named Instance - In this case, SQL Server listens by default to a dynamic port.


What is a dynamic port?  A dynamic port is a port allocated to SQL Server dynamically, meaning that the port is not fixed.  The dynamic port allocation process goes like this:


1.     Only a Named Instance can use dynamic ports.


2.     When the Instance appears for the first time, the port appears to be configured as 0.  This means that the SQL Instance is requesting that the operating system allocate it an available port.


3.     Once the operating system allocates a port for the Instance, it starts listening to that port.


4.     The allocated port is entered in the registry.


5.     Each time that the Instance appears, it will try to listen to the port listed in the registry.


6.     If another program is using the port, the SQL Server Instance will ask for another port.


So how does the client know which port to connect to?


In the case of a Default Instance, the client attempts to connect to good old port 1433.


The problem starts when we use a Named Instance, in which case there are two ways of letting the client know which port to connect to:


1.     Connection String -  We can state the port to which the Instance is listening in the Connection String (a bit tricky - as the port is dynamic and can change over time).  If we want to state the port in the Connection String, we need to use only the computer name and the port number, without any indication for the Instance name.  For example - Myserver,1578 instead of Myserver/InstanceName.
Of course, in this case we have to know which port the Instance is listening to.  This will be discussed later.


2.     Using SQL Server Browser - This is a Service that runs in an environment that has at least one Named Instance.  When it is running, it constantly listens to port UDP 1434.  When a client tries to connect to a Named Instance as follows Myserver/InstanceName, what happens is that a request is sent to the browser for port UDP 1434, and the response is the port to which the Instance is listening.


It should be noted, that when using the default port, the SQL Browser and port UDP 1434 are not required.


At this stage we should ask the question - what the devil do we do if we want to work using a Firewall, instead of using a port that is known to everyone????


The possibilities are as follows:


1.     Changing the Default Instance port and stating it in the Connection String.  In this case, we need to make sure that this port is open in the firewall.


2.     To configure a fixed port for the Named Instance and to state that port in the Connection String.  In this case, we need to make sure that the port is open in the firewall.


1)     To configure a fixed port for the Named Instance and to continue using SQL Browser.  In this case, we need to make sure that both that port and port UDP 1434 are open in the firewall. When using a Named Instance with a dynamic port and SQL Browser, it won't be possible to enforce working through the firewall, as the port can change.  Therefore, it is better not to use this method when using a firewall.


 


So how do we change the port?


We use the SQL Server Configuration Manager... actually, we read this - http://msdn2.microsoft.com/en-us/library/ms177440.aspx


 


 


 


How do we know which port the Instance is listening to?


1.     Check the SQL Server Configuration Manager, then Network Configuration, then TCP.


2.     Check the registry


SQL 2005


HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.<InstanceNumber>MSSQLServer
SuperSocketNetLibTCP


SQL 2008
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10.<InstanceName>MSSQLServer
SuperSocketNetLibTCP


 


 


So to summarize:


1.     When working with default Instances, we can change the port we are using (for security reasons). 


2.     When working with Named Instances, we can use either a dynamic port or a fixed port.


3.     If we change the default values, we need to state the port number in the Connection String: Myserver, xxxx (when stating the port number, don't state the Instance name).


4.     When using Named Instances, we can forego stating the port in the Connection String, and use SQL Server Browser, which uses port UDP 1434 and is used to notify applications which port is used by a SQL Server instance.


 


 


 

שחר בר
חברת ואלינור
http://www.valinor.co.il
http://www.sqlserver.co.il

LinkedIn - SQLServer Valinor
מאמרים נוספים שעשויים לעניין אותך:

שליחת המאמר שלח לחבר  הדפסת המאמר הדפסת המאמר  קישור ישיר למאמר קישור ישיר למאמר  דווח מאמר בעייתי דווח על מאמר בעייתי  כתוב לכותב המאמר פניה לכותב המאמר  פרסום המאמר פרסום המאמר 

©2017
כל הזכויות שמורות

מורנו'ס - שיווק באינטרנט

אודותינו
שאלות נפוצות
יצירת קשר
יתרונות לכותבי מאמרים
מדיניות פרטיות
רשימת כותבים
כותבים מומחים
עלינו בעיתונות
מאמרים חדשים
פרסם אצלנו
לכותבי מאמרים: פתיחת חשבון חינם
כניסה למערכת
יתרונות לכותבי מאמרים
תנאי השירות
הנחיות עריכה
לבעלי אתרים:



מדיה חברתית:
חלון מאמרים לאתרך
תנאי שימוש במאמרים
ערוצי מאמרים ב-RSS Recent articles RSS


מאמרים בפייסבוק מאמרים בטוויטר מאמרים ביוטיוב