Cannot connect to MsSQL created from SqlLocalDB.exe

[expired user #7001]'s profile image [expired user #7001] posted 11 years ago in General Permalink
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:
(localdb)\v11.0
[COMPUTER_NAME](localdb)\v11.0

np:\\.\pipe\LOCALDB#AF54E762\tsql\query
[COMPUTER_NAME]\np:\\.\pipe\LOCALDB#AF54E762\tsql\query

.\pipe\LOCALDB#AF54E762\tsql\query
[COMPUTER_NAME]\.\pipe\LOCALDB#AF54E762\tsql\query

(localdb)\[DB_NAME]
[COMPUTER_NAME]\(localdb)\[DB_NAME]

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.

Marco
jfalch's profile image jfalch posted 11 years ago Permalink
the data to enter into heidiql sesionĀ“s hostname/ip used to be

hostname\instancename

where hostname is the name of the machine that the server is running on (eg localhost or 127.0.0.1 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 ?
[expired user #7001]'s profile image [expired user #7001] posted 11 years ago Permalink
Hay!

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's profile image jfalch posted 11 years ago Permalink
to create a db, you must have been connected to the server. how did you do this ?
jfalch's profile image jfalch posted 11 years ago Permalink
ie what program did you use to create the db ?
jfalch's profile image jfalch posted 11 years ago Permalink
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.
[expired user #7001]'s profile image [expired user #7001] posted 11 years ago Permalink
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's profile image jfalch posted 11 years ago Permalink
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.
[expired user #7001]'s profile image [expired user #7001] posted 11 years ago Permalink
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's profile image jfalch posted 11 years ago Permalink
"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's profile image ansgar posted 11 years ago Permalink
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.
[expired user #7001]'s profile image [expired user #7001] posted 11 years ago Permalink
Hum, I just noticed that "SQL Server Native Client" is for SQL server 2008 and I installed SQL server express 2012
[expired user #7001]'s profile image [expired user #7001] posted 11 years ago Permalink
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
[expired user #8349]'s profile image [expired user #8349] posted 10 years ago Permalink
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's profile image ansgar posted 10 years ago Permalink
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.
[expired user #8349]'s profile image [expired user #8349] posted 10 years ago Permalink
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's profile image ansgar posted 10 years ago Permalink
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.
[expired user #8349]'s profile image [expired user #8349] posted 9 years ago Permalink
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

[expired user #10002]'s profile image [expired user #10002] posted 8 years ago Permalink

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

[expired user #8826]'s profile image [expired user #8826] posted 7 years ago Permalink

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

[expired user #10002]'s profile image [expired user #10002] posted 7 years ago Permalink

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

Cheers!

[expired user #8826]'s profile image [expired user #8826] posted 7 years ago Permalink

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.