Connecting SQL Server Express to Power BI using SQL Server authentication
Problem
You want to test some things in Power BI or, like me, you are studying for your PL-300 certification
You have SQL Server Express installed locally, but cannot get Power BI to connect to it using server or db credentials (no Active Directory available)
Everything in SQL Server Management Studio is working, the SQL Server user has been created in the DB and has been given db_datawriter and db_datareader permissions to the database
But Power BI will not load that damn AdventureWorks data using your creating SQL Server credentials
Second, check that the “Named Pipes” and TCP/IP protocols are enabled for your SQL Express install by using the SQL Server Configuration Manager in the MMC
Of course, make sure that the server services are started and working but you probably saw this in SSMS
Also of course who have restarted the SQL Server service after you created the local DB accounts, correct?
The real trick
I was able to get this to work by creating an Alias for the SQL Server; even localhost would not work out of the box with TCP/IP and Named Pipes activated
BUT it appears that SQL Server Express does not allow you to create aliases in SQL Server Configuration Manager, as I assume this is a licensing feature that Microsoft uses to encourage you to buy a real version of SQL Server if you are getting into “advanced” networking
How then to create the alias for your DB: there is a secret a tool buried in the system32 folder that will allow you to create aliases in SQL Express
“C:\Windows\System32\cliconfg.exe” is an older SQL client configuration tool that is part of the whole ODBC suite of tools built into Windows
Here you are able to create an alias and with the alias of “localhost” I was finally able to connect to my SQL Express instance using SQL Authentication instead of Windows authentication