How to access local MSSQL server using SQL Server Management Studio?

Reading Time: 4 minutes. 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.Connecting to the local instance in fact requires you to know the instance name pipe – a weird, nonsensical URI-looking piece of textual (pardon my Quebecois) vomit you simply can’t guess. And then, most of the time, you can just use your local user account to log in.But in order to figure out the said name, you’ll need a tool called “SQL Server Express LocalDB Command Line Tool”. It’s a simple exe file that comes with SQL Server, so chances are you already have it.The location is something like this:[SQLServerInstallationPath]\[version]\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 (bolded) with yours.After that, you can run this:SqlLocalDB.exeThe tool should output something like this:Below, the same output in text form.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".Okay, so that’s quite a screenful! The command outputs you the instructions on how to use it – great for your reference, but for now, let me guide you through the process. We’ll only need a couple of parameters.Next, let’s run the command with parameter “info”.> SqlLocalDB infoMSSQLLocalDBProjectsV13Ah, 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.Most likely, you’ll get an output somewhat like this:You’ll want to get this server up and running. That’s easy – just run the command below:SqlLocalDB start MSSQLLocalDBThe output might be something like below:SqlLocalDB info MSSQLLocalDB Name: MSSQLLocalDB Version: 13.1.4001.0 Shared name: Owner: [username] Auto-create: Yes State: Running Last start time: 2019-08-12 1:22:49 PM Instance pipe name: np:\.\pipe\LOCALDB#A4E758FA\tsql\queryPay very close attention to the state – it should now be “running” – and note “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.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.

#SharePointProblems

Solutions are worthless unless shared! Antti K. Koskela's Personal Professional Blog

Month: February 2020

How to access local MSSQL server using SQL Server Management Studio?

Office 365 video migration to Office Stream imminent – Get Ready!