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
Databases are created, SQL Server DB user is created and assigned rights to the AdventureWorks DB, SQL Server services are up and running, TCP/IP is enabled, but Power BI still will not connect with SQL Server Express unless you use Windows authentication

Solution

The Basics

  • First as listed above in the screenshots, make sure that you have correctly created a DB user with read rights to the database in question, AdventureWorksDW2020 – here is a nice guide for how to do this from Abhith Rajan
  • 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
Grayed out!?!
  • 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

Leave a Reply

Your email address will not be published. Required fields are marked *