Excel: How to connect to a sqlserver localDB

Question

I have created a LocalDB that I can work with in MS sqlserver management studio 2014, linqpad, and visual studio 2013.

Here is the part of the "connection property" dialog in SSMS which shows the server name:

I would like to be able to connect to this database from within excel. The problem is that the "Data Connection Wizard" in Excel, fails to connect.

Here, I have typed in the same server name as was given from "connection properties" in SSMS....

And here is the error I get...

Questions:

• Can excel connect to a localDB at all? I know it CAN connect to sql-server databases. Is there some limitation about LocalDB that prevents this? I thought the whole point of LocalDB was to allow development without the effort of setting up a standalone database.

• Is there an alternative way to connect? Or does my server name require some annoying modification?

Solution 1

What a gratuitous P.I.T.A !

I was able to make it work by using the "Data Connection Wizard" following these steps....

1. Select "Other/Advanced" from Data connection Wizard, then hit "next". I thought that localDB as created by sqlserver express would go as "SQL Server". Apparently not! though I can't fathom why.

2. Select "SQL Server Native Client 11.0" as provider. OK, I had just NOT selected "SQL Server" in the previous tab. Also I happen to be running version 12 of sql server express and there is no "12" in the list-- perhaps it is strictly referring to client version, with the subtle implication that client 11 can connect to server 12? Yet another cognitive paper-cut.

3. Enter the same server name that works in SSMS or linqpad. Select Windows Integrated security. Test Connection now works and it is possible to select the database, and dump a table into excel.

Its not difficult to do this, but there doesn't seem to be any logical flow to it you have to flounder around until something clicks.

Solution 2

This is the connection string (SQLServer 2012 / Excel 2013):
Server=(LocalDB)\MSSQLLocalDB; Integrated Security=true ;AttachDbFileName={mdf file full path}

Oh on my PC, I had to use ;Trusted_Connection=Yes, it might not make any sense, since both my laptop and PC are running the EXACT same versions of SQLServer and Excel.

---

Edit

I can't reproduce the error (I even installed SQL Server 2014 on a VM and it still works). The only option you could add and might help you is Provider=SQLNCLI11; OR Provider=SQLNCLI12; you can find out what version is installed by running sqllocaldb v in command line.
That would produce something like: Microsoft SQL Server 2014 (12.0.2000.8)

Comments

[Angelo]

thanks, but it still gives an error. I had to remove your "server=" part to avoid getting "parseConnectParams()" in the error dialog. When I did that, it gave the same error as I got before.

[tbc]

@Angelo I updated my answer, please do till the result once you try.

[tbc]

Dear, you are connecting to SQL Server 2014, which comes with SQLNCLI12, and in your screenshots you selected 'Native Client 11', which is SQLNCLI11, and for somereason, you don't have the 'Native Client 12' that excel looks for 2014.