Configuring SQL Server LocalDB
SQL Server LocalDB is a uselful lightweight version of the SQL Server database system that allows for easy delevopment of SQL Server related applications.
There are a number of versions which can be used, and they can all be installed and used on the same system at the same time. This feature makes it easy to develop applications on the same computer that target different versions of SQL Server.
Version downloads
For localdb 2012 and 2014, select the correct version of SQLLocalDB.msi for your operating system (x32 or x64).
For localdb 2016, download the SQL Server Express install, run it, select the Download Media buton, select the SQLLocaldb option and save it.
Instance default database locations
The default placement of instances of SQL Local DB are in the AppData folder of the user:
Command line
SQL LocalDB has a command line tool for managing database instances.
Versions installed
To see the versions currently installed:
Instances and names
The installed instances can be seen asking for info. Providing an instance name will print detailed info about the instance.
Creating instances
If more than one version of SQLLocalDB is installed and it is desired to use them all, you can create specific instances of each version with names that indicate what version the instance is — instead of just having the default “MSSQLLocalDB” instance.
Here is a sample for creating named instances of all three. The “-s” parameter causes the instance to be started as well.
Starting instances
An instance can be started or stop witht he command line.
Removing instances
Instances can also be removed.
Connecting to an instance in Visual Studio
To connect to the LocalDB instance in Visual Studio, open the Server Explorer tool window, add a connection, specfify the following connection string (altered for your instance name), select the database from the dropdown, “Test” the connection, and select Ok.
Connecting to an instance in SQL Server Management Studio
To connect to an instance of LocalDB using SSMS, use the following line (altered for your instance name) in the connection name:
Connect to the SQL Server
Summary: in this tutorial, you will learn how to connect to SQL Server from the SQL Server Management Studio and execute a query.
Connect to the SQL Server using SSMS
To connect to the SQL Server using the Microsoft SQL Server Management Studio, you use these steps:
First, launch the Microsoft SQL Server Management Studio from the Start menu:
Next, from the Connect menu under the Object Explorer, choose the Database Engine…
Then, enter the information for the Server name (localhost), Authentication (SQL Server Authentication), and password for the sa user and click the Connect button to connect to the SQL Server. Note that you should use the sa user and password that you entered during the installation.
If the connection is established successfully, then you will see the following Object Explorer panel:
Execute a query
To execute a query you follow these steps:
First, right-click on the localhost (SQL Server …) node and choose the New Query menu item:
Second, enter the following query in the Editor
This query returns the version of the SQL Server.
Third, click the Execute button:
The Results window shows the version of the SQL Server as shown in the above screenshot. A quick way to execute a query is to press the F5 keyboard shortcut.
Now, you should know how to connect to a SQL Server and execute a query from the SSMS.
How to connect to your local MSSQL server using SSMS?
This post was most recently updated on January 13th, 2023.
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:
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
-
Try using (localdb)\MSSqlLocalDb first
It won’t hurt testing “(localdb)\MSSqlLocalDb” for Server name – just to see if it works.
The location is something like this under your SQL Server’s installation path -> Tools -> Binn.
Easy enough – run this in your console:
SqlLocalDB.exe
The tool should output something like this:
Next, let’s run the command with parameter “info”.
> SqlLocalDB info
MSSQLLocalDB
ProjectsV13
Now we’ll get to actually run the command for our instance. This’ll look somewhat like below:
SqlLocalDB info [instancename]
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“!
And boom! You should be good.
Frequently asked questions
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.
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.
Connect to SQL Server Using Management Studio
In this tutorial, let us learn how to connect to SQL Server Using SQL Server Management Studio (SSMS). Using the SQL Server Management Studio is a simple and easy way to connect to the SQL Server Instance.
If you have not installed the SQL Server, then you must visit how to install SQL Server & How to install SQL Server Management Studio tutorials
Table of Contents
Starting SSMS
Start SQL Server Management Studio. You can find it from
Program files -> Microsoft SQL Server Tools 18 -> Microsoft SQL Server Management Studio
The first time you run SSMS, the Connect to Server window opens.
If it doesn’t open, you can open it manually by selecting Object Explorer > Connect > Database Engine. If Object Explorer is not opened, you can open it from the Menu -> View -> Object Explorer
Connect to Server
The Connect to Server window appears as shown below
Server Type: Choose Database Engine to connect to SQL Server database Server.
Server Name: Server Instance Name. Use your computer name if you want to connect to default Instance. Else enter the name as <compuetName>\<InstanceName>
The instance name is the name you have given when you were installing the SQL Server under Instance Configuration.
Authentication: There are two authentication modes. One is Windows & Another one is SQL Server authentication mode. If you choose windows, then the current logged in user is automatically picked by the system.
SQL Server Authentication works only if you have selected the Mixed Mode authentication when you were installing the SQL Server. We set up this while configuring the database engine.
User Name: This is enabled when you choose SQL Server Authentication Mode. If you are connecting to the newly installed SQL Server then the only user you have is sa.
Password: Enter the sa password
Click on Connect to Connect to the Server
Object Explorer
The SSMS connects to the SQL Server Instance. On Successful connection, you will see the object explorer as shown in the image below.
Under the Server Name, you will see various nodes like database, security, management listed. Expand the Databases node to see the list of databases available. In a newly installed server, you won’t see any database except for the System databases.
Summary
We use SSMS to connect to the database. SSMS Provides the GUI tools to explore & Manage a database instance.