Can connect to SQL Server but Databases are not visible

[DavidJLitttle]

Afternoon,

Im trying to connect to a local SQL Server Express Database. I can connect to the local server fine, but PowerBI doesnt list any databses available under the server ? I cant work out why

[Anonymous]

When you don't specify a database (the optional field below the server address) PowerBI tries to connect to a master system DB. It's best practice to specify the database. If you have the correct permissions you will be able to connect and explore tables. The case with the Databases not showing is likely an issue with the databases. I had a user who could only preview 4 out of 5 DBs. Upon investigation I found one DB was stuck in CREATING state, so it's likely the power query connector can't recognize it's online and available (just a theory but it's the only major difference between the 5 dbs). No sure how it works in express (I'm in Azure SQL), but SELECT * FROM sys.databases in master will show you state. You may need to back up the db turn everything off and turn it on again (so helpful). 

@DavidJLitttle 

[vanessafvg]

@DavidJLitttle  how are you connecting via your active directory?

and if you go directly to sql can you see it?

[DavidJLitttle]

---

@vanessafvg wrote:

@DavidJLitttle  how are you connecting via your active directory?

and if you go directly to sql can you see it?

---

Hi, if i connect directly via SSMS then i can see all DB's and if i connect via VB.Net connection string then i can happily populate the DB. Its just PowerBI cant see any of the 3 local DB's

[vanessafvg]

@DavidJLitttle  dont know if this thread helps

https://community.powerbi.com/t5/Desktop/Limitations-on-Power-BI-with-SQL-Server-Express/td-p/73131

are you using direct query?  what version are you on

[DavidJLitttle]

Hi, Im using SQL Server Express 2017 and SSMS v17 and im using Import rather than direct query. but neither work. The SQL login connects fine, but the Database list is blank

[TeigeGao]

Hi @DavidJLitttle ,

First, we need to check if you have used the wrong server name, the server name "(LocalDb)\MSSQLLocalDB" is not a standard SQL Server instance, it is a localdb. 

Then we need to check if these three databases are custom databases, the system database will not be displayed in the PowerBI list.

After that, we need to check if the SQL Server login you are using have the permission to see these databases. Finally, we can use the SQL Server Profiler to check what's happened during this process.

Best Regards,

Teige

[DavidJLitttle]

Morning, What would be the correct ServerName ? PowerBI succesfully connects to the server instance and gets as far as where it should show me the list of databases, there just aren;t any. Also the login/user is windows authentication and the same credential i use to connect in SSMS and VB.Net and both of these applications have visibility of the databases. Also in SSMS the User mapping for the Windows credential is set to dbo for the database in question.