February 26, 2008 at 1:43 am
Hi once again. I am unable to connect remotely to a second instance using the Management Studio on my local machine.
I can connect to the default instance OK.
MY-SERVER (connects OK)
MY-SERVER\Instance (fails to connect. Error 26 - Error locating Server/Instance Specified)
I am attempting to login using SQL Server Authentication (UN: sa)
I can connect to the second instance from the SQL Server.
The 'sa' credentials work fine for the default instance. Allow Remote Connections to this server IS checked?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 26, 2008 at 2:21 am
Is the SQLBrowser service running?
February 26, 2008 at 2:47 am
Thanks Ian, that did the trick.
I used SS Surface Area Configuration. I am sure there is another SQL tool for viewing the services, just can't recall what it is (maybe not!!).
Should this be set to 'Automatic'? It was set to manual by default?
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 26, 2008 at 2:50 am
Ian is right, the most frequent reason for the above error is that the SQL Browser Service is not running when you try to connect to a named instance with dynamic ports.
You can read more on this on http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx
One way you can enable and start the SQL Browser service is to start the SQL Server 2005 Surface Area Cnfiguration tool, then select the Surface Area Configuration for Services and Connection, ...
Regards,
Andras
February 26, 2008 at 2:53 am
Oops, I should learn to type faster 🙂
Anyway, setting it to Autmatic will ensure that it is started every time.
Regards,
Andras
February 26, 2008 at 2:58 am
Many thanks for the replies.
I am looking to walk through some of the tutorials within Visual Studio. I want to install the AdventureWorks OLTP + DW samples on my second instance.
Just to note. What is the recommended 'Best Practice' (if indeed there is one) regards this service, should I allow it to remain running? Just thinking about security.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 26, 2008 at 3:59 am
Philip Horan (2/26/2008)
Many thanks for the replies.I am looking to walk through some of the tutorials within Visual Studio. I want to install the AdventureWorks OLTP + DW samples on my second instance.
Just to note. What is the recommended 'Best Practice' (if indeed there is one) regards this service, should I allow it to remain running? Just thinking about security.
Many Thanks,
Phil.
Yes, you should let the SQL Server Browser run, if you want users and applications accessing it outside the local box. Then it all comes down to how you configure users' permissions so that proper security is maintained.
The tool to use when managing SQL services is SQL Server Configuration Manager.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 26, 2008 at 6:38 am
Cheers guys, your time is very much appreciated.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 27, 2008 at 9:52 am
I have seen people reporting this few many times. With SQL server 2005, Browser services needs to be started in machines that have more than a instance. ITs the browser service which lets the clinet know on which port thw other instance of the sql server is running and makes it for available connection.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 27, 2008 at 10:10 am
Sugesh Kumar (2/27/2008)
I have seen people reporting this few many times. With SQL server 2005, Browser services needs to be started in machines that have more than a instance. ITs the browser service which lets the clinet know on which port thw other instance of the sql server is running and makes it for available connection.
... if you don't want to have to specify port #'s or create aliases. You don't NEED to run Browser if you already "know how" to connect to a specific instance.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 27, 2008 at 10:18 am
Browser services needs to run when the SQL servers run on the default port and when you have specified a port number for the server you can use that for connecting to the server instance.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 28, 2008 at 1:43 am
Sugesh Kumar (2/27/2008)
Browser services needs to run when the SQL servers run on the default port and when you have specified a port number for the server you can use that for connecting to the server instance.
Hi Sugesh ,
probably accidentally you missed the word not out from your post 😀
If you run on the default port, or fix the ports of your named instances or change and specify this port then you do not need Browser services.
Regards,
Andras
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply