question

DaniSQL avatar image
DaniSQL asked

New Server Registration Failed(named instance)

I have a server that has two SQL Server instances(sql server 2005, sp2, enterprise ed) and I was able to connect to the default server from my local instance(SQL Server 2005 sp3, standard ed). but when i couldnt connect the named instance as ComuterName\namedinstance to my local instance. It shows me the following error:

TITLE: New Server Registration
------------------------------
Testing the registered server failed. Verify the server name, login credentials, and database, and then click Test again.
------------------------------
ADDITIONAL INFORMATION: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

I am using windows authentication. However, I created a new SQL login on the named instance and tried to register the server with sql server authentication and it doesnt work.

Please Help! Thanks.

server-instances
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

6 Answers

·
Matt Whitfield avatar image
Matt Whitfield answered

Is the sql server browser service running? It needs to be running in order to allow a lot of functionality to work, including named instance connections and dac connections.

3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DaniSQL avatar image DaniSQL commented ·
No! SQL server browser is not running. I thought of that at first but i rule out that because I am able to connect to the default instance. Is there any chance that I am unable to connect to the named instance but able to connect to the default and the problem might be not starting SQL SErver? Also do I need to restart sql server after i enable sql browser?
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
congrats on the 5k Matt.
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@DaniSQL - you've already figured it out, but the answer is no you shouldn't need to restart the sql server service... @Fatherjack - thank you very much sir!
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered

You need to make sure your discovery port is open on the server. Multiple instances take advantage of other ports. You either have to supply the port when connecting, or have the discovery port open so that the correct port can be found by passing the instance name.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

Check that SQL Browser service, Remote Connections etc are all running/enabled for the instance you want to connect to. Some services are instance specific so you can start/stop the instances independently.

Can you connect from the local server (the actual hardware where the instances are installed)? Its not recommended to install management tools on the production boxes but if you have then it might be useful in the case to test it.

If SQL Browser is running for the named instance then you should be able to use the SSMS connection dialog to Browse network to see the instance. Alternatively, if you are a programmer (or know someone who is) or know any PowerShell (or know someone who does) then you could try to connect/scan your network using SMO.

Its an old faithfull but have you 'turned it off and on again'?

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

umar avatar image
umar answered

CHECK SQL PORT AND AND IN CONNECTION STRING USE PORT AS

SERVERNAME,PORT

IF THAT DOES NOT HELP THEN USE

IP ADDRESS,PORT

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DaniSQL avatar image
DaniSQL answered

Thanks Matt! I just changed SQL Server browser status to running and Everything works fine!!

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cacio avatar image
Cacio answered
Hi guys, i meet the same issue that couldn't login the new registered server with sql server authentication. and my sql server browser is still running, but still face the error below: TITLE: Connect to Server ------------------------------ Cannot connect to Mirror. ------------------------------ ADDITIONAL INFORMATION: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.