13

I'm running SQL Server 2012 Management Studio Express in windows 7, i am having issues connecting to the local db. i tried all the above mentioned solutions, didnt work. please help. thanks in advance.

Installation url is here.

error- 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 MSDN link.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Rakesh
  • 289
  • 1
  • 3
  • 8
  • What is in the connection string? The error is telling you that you most likely have an invalid data source element in there. – Preet Sangha Aug 28 '12 at 17:15

7 Answers7

22

Assuming you installed SQL Server and used the named instance SQLEXPRESS, your server name should be:

.\SQLEXPRESS

Or:

127.0.0.1\SQLEXPRESS

You can check the instance name (it may not be SQLEXPRESS) and that the server is running in the Start Menu at a path very similar to this (but it will vary by version):

Start > Programs > Microsoft SQL Server > Configuration Tools >
   SQL Server Configuration Manager

If it is currently stopped, you need to right-click and choose Start:

enter image description here

You should also right-click and ensure that Startup Mode is set to automatic (this is under Properties > Service).

The error message seems to imply that the server is trying to use named pipes. While locally it should be able to use shared memory, you should next ensure that the SQL Server Browser service is running. You can do that in:

Control Panel > Administrative Tools > Services

enter image description here

If it's not started, start it, and set its Start Mode to automatic. (As above, you can do both of these things from the right-click menu.)

If you're still not getting anywhere, it's possible you installed SQL Server 2012 without manually adding your Windows account to the group of administrators. If this is the case, hopefully you set it to use mixed mode and you can connect as sa. Otherwise you will need to use a tool like PSExec.exe to run SSMS as NT AUTHORITY\SYSTEM:

PsExec -s -i "C:\...path to ssms...\Ssms.exe"

Whether you can connect as sa or you need to connect using PSExec, the next steps are:

  1. make sure your Windows account is a login under Server > Security > Logins. If it's not there, add it by right-clicking Logins > New Login.
  2. make sure that login is a member of the sysadmin fixed server role. If it's not, make sure it is under the Server Roles tab.
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 1
    And If you want to connect localDB, just put `(LocalDb)\v11.0` in server name text box. – Moshi Jan 06 '17 at 19:29
11

You can connect SSMS to a LocalDB instance using named pipes.

1 - Get the address of a (localdb) instance by running the following command: "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SqlLocalDB.exe" info [InstanceName] (if its the default instance you're interested in, specify v11.0 as the [InstanceName] You can write it in CMD to text file with the command: 

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SqlLocalDB.exe" info v11.0 > C:\db_details.txt

2 - Copy the "Instance pipe name" value, e.g. np:\.\pipe\LOCALDB#1E0FF40B\tsql\query

3 - Paste the "Instance pipe name" value as the Server Name.

Dmitry Pavlov
  • 30,789
  • 8
  • 97
  • 121
  • Does not work. I still get the same error message when connecting with the named pipe. – ygoe Dec 18 '14 at 07:39
  • Ensure you enabled named pipes - https://www.blackbaud.com/files/support/infinityinstaller/content/installermaster/tkenablenamedpipesandtcpipconnections.htm – Dmitry Pavlov Dec 18 '14 at 13:01
  • Oh great. Now that I've enabled named pipes (nobody said I need to do that), `sqllocaldb info` returns an empty value for the named pipe instance. Seems it's gone now that it should do something. Still not functional. Think I'll just stay with SQL Express. Or PostgreSQL/MySQL if it should be really compact. – ygoe Dec 19 '14 at 09:17
6

Late to the party but check this link out I had the same issue

http://blogs.msdn.com/b/sqlexpress/archive/2011/07/12/introducing-localdb-a-better-sql-express.aspx

Also try (localdb)\V11.0 as the server name.

Note.., the instance name V11.0 is case sensitive Capital V then 11.0

Troy Bryant
  • 994
  • 8
  • 29
  • 60
4

After two days troubleshooting, finally, I got the result If you are using express version of SQL server management studio then for local database connection, use this instead of Local****.

Use ServerName = (LocalDb)\MSSQLLocalDB

Image

For more information go to the reference link - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b75bef7e-13f3-4658-8d43-9df12ab4c320/connecting-localdb-using-sql-server-management-studio-express?forum=sqlexpress

shruti
  • 121
  • 1
  • 5
0

Try this code:

RegistryKey rKey;
string InstanceName;

//use this string for database connection
string ConString;
string regPath = @"Software\Microsoft\Microsoft SQL Server\UserInstances";
rKey = Registry.CurrentUser.OpenSubKey(regPath);

if (rKey.GetSubKeyNames().Length != 0)
{
    regPath += @"\" + rKey.GetSubKeyNames()[0];
    rKey = Registry.CurrentUser.OpenSubKey(regPath);
    InstanceName = rKey.GetValue("InstanceName").ToString();
    ConString = @"Server=np:\\.\pipe\" + InstanceName +
        @"\tsql\query;Initial Catalog=RahBord;Trusted_Connection=True";
}
else
{
    Process.Start(@"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SqlLocalDB.exe", "c amirLOCALDB -s");
    regPath += @"\" + rKey.GetSubKeyNames()[0];
    rKey = Registry.CurrentUser.OpenSubKey(regPath);
    InstanceName = rKey.GetValue("InstanceName").ToString();
    ConString = @"Server=np:\\.\pipe\" + InstanceName + @"\tsql\query;Initial Catalog=RahBord;Trusted_Connection=True";
}

If your instance stopped go to this address:

C:\Program Files\Microsoft SQL Server\110\Tools\Binn\

and use this command

SqlLocalDB.exe s yourInstance
nhahtdh
  • 55,989
  • 15
  • 126
  • 162
yeye
  • 99
  • 1
  • 9
0

Have you checked that the TCP protocol is enabled for your particular server. Follow these instructions to see that it is enabled.

http://msdn.microsoft.com/en-us/library/bb909712(v=vs.90).aspx

After you enable them you must restart the service. If it fails to start check the event log for the reason why it didn’t. Common causes are that there is another service (usually another version of sql server) already running on that port. If that is the case you might need to reconfigure your server ports. Check this blog post (also includes images on how to check for TCP/IP enabled):

http://blogs.msdn.com/b/sqlblog/archive/2009/07/17/how-to-configure-sql-server-to-listen-on-different-ports-on-different-ip-addresses.aspx

After that the usual next barrier is the firewall or Internet security software blocking the connection. This has happened to me even on local instances. Sort that out and you should be good to go.

If after all this you still can’t connect, search the event log for a reason.

Jorge
  • 69
  • 3
0
  1. open Task manager then find sqlservr.exe under the process tab
  2. make sure that the column Command Line is visible if not then click View > Select Column > Scroll Down and tick Command Line > Click OK
  3. you will see "C:\Program Files\Microsoft SQL\110\LocalDB\Binn\sqlservr.exe" -c SMSSQL11E.LOCALDB -sLOCALDB#C1E849D0 --etc
  4. Take note of the LOCALDB#C1E849D0
  5. Open SQL Management studio (runas Administrator) then in the Server name enter \\.\pipe**LOCALDB#C1E849D0**\tsql\query You should be able to browse now all the database inside the LOCALDB
Gil Candido
  • 1