267

I can't believe I couldn't find a working solution to this after an hour of searching. I'm following this article on Entity Framework 6.0 which gives a simple walk-through on Code First. I created the project and installed the latest EF Nuget package for the project to compile. I also verified that I have Microsoft SQL Server 2012 Express LocalDB installed which came with Visual Studio 2013. I don't have any other instances of SQL installed on my local computer. The program runs and entries are added to the database and outputted in the console. But when the article says "check your localdb" it doesn't say how! I don't see any '.mdf' or '.ldf' files created under the project folder. I tried every way to connect Visual Studio's Server Explorer to LocalDB. The wizard cannot locate (localdb) or cannot find any provider in Server Explorer to accept connection string like (localdb)\v11.0;Integrated Security=true; I've seen this asked several places in StackOverflow but no answer works or marked as answer. Please help, this doesn't have to be this frustrating!

What are the steps to connect Visual Studio Server Explorer to LocalDB?

MAXE
  • 4,978
  • 2
  • 45
  • 61
orad
  • 15,272
  • 23
  • 77
  • 113
  • 91
    I often pity people who are just entering this business. 20 years ago, the tools were actually much easier to use. The documentation is also now barely adequate, and in most cases, less than. I see "documentation" now on MSDN that simply explains a method, such as "foo" -> "This is the foo method." Really, Microsoft? What happened to the technical writers? What happened to Books Online (for real)? – Pittsburgh DBA Apr 28 '15 at 19:35
  • 3
    Make sure you are running Visual Studio as an administrator. – Darren Griffith Jun 03 '15 at 14:06
  • 7
    If you're okay with using Visual Studio's **SQL Server Object Explorer** instead of **Server Explorer**. It is simpler solution (less manual configuration), check my answer: http://stackoverflow.com/a/41906391/3645638 – Svek Feb 23 '17 at 08:36
  • 1
    My setup creates the database in sqlserverexpress rather than in localdb. What causes this? – Jason Cheng Sep 12 '18 at 22:36

14 Answers14

322

In Visual Studio 2012 all I had to do was enter:

(localdb)\v11.0

Visual Studio 2015 and Visual Studio 2017 changed to:

(localdb)\MSSQLLocalDB

as the server name when adding a Microsoft SQL Server Data source in:

View/Server Explorer/(Right click) Data Connections/Add Connection

and then the database names were populated. I didn't need to do all the other steps in the accepted answer, although it would be nice if the server name was available automatically in the server name combo box.

You can also browse the LocalDB database names available on your machine using:

View/SQL Server Object Explorer.
RationalDev likes GoFundMonica
  • 12,383
  • 6
  • 46
  • 49
  • 4
    Yup, this is what is supposed to work the first time. But if it didn't, the steps in the accepted answer should start the service and make it work. – orad Oct 13 '14 at 18:21
  • Maybe SQL Server Object Explorer started it for me as I used it to find out what the server name Entity Framework had used. I had found this question, but was kind of scared off trying the answer as I was hoping for something simpler and hadn't fully read it as I thought I had to use the pipe name :) – RationalDev likes GoFundMonica Oct 14 '14 at 08:40
  • Am I just blind to the details, or is it extremely frustrating that setting this up isn't more intuitive? Whether in 2012 or 2015, it seems like they are almost hiding stuff like this throughout the IDE. – Rex_C Jan 08 '16 at 20:01
  • thank you. I was racking my brains until find out your answer. – Alexandre N. Feb 16 '16 at 20:57
  • 7
    Why Microsoft, why? Why change the name and not update your documentation? (Thank you for the VS 2015 path) – Thibault D. Nov 18 '16 at 09:27
  • (localdb)\MSSQLLocalDB worked just fine for me, also. VS2015 localdb SQL 2016 – JDPeckham Mar 19 '17 at 14:31
  • I have VS 2015 but I was using the VS 2012 way to connect to my locadb. So, `(localdb)\MSSQLLocalDB` made the trick. Thank you very much! – Labanino Jun 23 '17 at 22:10
  • `(localdb)\MSSQLLocalDB` worked for me in VS 2017, too – Tony L. Sep 19 '17 at 18:44
  • Yep, .net programming for over six years but wasted a whole afternoon on this very issue! –  Dec 17 '17 at 18:17
  • View/SQL Server Object Explorer Yes! – Jepzen Jul 12 '18 at 10:08
267

OK, answering to my own question.

Steps to connect LocalDB to Visual Studio Server Explorer

  1. Open command prompt
  2. Run SqlLocalDB.exe start v11.0
  3. Run SqlLocalDB.exe info v11.0
  4. Copy the Instance pipe name that starts with np:\...
  5. In Visual Studio select TOOLS > Connect to Database...
  6. For Server Name enter (localdb)\v11.0. If it didn't work, use the Instance pipe name that you copied earlier. You can also use this to connect with SQL Management Studio.
  7. Select the database on next dropdown list
  8. Click OK

enter image description here

Jeffrey Knight
  • 5,888
  • 7
  • 39
  • 49
orad
  • 15,272
  • 23
  • 77
  • 113
  • 95
    Almost every action these days on the MS platform involves some magic or prior memorized knowledge....who will support these things in 5 years when a whole new set of magic is in place? – tbone Jul 25 '14 at 20:15
  • And why localDB is not discovered in "Add Connection" dialog when my app is fetching and adding to it, and it seems it wasnt even started.. – Michael Sep 26 '14 at 09:17
  • 37
    Thank you! Just to add to this excellent answer (and excellent question): `SqlLocalDb info` will list all server names. In my case, after the EF code-first magic, my database ended up in `MSSQLLocalDB` not `v11.0`, so I entered `(localdb)\MSSQLLocalDB` in the Add Connection dialog box. – biscuit314 Oct 22 '14 at 17:29
  • I don't know if this was coincidence, but before installing SSMS 2012 (and it's requirements), I could only get the named pipe connection to work. After, (localdb)\v11.0 worked no problem – MStodd Jan 23 '15 at 16:13
  • 1
    SqlLocalDb.exe appears twice in my system path - first in C:\Program Files\Microsoft SQL Server\110\Tools\Binn and then in C:\Program Files\Microsoft SQL Server\120\Tools\Binn. Therefore only the earlier version will ever get called! I have both VS2012 and VS 2013 installed. – John Pankowicz Feb 04 '15 at 15:21
  • @JohnPankowicz you can simply call it with absolute path `"C:\Program Files\Microsoft SQL Server\120\Tools\Binn\SqlLocalDb.exe" info v11.0` – orad Feb 04 '15 at 22:25
  • 2
    I believe more and more people are gravitating over to other programming languages from Microsoft stuff. This simple configuration stuff and file location stuff has become harder than whatever the other languages lack. If they lack at all. – JustJohn Aug 17 '15 at 22:02
  • 5
    I've been scratching my head all afternoon over this. Why do they have to make it so complicated, when it used to be so bloody easy? Thanks for the help. – George Williams Aug 18 '15 at 05:14
  • @tbone That was a curious statement. Why would there be any local db to support? LocalDB should be for dev or sandbox stuff only, no? – JDPeckham Mar 19 '17 at 14:34
  • @JDPeckham My point was that we are expected to somehow know so many incredibly obscure details on the MS platform, this post is a good example. How is one supposed to know about (localdb)\v11.0? Why not v12.0? – tbone Mar 20 '17 at 14:46
  • @tbone that'snot how you categorized your statement by responding to this particular answer you were implying these instructions are required for use and production support of such items would be hard. Second, connecting is in the instructions which are readily available: https://msdn.microsoft.com/en-us/library/hh510202.aspx and lastly, my point was that it is for development not production use. I think it's a strawman fallacy to state that MS products are so complex just because you are confused. – JDPeckham Mar 23 '17 at 15:00
  • SqlLocalDB.exe start v13.0 from the command prompt worked for me. Then (localdb)/ProjectsV13 as the server name in Visual Studio 2017 server name. –  Dec 17 '17 at 18:45
  • I would propose to consider a much simpler approach... https://stackoverflow.com/a/41906391/3645638 – Svek Jul 14 '18 at 20:10
  • 1
    I've got Visual Studio Enterprise 2019 v16.8.3, and this worked. My only LocalDB instance was "MSSQLLocalDB". Instead of the piping, I just did as Kaps/Nasreddine proposed and used (localdb)\MSSQLLocalDB for the Server Name in creating the connection. Seems the default instance for LocalDB varies by the version of SQL Server Express installed - 202 uses v11.0, but 2014 or newer will use MSSQLLocalDB: https://www.sqlshack.com/how-to-connect-and-use-microsoft-sql-server-express-localdb/ – Analytic Lunatic Jan 05 '21 at 15:29
83

Select in :

  1. Data Source: Microsoft SQL Server (SqlClient)
  2. Server name: (localdb)\MSSQLLocalDB
  3. Log on to the server: Use Windows Authentication

Press Refresh button to get the database name :)

Screenshot

Nasreddine
  • 36,610
  • 17
  • 75
  • 94
Kaps
  • 831
  • 7
  • 8
46

Use SQL Server Object Explorer (SSOX) Instead

Unlike the other answers, this approach uses:
- No special commands.
- No complicated configurations.
Just use the SQL Server Object Explorer

It's pretty straightforward...

  • From the View menu, open SQL Server Object Explorer.

one

  • Right click on the {YourTableName} table > View Designer

two

Done.

Svek
  • 12,350
  • 6
  • 38
  • 69
  • Nice, using Sql Server Express 2019 on Windows 11, and viewing this within VS2022, mine was named ProjectModels, instead of MSSQLLocalDB... Maybe I missed something during setup.. but anyway, this allowed me to at least set my connection string properly. Thanks! – eaglei22 Nov 04 '22 at 22:15
20

It worked for me.

  1. Open command prompt
  2. Run "SqlLocalDB.exe start"
  3. System response "LocalDB instance "mssqllocaldb" started."
  4. In VS， View/Server Explorer/(Right click) Data Connections/Add Connection
    • Data Source: Microsoft SQL Server (SqlClient)
    • Server name: (localdb)\MSSQLLocalDB
    • Log on to the server: Use Windows Authentication
  5. Press "Test Connection", Then OK.
Ader Hwang
  • 201
  • 2
  • 2
11

The following works with Visual Studio 2017 Community Edition on Windows 10 using SQLServer Express 2016.

Open a PowerShell check what it is called using SqlLocalDB.exe info and whether it is Running with SqlLocalDB.exe info NAME. Here's what it looks like on my machine: 

> SqlLocalDB.exe info
MSSQLLocalDB
> SqlLocalDB.exe info MSSQLLocalDB
Name:               mssqllocaldb
Version:            13.0.1601.5
Shared name:
Owner:              DESKTOP-I4H3E09\simon
Auto-create:        Yes
State:              Running
Last start time:    4/12/2017 8:24:36 AM
Instance pipe name: np:\\.\pipe\LOCALDB#EFC58609\tsql\query
>

If it isn't running then you need to start it with SqlLocalDB.exe start MSSQLLocalDB. When it is running you see the Instance pipe name: which starts with np:\\. Copy that named pipe string. Within VS2017 open the view Server Explorer and create a new connection of type Microsoft SQL Server (SqlClient) (don't be fooled by the other file types you want the full fat connection type) and set the Server name: to be the instance pipe name you copied from PowerShell.

I also set the Connect to database to be the same database that was in the connection string that was working in my Dotnet Core / Entity Framework Core project which was set up using dotnet ef database update.

You can login and create a database using the sqlcmd and the named pipe string:

sqlcmd -S np:\\.\pipe\LOCALDB#EFC58609\tsql\query 1> create database EFGetStarted.ConsoleApp.NewDb; 2> GO

There are instructions on how to create a user for your application at https://learn.microsoft.com/en-us/sql/tools/sqllocaldb-utility

simbo1905
  • 6,321
  • 5
  • 58
  • 86
7

Fix doesn't work.

Exactly as in the example illustration, all these steps only provide access to "system" databases, and no option to select existing user databases that you want to access.

The solution to access a local (not Express Edition) Microsoft SQL server instance resides on the SQL Server side:

  1. Open the Run dialog (WinKey + R)
  2. Type: "services.msc"
  3. Select SQL Server Browser
  4. Click Properties
  5. Change "disabled" to either "Manual" or "Automatic"
  6. When the "Start" service button gets enable, click on it.

Done! Now you can select your local SQL Server from the Server Name list in Connection Properties.

alejandrob
  • 603
  • 8
  • 6
  • 8
    This StackOverflow Q/A is about [LocalDB](http://msdn.microsoft.com/en-us/library/hh510202.aspx). I think your answer is about enabling other instances of SQL. – orad Sep 25 '14 at 18:15
  • I tried this fix on local environment and it does the FIX as expected for the original question posted, not what you may *think* it does. Don't assume that Enterprise Edition = Enterprise environment. Some of us use that edition for local development on a workgroup machine. – alejandrob Oct 30 '14 at 15:16
  • Even though I still had to enter (localdb) rather than use the browser this actually fixed it for me, so it does answer the question nicely. – PeterJ Dec 28 '14 at 04:34
7

Visual Studio 2015 RC, has LocalDb 12 installed, similar instructions to before but still shouldn't be required to know 'magic', before hand to use this, the default instance should have been turned on ... Rant complete, no for solution:

cmd> sqllocaldb start

Which will display 

LocalDB instance "MSSQLLocalDB" started.

Your instance name might differ. Either way pop over to VS and open Server Explorer, right click Data Connections, choose Add, choose SQL Server, in the server name type:

(localdb)\MSSQLLocalDB

Without entering in a DB name, click 'Test Connection'.

OzBob
  • 4,227
  • 1
  • 39
  • 48
4

Run the CMD as admin.

  1. from start menu 'cmd' - wait for it to find it.
  2. Right click on cmd, and select open as administrator
  3. type : cd C:\Program Files\Microsoft SQL Server\120\Tools\Binn
  4. type : SqlLocalDB start
  5. now type : SqlLocalDB info
  6. Shows the running sql instances available... choose what you want...

  7. to find more about the instance type : SqlLocalDB info instanceName

  8. now from VS you can setup your connection In VS， View/Server Explorer/(Right click) Data Connections/Add Connection Data Source: Microsoft SQL Server (SqlClient) Server name: (localdb)\MSSQLLocalDB Log on to the server: Use Windows Authentication Press "Test Connection", Then OK.

  9. job done

guestDoug
  • 41
  • 1
3

The fastest way in Visual Studio 2017 is to go to Tools -> SQL Server -> New query.. Choose from Local databases and choose the desired Database name at the bottom.

Alternative way

Visual Studio 2017 Server name is:

(localdb)\MSSQLLocalDB

Add the new connection using menu Tools -> Connect to Database...

Samuel
  • 346
  • 1
  • 9
1

I followed the steps above, but I forgot to install the SQL Server 2014 LocalDB before the Visual Studio 2015 configuration.

My steps are as follow:

  1. Install the SQL Server 2014 LocalDB;
  2. Open Visual Studio 2015 and then SQL Server Object Explorer;
  3. Find your LocalDB under the SQL Server tag.

Hope this help anybody.

David Ferenczy Rogožan
  • 23,966
  • 9
  • 79
  • 68
Mondi
  • 26
  • 8
1

https://aspblogs.blob.core.windows.net/media/dixin/Open-Live-Writer/89ee21b2c263_49AE/image_thumb_5.png Windows Application Log Error Computer\HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\UserInstances{2DD3D445-34C1-4251-B67D-7DFEED432A87}

Just change ParentInstance to MSSQL14E.LOCALDB or MSSQL15E.LOCALDB.

https://weblogs.asp.net/dixin/installing-sql-server-2017-2019-localdb-and-resolve-the-engine-versioning-problem?__r=8d90635095e1cd4

Kenan Cidali
  • 11
  • 1
0

Scenario: Windows 8.1, VS2013 Ultimate, SQL Express Installed and running, SQL Server Browser Disabled. This worked for me:

  1. First I enabled SQL Server Browser under services.
  2. In Visual Studio: Open the Package Manager Console then type: Enable-Migrations; Then Type Enable-Migrations -ContextTypeName YourContextDbName that created the Migrations folder in VS.
  3. Inside the Migrations folder you will find the "Configuration.cs" file, turn on automatic migrations by: AutomaticMigrationsEnabled = true;
  4. Run your application again, the environment creates a DefaultConnection and you will see the new tables from your context. This new connection points to the localdb. The created connection string shows: Data Source=(LocalDb)\v11.0 ... (more parameters and path to the created mdf file)

You can now create a new connection with Server name: (LocalDb)\v11.0 (hit refresh) Connect to a database: Select your new database under the dropdown.

I hope it helps.

Armando S
  • 11
  • 2
-1

With SQL Server 2017 and Visual Studio 2015, I used localhost\SQLEXPRESS

enter image description here

Ben S
  • 33
  • 8