Search code examples
sql-servervisual-studio-2015localdbsql-server-data-tools

How to connect to database created in VS2015 SQL Server Data Tools

I have Visual Studio 2015 and SQL Server Data Tools installed. I can see database, tables etc, and run queries in Visual Studio. However, I cannot connect to database using third party application.

enter image description here

It seems, that the SQL Server service isn't listening on any port:

enter image description here

enter image description here

How to connect to that database? Or how to make that service to listen a port?

Solution

  • SQL Server Express LocalDB (commonly referred to as just "LocalDB") runs as a background process initiated by a Login, not as a service. It can only accept local connections. The instances can be started manually using the SQLLocalDB.exe utility, or if an application is a .NET app or implements the LocalDB API, then attempting to connect via the (localdb)\... syntax will automatically start the instance if it is not already running. LocalDB instances also automatically shutdown about 5 minutes after the last connection to it is closed.

    You can see what instances you have by running the following in a command window:

    SQLLocalDB i

    You can start an instance by running:

    SQLLocalDB s ProjectsV12

    You can instance-specific info by running:

    SQLLocalDB i ProjectsV12

    You can stop the instance by running:

    SQLLocalDB p ProjectsV12

    You can connect to an instance to test, even if it is not currently running, by running the following command line (give it a moment to spin up before it connects the first time):

    SQLCMD -S (localdb)\ProjectsV12 -Q "SELECT @@SERVERNAME;"

    That will return a value along the lines of:

    MachineName\LOCALDB#xxxxxxxx

    where each "x" is a hex digit (i.e. 0-9, A-F). Or, if it is a shared instance, the name will be in the following format:

    MachineName\LOCALDB#SHxxxxxx

    Each running instance also has a named-pipes name in the form of:

    np:\.\pipe\LOCALDB#xxxxxxxx\tsql\query

    where each "x" is again a hex digit. And again, if it is a shared instance, then the first two hex digits are replaced with "SH".

    The main difference between the two syntaxes (i.e. (localdb)\... vs np:\\.\pipe\LOCALDB#xxxxxxxx\tsql\query) is that using the named-pipes syntax will not automatically start the instance if it is not already running: only the (localdb)\... syntax can do that as it gets routed through the special LOCALDB API.