How to connect to LocalDB in Visual Studio Server Explorer?

255
votes

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?

14
visual-studioentity-framework-6localdbserver-explorersql-server-2012-localdb
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
Make sure you are running Visual Studio as an administrator. – Darren Griffith
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: stackoverflow.com/a/41906391/3645638 – Svek
My setup creates the database in sqlserverexpress rather than in localdb. What causes this? – Jason Cheng

14 Answers

303
votes

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.
265
votes

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

76
votes

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

34
votes

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.

17
votes

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.
9
votes

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://docs.microsoft.com/en-us/sql/tools/sqllocaldb-utility

7
votes

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'.

6
votes

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.

4
votes

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

3
votes

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...

1
votes

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.

1
votes

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

0
votes

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.

-1
votes

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

enter image description here