Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Help : What’s my SQL Server Name?

Posted by blakhani on June 19, 2011


“What’s in a name? That which we call a rose by any other name would smell as sweet.” – William Shakespeare

Well, SQL Server connectivity did not know about Shakespeare Hot smileWhile working on the forum, one of the most common error posted is below:

TITLE: Connect to Server
——————————
Cannot connect to <Shakespeare>.
——————————
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)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
——————————
BUTTONS:
OK
——————————

Let’s look closely at the error message.
1. The server was not found or was not accessible.
2. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
3. error: 26 – Error Locating Server/Instance Specified.

All of the above are shouting out loud that “Damn! you have given wrong name to connect”. If you have given right name then check if SQL is up and running and then whole troubleshooting starts. The purpose of this post is to help the beginners who don’t care about troubleshooting because they are Visual Studio developers and they are connecting to local SQL Express most of the time.

Okay my dear developers, don’t worry… take a deep breath and start…

To get the SQL Server Name, the first thing you need to do is to find the instance name. On a single machine you can install 16 instances of SQL Servers. Each would have some name. To find the instance name of a SQL Server instance, please follow the steps below:

  • Open SQL Server Configuration Manager (SSCM) from Start – > All Programs -> Microsoft SQL Server 2005/2008/2008 R2 -> Configuration Tools.
  • In the SSCM, click SQL Server Services under SQL Server Configuration Manager (Local), you could find list like SQL Server (InstanceName),
  • The InstanceName is what we need to find, instance name. If you install multiple instances on the server, you will see more than one SQL Server (InstanceName) listed.

image

In above picture you can see two “SQL Server” in “Service Type” column. It means I have two instances of SQL Server. For default instance you would see Name as SQL Server (MSSQLServer). It means on my machine I have two “named” instances of SQL Server. Keep in mind that you can have only only default instance on a machine.

Okay great! Now you know that your machine has default instance or named instance. And if it’s named then you know its name. Correct? Now lets quickly see what name you should use to connect. Before that you need to find hostname or computer name. Go to Start > Run > cmd This would open command prompt. Type hostname and you should see your machine name as below.

image

Default Instance Named Instance Comments
BLAKHANI BLAKHANI\SQLEXPRESS
BLAKHANI\SQL2K8R2
this would work from remote machine also.
(LOCAL) (LOCAL)\SQLEXPRESS
(LOCAL)\SQL2K8R2
local only
. .\SQLEXPRESS
.\SQL2K8R2
local only
LOCALHOST LOCALHOST\SQLEXPRESS
LOCALHOST\SQL2K8R2
local only
BLAKHANI.MYDOMAIN.COM BLAKHANI.MYDOMAIN.COM\SQLEXPRESS
BLAKHANI.MYDOMAIN.COM\SQL2K8R2
Remote connections should be allowed as mentioned in http://support.microsoft.com/kb/914277 (

How to configure SQL Server 2005 to allow remote connections)

TCP:221.222.223.224,1433 TCP:221.222.223.224,9999 IP and Port can be found by reading ERRORLOG of SQL Server.

 

If you don’t see configuration manager then you can also get the instance name from services applet. To do this, go to Start > Run > Services.msc
Here is what i see on my screen

image

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

About these ads

31 Responses to “Help : What’s my SQL Server Name?”

  1. DEsipm said

    there is the photo of my old and new Database when i start working on it.

    https://skydrive.live.com/redir.aspx?cid=cdc59b233f2acd53&resid=CDC59B233F2ACD53!179

    plz have a look and tell me what do i lack…

    best regards

  2. [...] Connect to SQL Server using SQL Server Management Studio by providing correct name. To find the name to connect, refer my earlier post [...]

  3. [...] Connect to SQL Server using SQL Server Management Studio by providing correct name. To find the name to connect, refer my earlier post [...]

  4. Alvaro Trettin said

    I gotta bookmark this website it seems very helpful, invaluable

  5. [...] I am going with default instance. For more details about the impact of that, please refer my earlier blog http://sqlserver-help.com/2011/06/19/help-whats-my-sql-server-name/ [...]

  6. marketing said

    So Great! I need some infos in this submit for my rapport de stage. Can i have your contact please? I need your permission to quote it :D. Anyway, Thats great job. Maintain going.,

  7. You literally saved my day dude!

  8. Eshan said

    After going through all these steps i am getting the same error

  9. [...] Net Stop MSSQLServer (for default instance) / Net Step MSSQL$<InstanceName> If you want to know instance name, refer my earlier blog [...]

  10. pooja gupta said

    actuly for connceting sql server i dont know the “sql server name” can u help it out.

  11. HItesh Oza said

    Dear All,

    Open your visual studio… Create New Project for Windows or New Website…. then right click on your project in solution explorer and add > add Item > database (with .mdf)….. then database create and display in your solution explorer… then check the properties of your created database from Server Explorer… you can see the the CONNECTION STRING in properties… can get your SQL server name….

    That’s it final solution in short way….

    HITESH OZA
    hiteshdoza@gmail.com

  12. bharath said

    when i open sq;service in configuration manager its empty, pls help me how to connect now

  13. Akash Aggarwal said

    Are you online by any chance right now? I’m in deep shit and need your by tonight help connecting.. Tried everything, still can’t connect :( :( :(

  14. Tam said

    Thanks a lot

  15. thara said

    i did what you have post on your blog. but if there is same instance names, what should i do?

  16. Mosses Jabakumar said

    I have checked SQL instances are running and I have correct Server name, username and password but still I am not able to open SQL SERVER R2 management studio. Can any1 please help me out with this ..

  17. Gay said

    Awesome!

  18. Prasun said

    Thanx It Helped Me too much

  19. meme said

    hi, i want cmd but after i type hostname whats the next… pls. help me

  20. mgraves said

    cheers dude, I was missing off LOCALHOST when trying to connect.

  21. […] Net Stop MSSQLServer (for default       instance) / Net Step MSSQL$ If you want to know       instance name, refer my earlier blog […]

  22. ac said

    Thank you! Putting in the instance from the Config Manager did the trick for me.

  23. rony said

    Balmukund Lakhani , Thank you so very much :)

  24. Phathi said

    I have SQL Server Management 2012 installed and I followed those steps to find the instance name by SQL Configure manager and I have instance names. What do I do

  25. Phathi said

    I have SQL Server Management 2012 installed and I followed those steps to find the instance name by SQL Configure manager and I have no instance names. What do I do

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 866 other followers

%d bloggers like this: