October 9, 2008 at 8:37 am
Bottomline: WHY WEREN'T THE TWO REQUIRED ENTRIES AUTOMATICALLY ADDED TO MY WINDOWS FIREWALL EXCEPTIONS DURING THE NAMED INSTANCE INSTALLATION? WAS THERE AN OPTION I MISSED?
Details:
After two days of googling and testing it finally dawned on me that the instructions for remote connections to SQLExpress were pretty much the same for named instances. I guess because the default port isn't being used.
While testing out different authentication schemes, having members of my team attempting to connect to an instance on my machine, they would get the following errors:
1. sql server does not allow remote connections
2. SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified
3. An error has occured while establishing a connection to the server. When connecting to SQL Server 2005,this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.(provider:Named Pipes Provider,error:40-Could not open connection to SQL Server))
4. Server does not exist or access denied
I, on the otherhand, using one team member specifically, who had a default installation on his machine, could connect to his machine with no problem at all after adding him to my logins.
I did everything I could think of with the Surface Area Manager, granting TCP/IP or named pipes or both to remote access. I granted openrowset access too, since his was checked on default. Nothing worked.
Until I found a posting somewhere regarding SQLExpress, which I was inclined to skip over, because I am not using SQLExpress. Then I spotted the "backslash" in the name which by the way also exists in a named instance where you have the machine (server) name followed by a backslash then the name you specified when creating your named instance. 😉
It said to add two exceptions to the Windows Firewall then reboot. I tried it and it worked. However, my question is, WHY WEREN'T THESE TWO ENTRIES AUTOMATICALLY ADDED THESE DURING THE NAMED INSTANCE INSTALLATION?
sqlservr.exe and sqlbrowser.exe
Note: in your services you can find the path to these files. And you need to add the sqlserver.exe for each named instance. The exceptions tab on Windows Firewall has a browse button.
October 9, 2008 at 10:27 am
is there any questions?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 9, 2008 at 11:01 am
So you cant connect to the remote SQL server, and you did all the configuration that has been mentioned above 🙂
If i am understanding it right ???
October 9, 2008 at 3:23 pm
My question is at the top. I did solve my immediate connection problem. However, I wanted to know if anyone was able to install a named instance that allowed users to remote in, without specifically adding exceptions to the Windows Firewall?
I wasted a day and a half trouble shooting why they couldn't connect. And, regarding named instances, I didn't find any specific answers on Google or here. I only found references to SQLExpress.
I wanted to make a user-friendly posting for others as well as find out if there's even a better solution. that's all.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply