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.

Varanasi

LocalDB is a variant of SQL Server Express that is loaded on demand when an application needs it. So yes - it might be a bit "elusive" - however, there's the sqllocaldb utility that allows you to spin up LocalDB instances as needed, and then they stay active until you shut them down again. Google for sqllocaldb and read how to use it! – Kilometer

Possible duplicate of Unable to connect to LocalDB with HeidiSQL – Bonnie

An old one but a good one. After trying all sorts of hostname / IP variants, a simple dot worked. – Panay

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.

Fieldpiece

I was struggling for hours only to realize I had to remove np: from Instance pipe name. For me sqllocaldb info MSSQLLocalDB returned np:\\.\pipe\LOCALDB#DCEF86CA\tsql\query. In HeidiSql I had to use \\.\pipe\LOCALDB#DCEF86CA\tsql\query and it worked ! – Avidity

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://learn.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)

Windjammer

i tried to search.... that SQL Server Configuration but not found... any clue? i'm sure that i have SQL Server installed correctly, s23.postimg.org/cd4dq5iyz/sql_server_program.png @Windjammer – Saloon

@Saloon See the edit I'm about to make for an answer to that. – Windjammer

Answer 3

FYI, I answered this question in another SO question as well with step-by-step instructions. Special thanks to @GravityWell for the pipe name suggestion!

Hufford

Would you mind putting the instructions here, instead of pointing to an external source that may disappear without notice and without trace? – Bonnie

SQB you're right, however the link points to an answer in SO, rather than an external source. – Hufford

Ah, I missed that. The same answer applying to two questions points to the possibility of those questions being duplicates. So we should probably close one in favour of the other. However, the point still stands: another answer or even entire question may be deleted, making this a dead link. – Bonnie