March 27, 2006 at 1:36 pm
I have installed a named instance of SQL Server 2000 SP4 on a server that already has a default instance (also 2000 SP4). I am unable to connect remotely to the named instance unless I connect using <ipaddress>,<port>. I can connect while remote controlling the server.
I am able to connect to a named instance on another server with <servername>\<instance> . Thinking that maybe the port being used on the problem instance was the culprit, I modified the port number to match the instance that I can connect to. I also see in the log file that SQL Server is binding to the port number.
Builtin\administrators have been removed from both instances. Each instance is running under its own account which MS has told me should not be a problem. The other difference is that the instances have very different collations.
Any idea where to check next?
Thanks,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
March 27, 2006 at 2:00 pm
Can UDP 1434 get to the server? When you hit a server, this port allows the SQL browser to send back the port required for connection. Check for a server firewall that may block this.
March 27, 2006 at 2:14 pm
I had the network guy check and the port is not blocked internally. I didn't think it was since other named instances work.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
March 28, 2006 at 1:17 am
Couple of things to try
1. create a named pipe alias (using client network utility) specifying the server\instancename. This will probably solve issue, but means you are connecting over a named pipe not tcp\ip.
2. Check what logins you have used to start the sql and agent services. I know you should be able to use different logins but try using same login for both. Ensure these are defined to sql server as logins (especially if you have removed builtin and the login is in local admin group.
What tool are you using to connect currently and getting failure? SQL QA, EM, ODBC?
Derek
March 28, 2006 at 1:20 am
Sorry also noticed a comment in your last post which hinted that this server you are having issues with has a default instance and more than one named instance? How many named instances are running on the server you are having issues with?
March 28, 2006 at 5:54 am
You should also be able to use the Client Network Utility to create a SERVER\INSTANCE alias for TCP, specifying the port number.
March 28, 2006 at 7:47 am
If you client alias, be sure you specify a server port setting and not use dynamic ports.
I know you said the port was open, be sure it's open on the network AND the local server. The server firewall can be different from a network router/firewall.
Are you using Windows or SQL logins for the named instance? Are you sure it's valid? Someone could have changed something, so double check. Are you getting the "Login is not valid" message or something else?
March 28, 2006 at 10:45 pm
I spent 5 hours on the phone with MS support today. They could tell that UDP 1434 was being "filtered" but even with all the tools couldn't tell why. They requested that I reboot the server, which I just did. Now it is working properly, but I wonder if this is going to break again after a future reboot. The case is still open, so I'll post what I find out.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
March 31, 2006 at 2:27 pm
I finally have the answer! One thing I didn't mention in the previous post is that they also had me add the service accounts to the local admin group.
If the service account for the default instance does not belong to the local admin group, it doesn't enumerate the named instances. So, in addition to rights mentioned in article http://support.microsoft.com/Default.aspx?id=283811 , the service account for the default instance must also have read access to a key for the named instance http://support.microsoft.com/?id=888529 .
In this particular situation, I did not want the service accounts to be members of the local admin group. The instances belong to different departments, one of which has very confidential information. I am also restricting the accounts so that they do not have access to each others data and backup folders.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
April 2, 2006 at 8:00 pm
Ahaha, Steve this was the memory jog I needed re my post to your article on separate service accounts (best practices). If you use separate accts for each instance (best practice) and keep privileges tightly controlled you can hit some weird named instance snags such as this one.
April 3, 2006 at 9:01 am
Thanks for the followup, Kathi, and this is an interesting one. And a bug for MS to fix in SP5 and just grant those rights when you change the service account.
This should be fixed in ss2k5 since the SQL Server Browser handles this function, but I'm sure you'll hit other snags.
April 3, 2006 at 9:09 am
There are actually some other keys that the start up account needs full control that isn't documented anywhere.
On Windows 2003, if the service account is not a member of local admins, it needs full control on these keys (and similar keys for named instances) and their children to keep from getting errors when modifying some properties:
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply