Cannot connect to LocalDB with Power BI Desktop

[kennykhor]

I manage to connect to LocalDB with SSMS as below, but encountered error with the same config applied in Power BI. Looking forward for a solution to connect to LocalDB

SSMS Config:

SQL Server Express Version:

Power BI Config and Error prompted:

[RenierP]

I have a similar issue. Just started after working fine. Did not update PowerBI or SQL Server.

There seems to be an identity issue but I am not sure what or where. If I first connect with SSMS the connection works fine and I can run queries in SSMS. However if I then try and connect to the same database from PowerBI I get the following error.

DataSource.Error: Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Error occurred during LocalDB instance startup: SQL Server process failed to start.

 If I disconnect SSMS and kill the sqlservr.exe process associated with my windows account then the query that has just failed in PowerBI works perfectly. However, if I now try and connect again with SSMS, it fails to connect with a similar error message

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Error occurred during LocalDB instance startup: SQL Server process failed to start.
) (Microsoft SQL Server, Error: -1983577846)

My deduction from this is that the two applications are somehow running as two different verions of my Windows Identity (don't ask me how - and note that the username reported in Task Manager is identical) so that when the other connects, it does not recognise the previous connection's process and rather tries to start a new process, which then fails to start as per the error messages.

Has anyone come across this? Need a resolution urgently as I need to get a report out, but PowerBI is not build for developing and testing native sql queries

[GarethWatson]

I appreciate this issue is quite old but this is the first hit when searching for PowerBI and LocalDB.

I had this issue and managed to resolve it by downloading the latest SQL Server OBDC drivers from Microsoft.  With those installed I could use my EntraID (the new Azure AD thing) to auth against the DB.

I got this hooked up to PowerBI by creating a System Data Source and using my EntraID to authenticate.  Its called Integrated EntraID.... on the long list of authentication options.

Then I could use the ODBC connector in PowerBI and pick that data source.

For reference I am using the V18 version of the SQL Server ODBC driver.

Hope this helps someone.

[kennykhor]

It turns up that SQL Express couldn't accept connection from both SSMS and Power BI. I have to restart the SQL Express Instance, and connect with Power BI alone, without having SSMS running and connect to the same instance. It's a weird behavior, as the SQL Express limit is 10 connections per PC.

[v-jianboli-msft]

Hi @kennykhor ,

To connect to a shared instance of LocalDB add .\ (dot + backslash) to the connection string to reference the namespace reserved for shared instances. For example, to connect to a shared instance of LocalDB named AppData use a connection string such as (localdb)\.\AppData as part of the connection string. A user connecting to a shared instance of LocalDB that they do not own must have a Windows Authentication or SQL Server Authentication login.

So, Please try (localdb)\.\MSSQLLocalDB

If this doesn't work, try to use domain\username to replace (localdb).

For example: use .\username or local:\username to replace  (localdb).

Besides, you can use whoami in windows commands to findout your username

For more details, please refer to:

How to connect and use Microsoft SQL Server Express LocalDB

SQL Server 2012 Express LocalDB  

whoami | Microsoft Docs 

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@kennykhor , Try

Locahost\MSSQLLocalDB

or

<Mahine Name>\MSSQLLocalDB 

[kennykhor]

@amitchandak , thanks but the problem remains.