Cannot connect to Named Instance remotely

j-man

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?

NotHackingYou

Is the SQL Server browser instance turned on? Which port is B bound to? Edit to add - are you doing ServerName\InstanceName?

j-man

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?

j-man

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.

NotHackingYou

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.

knownhero

j-man wrote: »

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

j-man

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!

j-man

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.

j-man

Installed the SQL-core\ALTERNATE instance on the server running Server Core and it connected right away.

Consider this issue resolved. YAY