Excel: How to connect to a sqlserver localDB

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.

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)