Cannot connect to MsSQL created from SqlLocalDB.exe
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
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 ?
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.
if you dont have sa, use sqllocaldb.exe info then sqlcmd as specified in the linked docs to create a db user.
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
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.
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
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
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
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
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
Hey oliebol, in the meantime you could try scripting it a bit: https://stackoverflow.com/a/38785844/11895.
Cheers!
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.