none
SQL 2008 R2 Named instance and static port

    Question

  • I am issues connecting to SQL Server named instance configured to use static port  (I am trying to connect from PROD server to DR server and both machines are running SQL Server 2008 R2 Enterprise edition).

    The SQL is named instance and is configured to run on port 49152. Below image show the TCP/IP properties.

    From Production, I am trying to connect using Management Studio and in the Server Name field, I tried all 3 options listed below and I am unable to make a connection:

    1. DRServer\Instance
    2. DRServer\Instance,49152
    3. DRServer,41952

    The Intel team changed firewall settings but I still could not connect. Not sure if it matters, but the SQL Server Browser service is disabled on both PROD & DR systems. There is a default instance as well on both PROD & DR servers. The named instance is set to use a specific IP address (out of 2 available), so I am little unsure if I should put anything in the fields under "IPAll" and just fill in the "IP1".

    I would like to know if there are any errors in the TCP/IP configuration or the Server Name used for connections (and which one is correct).

    Any help is really appreciated.


    Don

    Monday, June 25, 2012 10:08 AM

Answers

  • Hi Don,

    Please follow below steps to configure your named instance to listen on specific port

    For Standalone instance of the SQL server  change the dynamic port of the named instance to the static port by using SQL server configuration manager in SQL 2005/2008. In order to change the port we need to go to the SQL server configuration manager.

    Start->Program Files-> Microsoft sql server 2005->Configuration Tools-> SQL server configuration manager.

    In configuration manager console, on the left hand panel, expand the SQL server  Network Configuration. Click on the”Protocols for <Instance_Name>”. On the right hand panel we need to right click and go to properties on the TCP/IP.

    Click on the IP Addresses tab on the top and keep the “Dynamic ports” row BLANK and write the desired port no. on which you want the named instance to listen on “TCP ports” row in the “IP ALL “section.

    After performing these activities we need to restart the SQL server services for the changes to take affects. After restarting the service you can confirm that whether changes has taken affect by checking the following registry key

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name> \MSSQLServer\SuperSocketNetLib\Tcp

    This key should contain the port no. defined by you.

    After you have configured SQL Server to listen on a specific port, there are three ways to connect to a specific port with a client application:

    • Run the SQL Server Browser service on the server to connect to the Database Engine instance by name.

    • Create an alias on the client, specifying the port number.

    • Program the client to connect using a custom connection string.

    Hope this may help.


    Regards, Vishal Srivastava

    Monday, June 25, 2012 9:01 PM
  • Well, make changes in firewall (add UDP port 1434) and let us know how was it going.

    It did turn out to be firewall issue. The named instances were configured to listen an IP2 and when I made a connection, it was using IP1 for out going but the firewall was opened only for connections between IP2 (PROD) - IP2 (DR). We had a Network person monitoring the activity which showed the connection was going from IP1 (PROD) to IP2 (DR). Once the firewall was opened, I was able to make connections.

    Don

    Saturday, June 30, 2012 9:02 PM

All replies

  • SQL Server browser should be enabled . If you look into ERROR.LOG file do you see that this instance listens to 41952 port?


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Monday, June 25, 2012 10:41 AM
  • once the browser service is enabled, make sure UDP port 1434 is allowed on the firewall too..

    what are the errors you are getting?



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Monday, June 25, 2012 10:49 AM
  • SQL Server browser should be enabled . If you look into ERROR.LOG file do you see that this instance listens to 41952 port?

    I tried enabling SQL Server browser and it did not work. The Error log does show "Server is listening on [ xx.xx.xx.xx <ipv4> 49152]".

    Don

    Monday, June 25, 2012 11:14 AM
  • once the browser service is enabled, make sure UDP port 1434 is allowed on the firewall too..

    what are the errors you are getting?



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    I will let Intel team to enable firewall on UDP port 1434 (is there a reason for opening this port ?).

    When I try to connect using DRServer\Instance, I get error "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server)"

    When I try to connect using DRServer\Instance,41952, I get error "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)"

    Whats the correct format of server name I should use in SSMS ?


    Don

    Monday, June 25, 2012 11:21 AM
  • >>>browser and it did not work. 

    Any error? Are you administrator on the server? What account SQL Server Browser is running under? I am almost sure that the error you are getting above is firewall settings...


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Monday, June 25, 2012 11:28 AM
  • >>>browser and it did not work. 

    Any error? Are you administrator on the server? What account SQL Server Browser is running under? I am almost sure that the error you are getting above is firewall settings...


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    I get the same errors I get with browser server is disabled. The SQL Server Browser service is running under Local Service account while all SQL Services are running under domain account.

    Don

    Monday, June 25, 2012 11:32 AM
  • you should be able to connect with just "drserver,49152" as your connection.

    Can you logon locally to the drserver and try connecting to sql server locally?

    also check that remote connections are enabled

    Uri's probably right - firewall sounds likely



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Monday, June 25, 2012 11:38 AM
  • Well, make changes in firewall (add UDP port 1434) and let us know how was it going.

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Monday, June 25, 2012 11:59 AM
  • The correct way to specify the server is "DRServer,49152" or "x.x.x.x,49152".  My guess is the name "DRServer" is not resolving to the correct IP adress.  Please try the IP address directly.

    Can you go to a command prompt and run "telnet x.x.x.x 49152"  What do you get?  Does it error?  Until telnet works, you will never be able to connect to it via SSMS.

    PS.  If you specify the port number in the connection, the connection does not use SQL Browser or port 1434.  SQL Browser is used to convert a named instance to a port number.

    Monday, June 25, 2012 1:58 PM
  • 1) Are you able to login on SQL server locally by belwo options ?

    • DRServer\Instance
    • DRServer\Instance,49152

    2) Check & Try

    • Tracert <IP Address> 
    • Telnet <IP Address> 49152   -- from lcoal box
    • Telnet <IP Address> 49152   -- from remote server PROD box

    To see conenctivity of server from PROD to DR.  Nomally PROD & DR is located on diffrent geogrophical locations. You may be facing a network connectivity issue between both servers.

    -Rohit


    Monday, June 25, 2012 4:40 PM
  • The correct way to specify the server is "DRServer,49152" or "x.x.x.x,49152".  My guess is the name "DRServer" is not resolving to the correct IP adress.  Please try the IP address directly.

    Can you go to a command prompt and run "telnet x.x.x.x 49152"  What do you get?  Does it error?  Until telnet works, you will never be able to connect to it via SSMS.

    PS.  If you specify the port number in the connection, the connection does not use SQL Browser or port 1434.  SQL Browser is used to convert a named instance to a port number.

    I actually read about it that if I specify the port number in the connection, then SQL Browser is not required to be running. I will all the options suggested in the forum and get back.

    Don

    Monday, June 25, 2012 7:08 PM
  • Yes I can login locally using DRServer\Instance (I have restored databases, created logins etc. at both PROD and DR instances) but I did not try to login locally using DRServer\Instance,49152

    I will try Telnet later today and update what happens. Yes the PROD and DR are located at different geographical locations but I am able to connect from PROD to DR on the Default instance. So the issue could be most likely firewall but I wanted to make sure the TCP/IP settings are correct for binding the named instance to a IP and a static port.

    Can anyone confirm the settings (as shown in the image) are correct ? I am not sure if I should leave the "IP All" section blank ?


    Don

    Monday, June 25, 2012 7:16 PM
  • "Yes the PROD and DR are located at different geographical locations but I am able to connect from PROD to DR on the Default instance."

    This means you are having 2 instance on DR server, default & named instance and you able to connect default instance from PROD but named instance not connecting.

    As you are using static port 49152, you can try cleaning all entires from "TCP Dynamic Ports", make it blank & enter your static IP 49152 in "TCP Ports" on all IP1, Ip2, Ip3,IP4,IPAll. Click ok & try to connect.

    Monday, June 25, 2012 8:43 PM
  • Hi Don,

    Please follow below steps to configure your named instance to listen on specific port

    For Standalone instance of the SQL server  change the dynamic port of the named instance to the static port by using SQL server configuration manager in SQL 2005/2008. In order to change the port we need to go to the SQL server configuration manager.

    Start->Program Files-> Microsoft sql server 2005->Configuration Tools-> SQL server configuration manager.

    In configuration manager console, on the left hand panel, expand the SQL server  Network Configuration. Click on the”Protocols for <Instance_Name>”. On the right hand panel we need to right click and go to properties on the TCP/IP.

    Click on the IP Addresses tab on the top and keep the “Dynamic ports” row BLANK and write the desired port no. on which you want the named instance to listen on “TCP ports” row in the “IP ALL “section.

    After performing these activities we need to restart the SQL server services for the changes to take affects. After restarting the service you can confirm that whether changes has taken affect by checking the following registry key

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name> \MSSQLServer\SuperSocketNetLib\Tcp

    This key should contain the port no. defined by you.

    After you have configured SQL Server to listen on a specific port, there are three ways to connect to a specific port with a client application:

    • Run the SQL Server Browser service on the server to connect to the Database Engine instance by name.

    • Create an alias on the client, specifying the port number.

    • Program the client to connect using a custom connection string.

    Hope this may help.


    Regards, Vishal Srivastava

    Monday, June 25, 2012 9:01 PM
  • Will test all the configuration during change window.

    Also, If I am going to configure SQL to listen on multiple ports, am I required to create endpoints on additional static port ? or its not required.

    http://msdn.microsoft.com/en-us/library/ms189310


    Don

    Tuesday, June 26, 2012 2:26 AM
  • I have similar issue. I have Windows Server 2008 R2 64bit and installed SQL Server 2008 R2 default instance and named instance. I have given a static port of the named instance.  This server is behind the network firewall, so our security team opened the named instance port and I am able to open telnet session.

    I have to connect this named instance from two different serves.

    First one is our monitoring server which already has access to the default instance (port 1433 is already enabled for this monitoring server). From this one I am able to connect both default instance and named instance.

    Second server is an application server, which I have to connect only to the named instance. While connecting to SERVERNAME\INSTANCENAME I am getting the error "A network-related or instance-specific error occurred while establishing a connection to SQL Server". But if I use the port number (SERVERNAME\INSTANCENAME,PORT) in my SSMS then I am able to connect .

    Is port 1433 also needed to be opened for connection to a specific named instance?

    Could someone please help to resolve my issue?

    Thursday, June 28, 2012 1:54 PM
  • Well, make changes in firewall (add UDP port 1434) and let us know how was it going.

    It did turn out to be firewall issue. The named instances were configured to listen an IP2 and when I made a connection, it was using IP1 for out going but the firewall was opened only for connections between IP2 (PROD) - IP2 (DR). We had a Network person monitoring the activity which showed the connection was going from IP1 (PROD) to IP2 (DR). Once the firewall was opened, I was able to make connections.

    Don

    Saturday, June 30, 2012 9:02 PM
  • I have similar issue. I have Windows Server 2008 R2 64bit and installed SQL Server 2008 R2 default instance and named instance. I have given a static port of the named instance.  This server is behind the network firewall, so our security team opened the named instance port and I am able to open telnet session.

    I have to connect this named instance from two different serves.

    First one is our monitoring server which already has access to the default instance (port 1433 is already enabled for this monitoring server). From this one I am able to connect both default instance and named instance.

    Second server is an application server, which I have to connect only to the named instance. While connecting to SERVERNAME\INSTANCENAME I am getting the error "A network-related or instance-specific error occurred while establishing a connection to SQL Server". But if I use the port number (SERVERNAME\INSTANCENAME,PORT) in my SSMS then I am able to connect .

    Is port 1433 also needed to be opened for connection to a specific named instance?

    Could someone please help to resolve my issue?

    Finally my network team opened 1434 port and that did fix the issue.

    Thank you....

    Tuesday, July 24, 2012 4:23 PM