locked
Remote Connectivity to SQL Server 2012 Enterprise Edition Named Instance RRS feed

  • Question

  • We recently started building out sql server 2012 sandboxes with named instances and we're having issues connecting remotely.  We've been using named instances in 2005 and 2008.

    • Made sure tcp/ip is enabled in configuration manager
    • Made sure firewall inbound rules have ports opened for the sql instances
    • Checked box within the servers properties to allow remote connectivity
    • Enabled xp_cmdshell through advanced options
    • Made sure browser service is started

    Operating system is Windows Server 2008, and we are now building out Windows Server 2012 for an upcoming project.  I'm at a loss as to what could be causing this issue, please help.


    • Edited by Carolyn007 Thursday, May 30, 2013 9:03 PM
    Thursday, May 30, 2013 8:59 PM

Answers

  • Try connecting directly to the port the named instance is listening on, both locally and remotely.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Fanny Liu Monday, June 3, 2013 9:03 AM
    • Marked as answer by Fanny Liu Friday, June 7, 2013 10:01 AM
    Friday, May 31, 2013 3:55 PM

All replies

  • This

    • Enabled xp_cmdshell through advanced options

    is not necessary or recommended.  Try connecting directly to the port the named instance is listening on. You can see this in the SQL logs even if it's configured for dynamic ports.

    To connect to a named instance without a port number, SQL Browser service must be running and firewall configured for it.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, May 30, 2013 9:26 PM
  • Due to seperation of duties at my organization, the DBA's don't have login access to the sql servers.  We use xp_cmdshell regularly.  I already enabled and set up the ports in the firewall inbound rules, we are unable to connect even after taking the firewall down to test.

    Thursday, May 30, 2013 9:33 PM
  • Hi,

    Have you tried connecting to FQDN\Instance instead of hostname\instance?

    Like server1.acme.com\instanceX instead of server1\instanceX?


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, May 31, 2013 2:36 PM
  • Try connecting directly to the port the named instance is listening on, both locally and remotely.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Fanny Liu Monday, June 3, 2013 9:03 AM
    • Marked as answer by Fanny Liu Friday, June 7, 2013 10:01 AM
    Friday, May 31, 2013 3:55 PM
  • First determine the IP and the port SQL Server named instance is listening on. IpConfig or SQL Config Manager for IP and you can use this script to determine port:

    use master
    
    DECLARE @InstName VARCHAR(16)
    DECLARE @RegLoc VARCHAR(100)
    
    SELECT @InstName = @@SERVICENAME
    
    IF @InstName = 'MSSQLSERVER'
      BEGIN
        SET @RegLoc='Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\'
      END
     ELSE
      BEGIN
       SET @RegLoc='Software\Microsoft\Microsoft SQL Server\' + @InstName + '\MSSQLServer\SuperSocketNetLib\Tcp\'
      END
    
    EXEC [master].[dbo].[xp_regread] 'HKEY_LOCAL_MACHINE', @RegLoc, 'tcpPort'
    

    or as said in previous posts you can take a look in SQL Server error log or Configuration Manager.

    Then I would try to telnet to both FQDN + port and IP + port. Also, you can try to ping the server from client but this does not have to succedd in order to establish database connectivity.

    If previous test are successfull then try connecting from SSMS specifying IP + port, NETBIOS + port and FQDN + port. If this is all successfull then your SQL Server Browser is not working or you are having some kind of problems with it. Take a look at event logs on SQL server computer.

    If connecting through IP is successfull then report an issue with your networking department becacuse client cannot resolve netbios and/or sql server dns name. Also, check if TCP/IP is enabled on your client computer. Although you could use named pipes but I doubt this is the case.

    Regards,

    Dean


    DS

    Saturday, June 1, 2013 9:32 AM