How to connect to your local MSSQL server using SSMS?

This post was most recently updated on January 13th, 2023.

4 min read.

Every now and then you run into a situation, where you really need to run some SQL against your local development database. That database, at least in my case, is hosted on your local SQL Server Express.

Connecting to a local SQL Server should be a walk in a park, right? Eh, well…

While using a connection string to connect to said DB is easy, you can’t do that with the SQL Server Management Studio. I wish you could, but hey – it is what it is.

There’s a couple of ways to connect, though! Let’s start with the easy one, that doesn’t always work:

How to connect to your local database with SQL Server Management Studio?

Table of Contents

Connecting to the local instance might work by using the name of the instance. While I’m not sure what’s the reason it hasn’t worked for me (I wonder if the instance names differs based on what you’re installing the SQL Server or SQL Express with..?), maybe it works for you.

Essentially, just paste this into the connection window:

(localdb)\MSSqlLocalDb

Or like shown below:

How to log in to local SQL Server database using SQL Server Management Studio.

Workaround: Use Named Pipes instead

If it doesn’t work – well, there’s a workaround, although it’s a bit laborious. It in fact requires you to know the instance name pipe – a weird, nonsensical URI-looking piece of textual vomit you simply can’t guess. And then, most of the time, you can just use your local user account to log in.

The workaround has quite a few steps, and finally comes down to this beautiful screen below:

How to log in to local SQL Server using named pipes.

But how do you figure out the right Server name? Check out the steps below!

Time needed: 10 minutes

How to connect to local MS SQL Server?

Try using (localdb)\MSSqlLocalDb first
It won’t hurt testing “(localdb)\MSSqlLocalDb” for Server name – just to see if it works.

That’s definitely the first step – only if it fails, go through the rest of the steps!
Find your friendly local SQL Server Express LocalDB Command Line Tool
The location is something like this under your SQL Server’s installation path -> Tools -> Binn.

So, in order to use the tool, let’s fire up a command line window and write the following command:
> cd “C:\Program Files\Microsoft SQL Server\130\Tools\Binn\”
(Replace the version number with whatever’s relevant to your environment!)
Run the tool
Easy enough – run this in your console:
SqlLocalDB.exe

The tool should output something like this:
Take a closer look at your SQL Server instance
Next, let’s run the command with parameter “info”.
> SqlLocalDB info MSSQLLocalDB ProjectsV13

Ah, great. Now we know what the SQL Server instances on the machine are named. The one we’re mostly interested in is MSSQLLocalDB – that’s the server instance hosting our local dev databases.
Run the tool with instance name
Now we’ll get to actually run the command for our instance. This’ll look somewhat like below:
SqlLocalDB info [instancename]

It’ll look somewhat like below:
Start your SQL Server instance!
You’ll want to get this server up and running. That’s easy – just run the command below:
SqlLocalDB start MSSQLLocalDB

The output might be something like below:

Or in text form:
SqlLocalDB info MSSQLLocalDB Name: MSSQLLocalDB Version: 13.1.4001.0 Shared name: Owner: [username] Auto-create: Yes State: Running Last start time: 2020-08-12 1:22:49 PM Instance pipe name: np:.\pipe\LOCALDB#A4E758FA\tsql\query

Pay very close attention to the state – it should now be “running” – and note “Instance pipe name“!
Copy the Instance pipe name
While you can’t easily use the connection string to connect to the server, you can use the “named pipe” instead! Grab the path, starting with np:\ to your clipboard.
Log in to SSMS
Next, you should be able to use your local log-in for the SQL Server. See the screenshot down below:

And boom! You should be good.

Frequently asked questions

Where to download SQL Server Management Studio (SSMS)?

The executable installation file is hosted by Microsoft – they keep changing the shortlink of the actual download, so your best bet is to download it from the Learn/Docs page describing the tool – here: https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16

References and appendices

Updated 13.2.2020: Added mention of the easier method, thanks Mika Berglund.

For further reference, check out these links:

Appendix 1: the output of SqlLocalDB.exe

I’m including the whole default output of SqlLocalDB.exe here, as it won’t fit into the step-by-step instructions above.

C:\Program Files\Microsoft SQL Server\130\Tools\Binn>SqlLocalDB.exe
 Microsoft (R) SQL Server Express LocalDB Command Line Tool
 Version 13.0.1601.5
 Copyright (c) Microsoft Corporation.  All rights reserved.
 Usage: SqlLocalDB operation [parameters…]
 Operations:
 -?
     Prints this information
 create|c ["instance name" [version-number] [-s]]
     Creates a new LocalDB instance with a specified name and version
     If the [version-number] parameter is omitted, it defaults to the
     latest LocalDB version installed in the system.
     -s starts the new LocalDB instance after it's created
 delete|d ["instance name"]
     Deletes the LocalDB instance with the specified name
 start|s ["instance name"]
     Starts the LocalDB instance with the specified name
 stop|p ["instance name" [-i|-k]]
     Stops the LocalDB instance with the specified name,
     after current queries finish
     -i request LocalDB instance shutdown with NOWAIT option
     -k kills LocalDB instance process without contacting it
 share|h ["owner SID or account"] "private name" "shared name"
     Shares the specified private instance using the specified shared name.
     If the user SID or account name is omitted, it defaults to current user.
 unshare|u ["shared name"]
     Stops the sharing of the specified shared LocalDB instance.
 info|i
     Lists all existing LocalDB instances owned by the current user
     and all shared LocalDB instances.
 info|i "instance name"
     Prints the information about the specified LocalDB instance.
 versions|v
     Lists all LocalDB versions installed on the computer.
 trace|t on|off
     Turns tracing on and off
 SqlLocalDB treats spaces as delimiters. It is necessary to surround
 instance names that contain spaces and special characters with quotes.
 For example:
    SqlLocalDB create "My LocalDB Instance"
 The instance name can sometimes be omitted, as indicated above, or
 specified as "". In this case, the reference is to the default LocalDB
 instance "MSSQLLocalDB".

Author
Recent Posts

Antti K. Koskela

Developer / Speaker / Consultant at Koskila / Precio Fishbone / Norppandalotti Software Co / Alter - Experience Ideas Ltd

Antti Koskela is a proud digital native nomadic millennial full stack developer (is that enough funny buzzwords? That's definitely enough funny buzzwords!), who works as Solutions Architect for Precio Fishbone, building delightful Digital Workplaces.

He's been a developer from 2004 (starting with PHP and Java), and he's been working on .NET projects, Azure, Office 365, SharePoint and a lot of other stuff. He's also Microsoft MVP for Azure.

This is his personal professional (e.g. professional, but definitely personal) blog.

Latest posts by Antti K. Koskela (see all)