The other day I switched to the dark side, as some of my data friends and colleagues call it, I’m using a Mac Book Pro now. This article might be the beginning of another series, but will definitely be a reflection of my first steps.

As I started this journey two months ago, I knew I needed a Windows VM because I’m addicted to Power BI. The reason for this requirement is simple - the full experience when developing reports is currently not available on the web. This includes data modeling, writing DAX, composing data visualizations using the custom Deneb visual, and other features.

Using Parallels makes creating a VM a breeze, I use Parallels Pro, because I want to assign more than 8GB of RAM to my virtual machine. Please be aware that you need a Windows license.

My favorite data source for Power BI datasets is Microsoft’s SQL Server, this is for many reasons. SQL Server led me to where I’m now, and I consider this a good place to rest for a while, but it’s also because I often encounter a SQL Server as a data source during my day life. Next to that, I still love writing SQL, turning data into insights.

I tend to “recreate” challenges that I encounter during the day in my own environment, meaning my own Power BI tenant, my Azure subscription. I do this because I can tackle the challenge from different angles and turn on/off properties without being blocked because I do not have to adhere to the “segregation of duties.” Of course, this concept makes sense in a professional environment. Still, in my lab situation, I want to deal with different challenges immediately instead of waiting for the proper privileges to arrive at an ad-hoc meeting.

Because I want to have a local instance of SQL Server, but SQL Server is not running on my Windows virtual machine on top of the ARM-based M2 Apple processors (Apple Silicone).

I consider spinning up a SQL Server Virtual Machine on Azure, I consider this closest to the local experience. The next image shows what I’m talking about:

Select wisely, as it will cost money 😊

Of course, you can stop the virtual machine whenever you want, do not forget to configure the Auto-shutdown feature. Sometimes I forget to shut down the virtual machine. Without the auto shut-down feature active, this can become costly.

Connecting to an on-premsises SQL Server database from Power BI desktop is easy, it’s only necessary to provide the name of the SQL Server, the name of the database, and authenticate - done. But connecting to a SQL Server running on a virtual machine in an Azure data center requires a little thinking and a little “trick”. The below diagram shows a diagram of how this works and what must be considered:

The next chapters will explain the four steps, and some of the challenges in more detail:

1. Connect to the virtual machine and create a database and install the on-premises gateway
2. Connect to the SQL Server / database from Power BI Desktop
3. Publish the pbix to the Power BI Workspace
4. Configure a scheduled refresh, most of the time I consider Power BI solutions incomplete when it’s not possible to connect to the data source from inside the Power BI Service

If you are familiar with data refresh and on-premises data sources, you will know that the on-premises data gateway is needed, because a SQL db running on a virtual machine in an Azure data center, must (maybe, can) be considered as an on-premises database.

I’m already familiar with connecting to windows machines using my iPad, I do this by using the “Microsoft Desktop App” on my iPad. For this reason, I installed the macos version on my mbp.

With the help of the Microsoft Remote Desktop app, it’s simple to connect to the VM, download the ContosoRetailDW sample database, restore the database, and it’s easy to install the Power BI on-premises gateway. I install the on-premises gateway on the same machine that is also hosting the SQL Server, I do this for simplicity. In real life, this is not a good idea unless the SQL Server is the only data source in your Power BI environment. In real life scenarios the recommendation is: install the gateway close to data but on a separate machine.

Connecting to the remote machine is simple. It’s achieved by using the public ip-address that comes with the virtual machine:

Please be aware that my Power BI Desktop client is running inside a virtual machine on top of macos. My machine is also not connected to a local network. For this reason, it requires a little thinking to simulate a “local” experience when connecting to the SQL Server database. When using the public-ip connecting with the SQL Server from Power BI Desktop is almost as simple as using the Microsoft Remote Desktop app, but I tend to use the name of sql server machine, instead of an ip. For this reason I use a little trick. I add the name and the public-ip to the hosts file. The hosts file is here:

I add this to the file:

1.2.3.4 NameOfTheServer

Basically, I’ ready to connect to the remote server from within Power BI Desktop using something like this:

But then this error message appears:

There is one essential aspect that must be considered when connecting to a virtual machine via the internet, this aspect is the security of your assets, here it’s the virtual machine/SQL Server.

For this reason, inbound connections (connections that want to connect to the VM) are blocked by default. This can be resolved quickly by adding a security in the networking section of the VM leveraging the Azure Portal:

Now, the connection from Power BI desktop works flawlessly.

This step does not differentiate from my earlier setup, I do not have to consider anything special.

This step, also does not require any special considerations. In step 1 the on-premises gateway was registered with the Power BI Service. Because the gateway is installed on the same VM that hosts the SQL Server instance, the SQL Server can be “mentioned” by name in the gateway connection configuration.

In the next article I will explain how leveraging the VM in combination with Azure Cloud App makes using the on-premises gateway superfluous and how the security can be tightened.

Thank you for reading!