SQL: Connecting to LocalDB from SQL Server Management Studio (SSMS)

I saw a question on the Q&A forums about how to connect to the LocalDB version of SQL Server from SQL Server Management Studio (SSMS). I thought it was worth posting a walkthrough of how to do it.

LocalDB

The first thing to understand about LocalDB is that it doesn’t run as a service. You fire it up as an attached process to another program. The basic idea was that you’d use it from Visual Studio or Azure Data Studio, and those programs would instantiate it as required.

SqlLocalDB

But what if you wanted it to be launched in the background, to have other tools like SSMS connect to it?

Now SSMS doesn’t fire it up for you, but Microsoft provide a utility called SqlLocalDB.exe that does that all for you. The linked page shows the available commands.

On my machine, I’ve opened a command prompt and changed to my C:\Temp folder. (Doesn’t matter where) I’ve then executed the following command to check the current status for LocalDB:

SqlLocalDB info

And it returned:

This tells me the LocalDB instances that have been created. In this case, I’m going to create a new one:

SqlLocalDB create "GREGTEMP" -s

This command creates a new instance and starts it (that’s the -s part). It returns:

Note that it shows both creating it and starting it. I’ve then shared it as a specific name:

SqlLocalDB share "GREGTEMP" "GREGLOCALDB"

You can tell if the instance is started or stopped by executing the info command:

sqllocaldb info "GREGTEMP"

In that image, I’ve shown you one that’s not started, then starting it with a start command, and checking again with the info command. Note that the shared name is also provided.

Connecting to LocalDB from SSMS

At this point, you have two choices for connecting to LocalDB. You can use named pipes. You can see the named pipe address at the bottom of the info command above.

In my case, I’m going to connect from SSMS. I open SSMS and enter the address as follows:

(localdb)\.\GREGLOCALDB

Note that I’m using (localdb) which says to use the in-memory provider for localdb, and then it’s referring to the shared name.

Also note that I’m using Windows authentication. I can create a SQL login, user and password and use those to connect. In this case, Windows is fine, and I just click Connect.

And we’re in business !

NOTE 1: Sometimes when I try this, it will not connect at first, and gives a timeout. Yet if I wait a few minutes, it works fine.

NOTE 2: the documentation says that you can’t remotely manage LocalDB using SSMS. It doesn’t mean you can’t use SSMS, it just has to be on the same machine so that the shared memory provider or the local named pipes provider will work. I do have vague recollections of actually enabling TCP/IP for LocalDB at one point and connecting remotely to it, but that might have been in the early days and no longer possible. If you’re doing that though, you’re better off with SQL Server Express edition.

I hope that helps someone get started.

2024-04-21