Cannot connect to MsSQL created from SqlLocalDB.exe

NaturalBornCamper posted 4 years ago in General
Hello, I have been on this for 5 hours now and I almost lost a full day of work with nothing accomplished yet, any help would be appreciated on how to connect to a MsSQL server.

I Installed MsSQL with SqlLocalDB.msi from their website, followed their tutorial and successfully create a database and started the instance but I cannot connect to it at all. I'm new to MsSQL and this is very frustrating, before coding anything with php, I want to connect with Heidi but even that simple step I cannot do.

I tried all these hostnames/IP:




And for each one of them, ticking the box "Use Windows authentification", putting just my windows username, with and without password, blank fields and the "Prompt for credentials" box.

jfalch posted 4 years ago
the data to enter into heidiql sesionĀ“s hostname/ip used to be


where hostname is the name of the machine that the server is running on (eg localhost or if you installed it locally on your machine).

instancename in earlier version used to be SQLEXPRESS ; some pointers how to find it out are here (second post, "In a simple way").

dbname or anything with \pipe is never part of this.

I have no real idea what authentication data to use; in earlier version, server installation used to create a single user named "sa" (enter without the quotes), and asked for a password to be specified for sa during installation.
since you said "followed their tutorial and successfully create a database" it sounda as if you have been able to connect to MSSQL server somehow at least (MS client?); did you enter a username and password there ?
NaturalBornCamper posted 4 years ago

Nope, the tutorial stopped after having created a db and getting its info with the command "sqlLocalDB info". I did not find anything else after this, I tried to look for how to connect on google, finding other tutorials but got nothing. Did not even try with php as I have no idea what is the username/password/server, MS gives me nothing, no feedback except the name pipe.
jfalch posted 4 years ago
to create a db, you must have been connected to the server. how did you do this ?
jfalch posted 4 years ago
ie what program did you use to create the db ?
jfalch posted 4 years ago
this here has more info. if you used sqllocaldb create, what instance name did you specify ? this would be the instance name to use with heidisql.

if you dont have sa, use sqllocaldb.exe info then sqlcmd as specified in the linked docs to create a db user.
NaturalBornCamper posted 4 years ago
Hahaha german documentation? My german is a little rusty!

I installed SqlLocalDB.msi

Then in console:
"SqlLocalDB create testdb"
"SqlLocalDB start testdb"
"sqllocaldb info testdb"

That's how I got the pipe name. I don't have sqlcmd, I think it is part of the whole 1GB MS sql server express. That's why I'm confused as how it's supposed to be working standalone with no configuration whatsoever
jfalch posted 4 years ago
hahaha ? never heard of google translate ? anyway, here it is again in en-US.

when you used "SqlLocalDB create testdb" then your instance name is testdb .

sqlcmd can also be had by installing the much smaller MS sql native client.
NaturalBornCamper posted 4 years ago
Ok I will try to install this thanks. And I should have told you, no need to repost the link, I already read that.

I tried installing Microsoft sql management studio and it cannot connect either Arrrg!! I hate microsoft, why is it always so complicated?!!
jfalch posted 4 years ago
"why is it always so complicated": that is intentional. MS has spawned a thriving industry of training and certification for its products; it is usually very difficult to use their products without paying for this.
ansgar posted 4 years ago
Hehe.. yes, also what I feel when using MS products, e.g. MSSL integration in HeidiSQL. In the end these complications break MS products at some point, similar to their unhappy Internet Explorer history.
NaturalBornCamper posted 4 years ago
Hum, I just noticed that "SQL Server Native Client" is for SQL server 2008 and I installed SQL server express 2012
NaturalBornCamper posted 4 years ago
Ok, noticed SQL Management studio was 2005, so I had to connect with the name pipe, but then it was too old and not compatible. So I had to download the whole 600MB 2012 version of management studio that include a ton of other microsoft crap.

Now I can connect to the db with 2012. Still not working with HeidiSQL, but it doesn't matter now as it's already too late for my system, microsoft has it now. You guys can still save yourselves!

Thanks for your help though
jhauser posted 3 years ago
I am still back where NaturalBornCamper was a year ago. Trying to connect to (localdb)\v11.0 (which replaces SQLEXPRESS) is a mystery.

Any suggestions welcome.
ansgar posted 3 years ago
So you are not able to find the right hostname or what?

In my local SQL server instance, it is "ANSGAR-PC\SQLEXPRESS", where "ANSGAR-PC" is my computer name.
jhauser posted 3 years ago
I have the developer version MSSQLSERVER installed and that creates a variable and temporary instance under this server name (localdb)\v11.0. This is an alternative to the free SQLEXPRESS edition. I can't for the life of me work out how to connect to this and there is no clear documentation that I can find on the web for Heidisql.

What I have subsequently done is install the free version of SQLEXPRESS as a second database instance and that works fine with the following setup:

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

I suspect that this has something to do with my installation of the MSSSQLSERVER and particularly the authentication method. Maybe there is a problem when that is set to SQL Server and Windows Authentication mode (rather than just Windows Authentication).

The other option is that (localdb)\v11.0 requires a completely different type of connection method

ansgar posted 3 years ago
Please look at your start menu > Microsoft SQL Server XYZ > Configuration Tools > SQL Server-Configurations-Manager

Then, collapse "SQL Server Network configuration" and click "Protocols". There I recall I had activated TCP/IP and named pipes previously.
jhauser posted 3 years ago
For reference it was a configuration issue. Probably a consequence of many separate installations and upgrades of SQL Server. I removed all copies from my PC and re-installed SQL with Windows Authentication only. I also nominated my windows user details as administrator of all services.

I can then login via heidisql using:

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

Based on my earlier trial I could also install an SQLEXPRESS instance alongside this and use:

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

I still haven't solved the mystery of (localdb)\v11.0 but I will keep digging

exalted posted 1 year ago

I tried to explain how to connect to LocalDB with HeidiSQL on Stack Overflow. For reference to my future-self and anyone else who may be interested, please read http:// stackoverflow.com/questions/37852239/unable-to-connect-to-localdb-with-heidisql/38722065#38722065

oliebol posted 3 months ago

Excuse me for bumping this old post, but while the info from the stackoverflow-thread linked by exalted here is really useful, it is a pity the pipe-name you have to use to connect is randomized and not persistent.

Personally I would love a new "network type" option named Microsoft SQL Server (LocalDB) and then you give it the localdb-name (so "v11.0" in above thread). HeidiSQL could then dynamically figuring out the right pipe-name by calling SqlLocalDB.exe. (See that stackoverflow question). I'd be fine if I would need to specify the location of the sqllocaldb-binary in some settings window somewhere once (since most wont have it anyway probably).

I don't know if such a feature would be considered since its probably only useful for a very small set of users. But I'd be soo happy with it :3

exalted posted 3 months ago

Hey oliebol, in the meantime you could try scripting it a bit: https://stackoverflow.com/a/38785844/11895.


oliebol posted 3 months ago

Yeah, I had hacked together this batch script in the meantime:

@echo off

REM start it, just in case it isnt
sqllocaldb s projectsv13

REM get pipe-name
FOR /f "tokens=2*delims=::" %%a IN ('sqllocaldb i projectsv13') DO (
  IF "%%a"==" np" SET "pipename=%%b"

REM start heidisql
start "" "D:\Software\HeidiSQL\heidisql.exe" --nettype=3 --host "%pipename%" --winauth=1

which does the job (for me obviously, since its all hardcoded) xD

Please login to leave a reply, or register at first.