Unable to connect to LocalDB with HeidiSQL

问题:

I have been trying to connect to a localdb instance with the latest version (at this time) of heidisql to no avail.

I have followed the instructions from this answer here but it doesn't seem to work (anymore):

HeidiSql connection to MS SQL Server LocalDB

I tried the following:

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

2) heidisql -d=LocalDB -h= np:\.\pipe\LOCALDB#41CF9FCB\tsql\query -n=3 -d=LocalDB (i realize that the number changes every time your start a new localdb instance)

3) heidisql -d=LocalDB -h=41CF9FCB -n=3 -d=LocalDB

Any ideas?

EDIT

Error message

回答1:

For the following connection string in my Web.config;

<add name="WEDOBADGEDatabase" connectionString="Data Source=(LocalDb)\development;Initial Catalog=WEDOBADGE;AttachDBFilename=|DataDirectory|\DevelopmentDatabase.mdf;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />

Where the most important piece of information is Data Source=(LocalDb)\development, [replacing (of course) C:\Program Files\Microsoft SQL Server\120\ with the correct path & use the correct name chosen in your connection string after Data Source=(LocalDb)\ (noncase-sensitive, development in my case)] try:

"C:\Program Files\Microsoft SQL Server\120\Tools\Binn\SqlLocalDB.exe" info Development

This should output something similar to:

Name:               Development
Version:            12.0.2000.8
Shared name:
Owner:              hostname\username
Auto-create:        No
State:              Running
Last start time:    8/2/2016 3:20:57 PM
Instance pipe name: np:\\.\pipe\LOCALDB#CDE5547F\tsql\query

Copy \\.\pipe\LOCALDB#CDE5547F\tsql\query from the "Instance pipe name", WITHOUT the initial np:. Also, your pipe name is likely to be different than mine.

Then you can create a new session in HeidiSQL like this:

N.B.: "Network type", "Hostname / IP", and "Use Windows authentication" are important; whilst other settings (such as "Databases") is up to you, so you could leave them empty / skip, etc.

Credits go to https://stackoverflow.com/a/33748584/11895 for the pipe name suggestion.

回答2:

As of heidisql.exe Revision 9.3.0.5108 you could also:

1. Start the database with "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\SqlLocalDB.exe" start "MSSQLLocalDB"
2. Read the "Instance pipe name" from "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\SqlLocalDB.exe" info "MSSQLLocalDB" (e.g., \\.\pipe\LOCALDB#1B9DCF1E\tsql\query
3. Start HeidiSQL with "C:\Program Files\HeidiSQL\heidisql.exe --nettype=3 --host="\\.\pipe\LOCALDB#1B9DCF1E\tsql\query" --winauth=1"

I use a Bash script that automates all that, and it goes roughly like this:

shopt -s expand_aliases
alias __sqllocaldb='/c/Program\ Files/Microsoft\ SQL\ Server/120/Tools/Binn/SqlLocalDB.exe'
alias __heidisql='/c/Program\ Files/HeidiSQL/heidisql.exe'
__sqllocaldb start  "Development"
hostname=$(__sqllocaldb info  "Development" | tail -1 | sed 's/\(Instance pipe name: np:\)//')
__heidisql --nettype=3 --host="$hostname" --winauth=1