Link to home
Start Free TrialLog in
Avatar of Jim Klocksin
Jim KlocksinFlag for United States of America

asked on

SQL Server named instance will not allow remote connection via RemoteApp

I had this question after viewing cannot consistently connect to SQL Server named instance.

My situation is slightly different but similar.  I have a default instance (production data) and a named instance (test data) on a SQL Server 2008 R2 installation.  Initially, I was not able to connect locally across my internal network, until I added a firewall rule to open up UDP port 1434 on my SonicWall firewall device.  Now I can access both instances internally without issue.  I have remote users that are using RemoteApp to run my software system off my network and this works perfectly for the default instance.  Whey anyone attempts to access my "test environment" (databases on the named instance), via RemoteApp, they can NOT connect to the databases in the named instance.  This is presenting a problem for me in that my users need to either run demos or perform actual testing using the databases deployed on the named instance.  Does anyone have any suggestions as to what's causing this situation?
Avatar of Zahid Ahamed
Zahid Ahamed
Flag of United States of America image

Find out the port number for the  named instance and then go to firewall--advance settings--new inbound rule and there mention the port named instance port number. Hope that will work.
Avatar of Jim Klocksin

ASKER

How do I (or where can I) find the port number for the named instance?
In the sql server configuration manager. Right click the instance name properties you will see there is a tab IP address. And scroll down you will see the port number
Avatar of Vitor Montalvão
Be aware that by default the port for named instance is dynamic, meaning that next time the SQL Server service restarts it will have another port number and then the firewall rule won't work anymore and you need to update it.
So you have two options:
1 - Provide a static port number for the named instance by following this article.
2 - Do not open a port in the Firewall but allow the SQL Server program (sqlservr.exe) instead.
Vitor, your suggestions made the most sense to me since I had already read that the named instances provide for dynamic port numbers!  I've tried your first option which did NOT work for me (providing a static port address for my named instance).  In fact, it made my situation worse from the standpoint that I "temporarily" lost connectivity within my own local network.  So I set everything back to dynamic (which resolved my local network problem) and added an "inbound rule" in the Windows firewall for the "sqlservr.exe" program.  Unfortunately, this didn't change my situation.  Do I need to make some type of similar rule in my Sonicwall firewall?  I'm not exactly sure how to do this anyway, but if you believe that it's necessary, I will try to figure out how to do this.  So, bottom line for me is that my server is being protected by the "built-in" Windows Firewall and a hardware Sonicwall firewall.  Thanks!
I'm still trying to get this to work, without much success.  I've changed back to a static port for my named instance.  My local network connects to the named instance with no issues.  I can also access the named instance databases when I run the software directly from the server.  Problem I'm still having is attempting to connect over a Remote Connection using the RemoteApp.  The RemoteApp works perfectly when connecting to the default instance, does NOT work at all when connecting to the named instance.  I've searched thru the Internet for anything related....found lots of articles, but nothing that has helped my situation.  So, again, I'm looking for any help that anyone can provide.  Suggestions, please?
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I've set the port number on my named instance to 1435.  This allows me to access my named instance on my local network (for development and testing purposes).  I've tried setting the port to other values, but everything I've tried caused my local network to stop working!  My problem is that I set up the named instance to be a "testing environment", for all the same databases I have in the default instance, but I need it to be accessible via Remote Desktop using RemoteApp.  The RemoteApp "piece" is working correctly.  Accessing the default instance via RemoteApp works with no issues.  Accessing the named instance via RemoteApp does NOT see the named instance and simply returns an error (from within my software) that makes it obvious that the application can NOT connect to the named instance (while it connects to my default instance without any problem!?).  Obviously, I'm wasting a lot of my time on this and it just seems that there has to be some simple solution to this, but I have no idea what that is.  Help!!!!????
I don't know your application but it might be owner of the issue. Perhaps that application can only connect to default instances? I mean, it's only prepared for the port number 1433?
Vitor, if that were the case, then why does my application work perfectly on my local network using the "named" instance?  What I was referring to in my last comment was that I had tried a number of different port numbers (other than 1433) and the only one that worked on my local network was 1435....I have no idea why that is, but it is!
What do you mean with local network? I always thought you couldn't connect remotely but locally only. I didn't assume that locally was local network but locally inside the same server where the SQL Server is.
This scenario will put the issue somewhere in the network (firewall, gateway, ...).
Yes, by local network, what I mean is that I can connect from any of my workstations and the program runs fine (using both the default instance as well as the named instance).  I did have to add a "firewall rule" to my Sonicwall device (for UDP port 1434) to get the application to work from all of my workstations.  I know for a fact that the problem is NOT with Windows firewall (which is running on my server) since I turned that off and I still had the problem.  The problem ONLY occurs when running via RemoteApp (RDS).  Again, RemoteApp works fine when the application is attempting to connect to any database in the default instance.  RemoteApp does NOT work when the same application is attempting to connect to the named instance (which I set up in order to have a "test / demo" version that could be used by my clients).  The default instance is actually a live production environment that I'm hosting on my network for my clients.  Since the "test" (named instance) works over my network, I can update and test the application with no problem (which is good, obviously).  I do need to give me clients the capability to test and/or demo the application using the named instance which is essentially the crux of my issue.  I tend to agree with you that the problem must be in the firewall (specifically, I think the Sonicwall device), but my knowledge of the Sonicwall device is limited and, frankly, I'm not even totally sure that the problem is the Sonicwall (I just think that it's the only thing left that could be the culprit, so to speak!).
Vitor,  Thanks for all the attention you gave to my question/dilemma!  I finally figured out what the problem was and, as you said in your answer I checked as best solution, it was something small that I overlooked and, frankly, just never thought about.  I have close to 300 user logins that connect to this application and, fortunately, I have them all in a "group" (in Active Directory).  The problem was simply that I never added the group as a login to the named instance!  As soon as I did, everything's working fine.  Thanks again for all your time...