“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 
While 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.
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.
| 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
Hope this would help someone in the world!
Cheers,
Balmukund Lakhani
Twitter @blakhani
Help: SQL Server 