locked
SQL connectivity issue with named instance RRS feed

  • Question

  • Hi All,

    We did a SQL 2012 DEV edition in a new server. Locally I am able to connect  it using servername\instance and with just servername which is weird. From our UAT instances we can only connect using servername  and when we try to establish connection using servername\instance name it throws network error. I find it very strange since we have not faced such issues in the past. The new server is listening on port 1433. ANy idea why this is happening?

    Thanks,

    Preetha

    Tuesday, March 27, 2018 1:37 AM

Answers

  • Hi,

    Since the named instance is listening on 1433(default port), you can always connect to the machine using the hostname because its not listening to SQL Browser.

    Now the question i have is

    • Is your browser service up and running ? i guess yes since you can connect with instance name from Local
    • Are the 2 machines (local and UAT server) under same domain ?
    • Check the firewall rules about incoming firewall blocks on UDP port 1434

    When a client wants to find out how to connect to a named instance, it sends a message via the UDP protocol to the computer where the named instance is hosted. It sends specifically to port 1434, asking for the named instance

    So the bottom line its not an issue but could be firewall differences between your UAT and Local.

    Also please remember that its not a best practice to configure the default port for a named instance.


    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Wednesday, March 28, 2018 12:14 AM
  • Hi Preetha7,

    SQL server default instance listens to port number: 1433 not the named instances.

    Names instances listens to port number 1434 by default.

    you can configure to a different port if you would like to.

    below is the link to change the port number:

    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port

    finally you need to make sure the firewall is opened for this port.

    good luck

    kumar

    Wednesday, March 28, 2018 12:06 AM

All replies

  • Hi,

    How many instances of SQL server(developer edition) are installed on the new server ?

    Do you have a default instance installed ?

    If there is a default instance (server\MSSQLSERVER),  then it can be listened with just mentioning the server name.

    Please let us know what is the instance name that you uses if that ok. Just wanted to make sure its not 'MSSQLSERVER'


    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Tuesday, March 27, 2018 3:16 AM
  • Can you post the whole error message ?  Are you sure that your instance is listening to 1433? have you checked it in the ERROR.LOG?

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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, March 27, 2018 4:55 AM
  • Hi,

    I have only one SQl instance installed and it is a named instance. I did not install default instance. I have installed a instance called DEV_TEST. However, I can connect to this server by just giving the server and it is not connecting when I give server\DEV_TEST which is very strange from a UAT server. From my local machine I can connect to this instance either by server\DEV_TEST  or just server which is also incorrect. I am not sure how this is happening.

    Tuesday, March 27, 2018 11:05 PM
  • Yeah I did check, it is listening to 1433 since that is the only port we have opened to connect to other boxes. The error message is nothing but the one below:

    Tuesday, March 27, 2018 11:06 PM
  • Hi Preetha7,

    SQL server default instance listens to port number: 1433 not the named instances.

    Names instances listens to port number 1434 by default.

    you can configure to a different port if you would like to.

    below is the link to change the port number:

    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port

    finally you need to make sure the firewall is opened for this port.

    good luck

    kumar

    Wednesday, March 28, 2018 12:06 AM
  • Hi,

    Since the named instance is listening on 1433(default port), you can always connect to the machine using the hostname because its not listening to SQL Browser.

    Now the question i have is

    • Is your browser service up and running ? i guess yes since you can connect with instance name from Local
    • Are the 2 machines (local and UAT server) under same domain ?
    • Check the firewall rules about incoming firewall blocks on UDP port 1434

    When a client wants to find out how to connect to a named instance, it sends a message via the UDP protocol to the computer where the named instance is hosted. It sends specifically to port 1434, asking for the named instance

    So the bottom line its not an issue but could be firewall differences between your UAT and Local.

    Also please remember that its not a best practice to configure the default port for a named instance.


    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Wednesday, March 28, 2018 12:14 AM