Login/Register
How to connect to LocalDB in Visual Studio Server Explorer?
Asked Answered
Solved visual-studioentity-framework-6localdbserver-explorersql-server-2012-localdb
N

14

271

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?

Noelianoell answered 4/2, 2014 at 21:33 Comment(4)
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)? – Unroot
Make sure you are running Visual Studio as an administrator. – Kalpak
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: https://mcmap.net/q/108431/-how-to-connect-to-localdb-in-visual-studio-server-explorer – Venireman
My setup creates the database in sqlserverexpress rather than in localdb. What causes this? – Derwon
N
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

Noelianoell answered 4/2, 2014 at 21:34 Comment(14)
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? – Froufrou
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.. – Enki
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. – Interglacial
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 – Maximinamaximize
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. – Nurseryman
@JohnPankowicz you can simply call it with absolute path "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\SqlLocalDb.exe" info v11.0 – Noelianoell
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. – Erse
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. – Waggle
@Froufrou That was a curious statement. Why would there be any local db to support? LocalDB should be for dev or sandbox stuff only, no? – Coiffure
@Coiffure 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? – Froufrou
@Froufrou 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: 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. – Coiffure
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. – Hughmanick
I would propose to consider a much simpler approach... https://mcmap.net/q/108431/-how-to-connect-to-localdb-in-visual-studio-server-explorer – Venireman
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: sqlshack.com/… – Ula
J
323

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.
Jetport answered 13/10, 2014 at 15:37 Comment(10)
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. – Noelianoell
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 :) – Jetport
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. – Sublunary
thank you. I was racking my brains until find out your answer. – Decimate
Why Microsoft, why? Why change the name and not update your documentation? (Thank you for the VS 2015 path) – Lusty
(localdb)\MSSQLLocalDB worked just fine for me, also. VS2015 localdb SQL 2016 – Coiffure
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! – Transit
(localdb)\MSSQLLocalDB worked for me in VS 2017, too – Wilhite
Yep, .net programming for over six years but wasted a whole afternoon on this very issue! – Hughmanick
View/SQL Server Object Explorer Yes! – Debroahdebs
N
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

Noelianoell answered 4/2, 2014 at 21:34 Comment(14)
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? – Froufrou
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.. – Enki
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. – Interglacial
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 – Maximinamaximize
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. – Nurseryman
@JohnPankowicz you can simply call it with absolute path "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\SqlLocalDb.exe" info v11.0 – Noelianoell
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. – Erse
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. – Waggle
@Froufrou That was a curious statement. Why would there be any local db to support? LocalDB should be for dev or sandbox stuff only, no? – Coiffure
@Coiffure 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? – Froufrou
@Froufrou 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: 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. – Coiffure
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. – Hughmanick
I would propose to consider a much simpler approach... https://mcmap.net/q/108431/-how-to-connect-to-localdb-in-visual-studio-server-explorer – Venireman
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: sqlshack.com/… – Ula
E
84

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

Espy answered 1/4, 2015 at 12:9 Comment(7)
somehow it was the only working answer on 28/04/2015 – Jhelum
same here vs2015.. This was the answer.. I guess they renamed localDb? – Zarah
This one did it for VS2015 RC on Win 8.1. – Ditzel
About the server name: #27197859 – Kinetics
Works in vs2017 also. – Nirvana
I've got Visual Studio Enterprise 2019 v16.8.3, and this worked. My only LocalDB instance was ""MSSQLLocalDB". – Ula
Works in VS 2022 v17.1.6 Community – Friday
V
53

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.

Venireman answered 28/1, 2017 at 4:40 Comment(1)
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! – Development
G
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.
Grice answered 7/5, 2015 at 10:49 Comment(0)
C
12

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

Claude answered 12/4, 2017 at 15:43 Comment(0)
C
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.

Commanding answered 25/9, 2014 at 13:15 Comment(3)
This StackOverflow Q/A is about LocalDB. I think your answer is about enabling other instances of SQL. – Noelianoell
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. – Commanding
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. – Chaplain
L
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'.

Listlessness answered 10/7, 2015 at 7:19 Comment(0)
T
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

Tong answered 31/12, 2016 at 17:55 Comment(0)
T
4

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

Thiazine answered 21/6, 2017 at 17:4 Comment(1)
If you did not install SQLExpress to a default instance you may also try... '(localdb)' – Methylal
B
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.

Benia answered 25/9, 2015 at 2:58 Comment(0)
R
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

Reddin answered 23/4, 2021 at 8:55 Comment(0)
W
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.

Wisner answered 15/5, 2015 at 18:27 Comment(0)
M
-1

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

enter image description here

Maulstick answered 4/12, 2017 at 23:16 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.