HeidiSql connection to MS SQL Server LocalDB

Question

I have been trying in vain to connect to my SQL Server database, attached to the LocalDB instance (localdb)\v11.0, using HeidiSql and Windows Authentication.

My testing has shown that HeidiSql can connect to the permanently installed MSSQLSERVER and SQLEXPRESS instances as follows:

MSSQLSERVER:

• SQL Server: (named pipe)
• Hostname / IP: .
• Use Windows Authentication

SQLEXPRESS:

• SQL Server: (named pipe)
• Hostname / IP .\SQLEXPRESS
• Use Windows Authentication

The LocalDB connection proves elusive.

My reading of the situation suggests that the LocalDB instance is something of a phantom created by Visual Studio and SQL Server Management Studio as and when required. They don't persist or exist when these programs aren't running. When they are running access is buried in these programs and not available to other applications.

I know I can switch to MSSQLSERVER or SQLEXPRESS as the standard database server for my Visual Studio applications but am interested to know whether there is a pathway for connecting to HeidiSql the LocalDB instance.

Answer 1

It can work, but is tricky. You need to use the LocalDB named pipe as the Heidi hostname. Using LocalDB V12 for example:

sqllocaldb info sqllocaldb start MSSQLLocalDB sqllocaldb info MSSQLLocalDB 

The "info" parameter reveals:

\\.\pipe\LOCALDB#12345678\tsql\query 

Note the number is a random hash, which complicates things, as you need to keep checking it. It's possible to batch/script the above, with Heidi's command line options.

My command line:

heidisql -d=LocalDB -h=%pipename% -n=3 -d=LocalDB 

The -d parameter is used twice intentionally.

Answer 2

For normal SQL Server instances rather than LocalDB instances:

I realize this question asks about local db; however, this result came up when I was looking around and this answer would have helped me.

In windows search (press enter and type to search) enter "sql server configuration manager". Open that, but make sure you are not opening "sql server master data servervices configuration manager" that is not correct. For me, the first time I had to look be low it in the results under "apps".

Then Click "SQL Server Network Configuration".

If that does not come up, you can manually open the file. See: https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-configuration-manager

For me it is at "C:\Windows\SysWOW64\SQLServerManager13.msc"

Double click "Protocols for [your instance here]"

To access connect using something besides SSMS, you'll need to choose to enable a named pipe, or TCP/IP.

TCP/IP

Right Click "TCP/IP" and click properties

Change "Enabled" to true

Change IP4 (or the one with 127.0.0.1 as the IP Address) to be enabled.

Click "Ok".

Jump to restarting

Named Pipe

Right click "Named Pipes" and click "Properties"

Change "Enabled" to true

Copy the "Pipe Name" and use that in your database client later

Jump to restarting

Restarting

As you are warned, you need to restart the service, so click "SQL Server Services" on the left

Right click "SQL Server ([your instance name])" and click "Restart"

You can now use your database client to browse your sql server instance. In heidi, you simply select the network type that matches your setup - choose "Use Windows authentication" and fill in the appripriate hostname/ip (the pipe name or the ip depending on which method you chose to use)