+ Reply to Thread
Results 1 to 9 of 9
  1. Senior Member
    Join Date
    Feb 2011
    Location
    Wisconsin
    Posts
    143

    Certifications
    A+
    #1

    Default Cannot connect to Named Instance remotely

    I'm currently working my way through the Microsoft Press 70-462 book and am pulling my hair out on Chapter 2 Lesson 2 Managing SQL Instances. I know there are errors in the book so up until now, I've been successful in figuring out what the problem was but now I'm stumped.

    I've installed the ALTERNATE named instance on SQL-B but I cannot connect to that instance from SQL-A from SSMS. I've reinstalled the instance and made sure TCP/IP is enabled. I've triple checked the Group Policy for typos.

    I can connect to the default instance on SQL-B.

    What am I missing?
    Reply With Quote Quote  

  2. SS -->
  3. Achieve excellence daily
    Join Date
    May 2012
    Location
    Washington State
    Posts
    1,341

    Certifications
    CISSP
    #2
    Is the SQL Server browser instance turned on? Which port is B bound to? Edit to add - are you doing ServerName\InstanceName?
    Last edited by NotHackingYou; 05-30-2013 at 10:09 PM.
    When you go the extra mile, there's no traffic.
    Reply With Quote Quote  

  4. Senior Member
    Join Date
    Feb 2011
    Location
    Wisconsin
    Posts
    143

    Certifications
    A+
    #3
    I can connect to SQL-B Database Engine and Integration Services fine even if the SQL Browser service is running on the server or not. Not sure what you mean by the port. It was installed using all the defaults.

    I'm connecting from SSMS on SQL-A by trying both SQL-B\ALTERNATE and the IP address of the server. No dice.

    It's really bugging me because as I mentioned earlier, I can connect to both the Database Engine and Integration Service on that SQL server.

    These are all VirtualBox servers running on my computer.

    Can you turn off the firewall on a server if there are GP firewall rules being applied to it?
    Last edited by j-man; 05-30-2013 at 10:47 PM.
    Reply With Quote Quote  

  5. Senior Member
    Join Date
    Feb 2011
    Location
    Wisconsin
    Posts
    143

    Certifications
    A+
    #4
    It was the Firewall. I turned it off on that server and I could connect to the named instance.

    Do you know what needs to be configured in the Group Policy Object for those servers to allow the connection? I thought it was allowing access for the following program: c:\Program Files\Microsoft SQL Server\MSSQL11.<instance name here>\MSSQL\Binn\sqlservr.exe ? That is the path to the instance when it's installed. Other than that one thing, the inbound rule is exactly the same as the IS rule I set up, and the Database Engine remote access rule. Those both work. Just not the named instance bit.

    I really hate this book. While I'm so tired of looking up things online in order to make the exercises work.... I'm learning a lot. Infuriating but fun.
    Reply With Quote Quote  

  6. Achieve excellence daily
    Join Date
    May 2012
    Location
    Washington State
    Posts
    1,341

    Certifications
    CISSP
    #5
    You needed to allow access in on TCP port 1433. You can turn your firewall back on and just put in a custom rule for that port, inbound.
    When you go the extra mile, there's no traffic.
    Reply With Quote Quote  

  7. Senior Member knownhero's Avatar
    Join Date
    Jul 2008
    Location
    UK
    Posts
    434

    Certifications
    MCSE: SharePoint 2013, Productivity
    #6
    Quote Originally Posted by j-man View Post
    It was the Firewall. I turned it off on that server and I could connect to the named instance.

    Do you know what needs to be configured in the Group Policy Object for those servers to allow the connection? I thought it was allowing access for the following program: c:\Program Files\Microsoft SQL Server\MSSQL11.<instance name here>\MSSQL\Binn\sqlservr.exe ? That is the path to the instance when it's installed. Other than that one thing, the inbound rule is exactly the same as the IS rule I set up, and the Database Engine remote access rule. Those both work. Just not the named instance bit.

    I really hate this book. While I'm so tired of looking up things online in order to make the exercises work.... I'm learning a lot. Infuriating but fun.

    Just seen this post and was going to say turn off your firewall. Good to see you figured it out, but like Carl said you should really put in the custom port in the firewall to allow this. Good video to show you what needs to be done here
    70-410 [x] 70-411 [x] 70-462[x] 70-331[x] 70-332[x]
    MCSE - SharePoint 2013

    Road map 2017: JavaScript and modern web development

    Reply With Quote Quote  

  8. Senior Member
    Join Date
    Feb 2011
    Location
    Wisconsin
    Posts
    143

    Certifications
    A+
    #7
    I ended up opening that port via GPO and all is well. I hate it when the book has step by step instructions to follow and then to find out they are wrong.

    Here's a nice link: Configure a Windows Firewall for Database Engine Access

    Thanks much guys!
    Reply With Quote Quote  

  9. Senior Member
    Join Date
    Feb 2011
    Location
    Wisconsin
    Posts
    143

    Certifications
    A+
    #8
    The pat on the back was short lived. I forgot to turn on the firewall on SQL-B. When I enabled the firewall, back to square one, no connection. A GPO for TCP 1433 was created with no luck connecting. I then created a GPO for UDP 1434 (Browser) and... holy cow! When I opened up SSMS on SQL-A and chose browse then network servers, SQL-B and SQL\ALTERNATE appeared. I gleefully clicked on SQL-B\ALTERNATE and <insert sad trombone here> no dice. Error was SQL server was taking too long to respond. Good start. At least the instances were showing up now. Being that it seems that named instances (not the default instance) all like to use dynamic ports, I re-created the GPO allowing the program c:\Program Files\Microsoft SQL Server\MSSQL11.ALTERNATE\MSSQL\Binn\sqlservr.exe access.

    Restarted SQL-B, opened SSMS on SQL-A, browsed for servers, Network Servers, SQL-B and SQL-B\ALTERNATE appeared.... clicked on SQL-B\ALTERNATE and lo and behold, it connected!

    It seems that SQL Server Browser will take care of the dynamic port mapping and the specific instance installation in the program based GPO took care of the second half of the puzzle for me.

    What a PITA this has been. But it's been very educational. Hopefully this will save me from putzing around with the Firewall on SQL-Core (the SQL Server installed on Server 2008 R2 Core).

    Anyhow, thanks again for the help gents. I couldn't have done it without y'all.

    Onwards and upwards.
    Reply With Quote Quote  

  10. Senior Member
    Join Date
    Feb 2011
    Location
    Wisconsin
    Posts
    143

    Certifications
    A+
    #9
    Installed the SQL-core\ALTERNATE instance on the server running Server Core and it connected right away.

    Consider this issue resolved. YAY
    Reply With Quote Quote  

+ Reply to Thread

Social Networking & Bookmarks