October 27, 2008 at 10:31 pm
Hi Folks,
I had a strange issue the other day where the port on a named instance had changed on me.
I assume it was because I restarted the services for the instance. Has anyone else experienced this problem and how did they fix it?
October 28, 2008 at 3:07 am
Use SQL Server Configuration Manager to check if your instance is configured for dynamic ports:
Under "SQL server network configuration" select Protocols for .
Double click TCP/IP and under the tab "IP addresses" check if the dynamic port is enabled.
If so, put a port number in the TCP Port (the same for all ip addresses in this mask) then restart the services.
Take care to chose a port not used by other services...
See The Jenga blog
October 29, 2008 at 12:08 am
so you are saying if I use dynamic ports then restarting the service will change the port?
😛
October 29, 2008 at 9:52 am
That's the meaning for "dynamical".
More seriously: usually the port of the instance is the same. But sometimes (i.e. if the port is used by another service) SQL server will chose another one.
To be SURE that an instance always listens on the same port, configure it to be static.
For example if you connect through a firewall you need to open the correct port on the firewall...
take a look:
http://msdn.microsoft.com/en-us/library/ms177440(SQL.90).aspx
See The Jenga blog
October 29, 2008 at 10:05 am
There is no guarantee that the ports will be the same on a restart. That's why your clients contact the server on 1434 and then get the port for that named instance.
Also, in general you don't want to connect by port, connect by name. Unless you have firewall settings you need to keep.
October 29, 2008 at 6:14 pm
If I connect to a named instance via ODBC don't I have to specify the port?
October 30, 2008 at 7:22 am
bodhilove (10/29/2008)
If I connect to a named instance via ODBC don't I have to specify the port?
Depends on ODBC Driver. MS drivers don't if they can get to the port 1434, they can connect by server\instancename. If 1434 is not available, even MS's drivers need to the port specified, or an alias entry. Most 3rd party drivers require you top specify the port, either in a connection string, dsn, or an alias file.
October 30, 2008 at 1:02 pm
Hi,
For security reasons, sick to Igors recommendation to use static ports. http://technet.microsoft.com/en-us/library/cc262849.aspx#section3 "Secure communication with the Microsoft SQL Server database" describes this a bit.
Once you have set everything up to use static ports you can also go ahead and shut down the SQL Browser service (but remember you need to turn it on if you want to make a DAC, dedicated administrators connection, http://msdn.microsoft.com/en-us/library/ms181087(SQL.90).aspx contains full details).
After that, nothing or no one will be able to connect to your server without specifying the port (which is a good thing!).
HTH!
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply