distal-attribute
distal-attribute
distal-attribute
distal-attribute

Cannot connect to MsSQL created from SqlLocalDB.exe

NaturalBornCamper posted 2 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:
(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 posted 2 years ago
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 ?
NaturalBornCamper posted 2 years ago
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 posted 2 years ago
to create a db, you must have been connected to the server. how did you do this ?
jfalch posted 2 years ago
ie what program did you use to create the db ?

jfalch posted 2 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 2 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 2 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 2 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 2 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 2 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 2 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 2 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 months 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 months 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 months 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 months 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 months 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

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