Unable to connect to LocalDB with HeidiSQL

Question

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

Educational

LocalDB is an in-process database. If no process starts it, no-one can connect to it. Did you start it before trying to connect? – Colored

And did you try to connect using HeidiSQL's session manager, instead of using the limited command line syntax? – Annual

@Panagiotis Yes, I had the localdb service running every time I tried to connect. Anse: I tried connecting using HeidiSQL's session manager (used the "Use Windows Authentication option") but same result. – Educational

Answer 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://mcmap.net/q/492212/-heidisql-connection-to-ms-sql-server-localdb for the pipe name suggestion.

Pairoar

Great answer. So what is Sql Server Management Studio doing differently to avoid this?I'm assuming it can query the service directly to get the random hash. – Caracaraballo

Answer 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

Pairoar

Answer 3

Here is an AutoHotkey script that automates everything, from copying DB instance name to opening, pasting and connecting to the server.

(Adjust Sleep if it's too fast)

#NoEnv
SetWorkingDir %A_ScriptDir%
#Warn
CoordMode, Mouse, Window
SendMode Input
#SingleInstance Force
SetTitleMatchMode 2
SetTitleMatchMode Fast
DetectHiddenWindows On
DetectHiddenText On
#WinActivateForce
#NoTrayIcon
SetControlDelay 1
SetWinDelay 0
SetKeyDelay -1
SetMouseDelay -1
SetBatchLines -1
#Persistent


HeidiSQLConnectToSSMS:
batch := 
(LTrim
"sqllocaldb start MSSQLLocalDB
for /f ""tokens=3 delims=:"" `%`%i IN ('sqllocaldb info MSSQLLocalDB ^| findstr ""Instance pipe name:""') do set sqlConn=`%`%i
set /p =""`%sqlConn`%""<nul | clip"
)  ; batch
FileDelete, sqlConn.bat  ; sqlConn.bat
FileAppend, %batch%, sqlConn.bat  ; sqlConn.bat
RunWait, sqlConn.bat, , Hide  ; sqlConn.bat
Run, C:\Program Files\HeidiSQL\heidisql.exe  ; HeidiSQL
WinWait, Session manager ahk_class Tconnform ahk_exe heidisql.exe  ; HeidiSQL
Sleep, 333
WinActivate, Session manager ahk_class Tconnform ahk_exe heidisql.exe  ; HeidiSQL
Sleep, 333
ControlClick, TButton4, ahk_class Tconnform ahk_exe heidisql.exe,, Left, 1,  NA  ; New
Sleep, 100
ControlFocus, ComboBox1, ahk_class Tconnform ahk_exe heidisql.exe  ; Network Type
Sleep, 100
ControlSend, ComboBox1, {Home}, ahk_class Tconnform ahk_exe heidisql.exe  ; Select Microsoft SQL Server (named pipe)
Sleep, 100
Loop, 5  ; Select Microsoft SQL Server (named pipe)
{
    ControlSend, ComboBox1, {Down}, ahk_class Tconnform ahk_exe heidisql.exe  ; Select Microsoft SQL Server (named pipe)
    Sleep, 100
}
ControlClick, TCheckBox1, ahk_class Tconnform ahk_exe heidisql.exe,, Left, 1,  NA  ; Use Windows authentication
Sleep, 100
Loop, 2  ; Set Hostname / IP:
{
    Send, {Shift Down}{Tab}{Shift Up}  ; Set Hostname / IP:
    Sleep, 100
}
SendRaw, %CLIPBOARD%  ; Set Hostname / IP:
Sleep, 200
ControlClick, TButton7, ahk_class Tconnform ahk_exe heidisql.exe,, Left, 1,  NA  ; Save
Sleep, 100
Send, {Enter}  ; Connect
Sleep, 100
FileDelete, sqlConn.bat  ; sqlConn.bat
ExitApp  ; ExitApp
Sleep, 100
Return

Fetching