Qlik Sense Business vs. Azure SQL Database

What are we doing?

In today’s blog, I am going demonstrate how to connect to an Azure SQL Database from Qlik Sense Business, for the first time

Scenario

The scenario we have today is a customer call Bobs Room Bookings, who has an Azure SQL Database which holds the data for their ERP system. They want to be able to use Qlik Sense Business to import and interrogate all of their data.

Steps

So, lets go…

1) Log into Qlik Sense Business, and either create or open an App

2) Browse to the Data Load Editor

3) From the right hand menu, click “Create new connection”

4) Search for SQL, and you will notice a variety of SQL like connections. The one we are interested in is “Azure SQL Database”

5) Enter the connection details and username/password for your database,

Server: carlh.database.windows.net
Database: carlh1
Username: Azure
Password: IAmNotTellingYou

6) Click “Test Connection”

Uh-oh…

Error Message “Please check the values for Username, Password Host or other Properties”
General Error. Cannot open the host ‘carlh’ requested by this login. Client with IP Address “xxx” is not allowed to access the server.

Oh dear, quite a grizzly error message has appeared.

Problems problems problems…

So, what does this mean…?

Well, it means that the Qlik Sense Business tenant (i.e. <yourname>.<your region>.qlikcloud.com) does not have access to your Azure tenant. Even though Qlik Sense Business and Azure are both Cloud and SaaS solutions, there is some configuration required to allow both parties to talk to one another.

It should be as simple as pie to get two Cloud solutions to talk, but, as we are ‘technically’ accessing a data source from a private network, the private network being Azure, we need to allow both parties to talk in a safe and secure manner.

This diagram explains the problem – we are getting a Connection Failed error, as the IP Address from Qlik Business Cloud is not in the Azure Firewall.

Don’t panic, its quite a simple fix!

Firstly, we need to identify which Region your Qlik Sense Business tenant resides in, and grab the IP addresses required for Azure. See the Qlik Help guide here > https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/LoadData/connect-data-sources.htm

My tenant is carlh.eu.qlikcloud.com, so I need the 3 IP addresses listed in second section

With this information in hand, we need to head over to the Azure tenant where your Azure SQL Database lives. If you login to your Azure Portal (https://portal.azure.com/), and find the SQL Server in question – see below for what mine looks like

From the top left, search for Firewall and select “Firewalls and virtual networks”

Enter the three IP addresses from above into the Client IP Address section half way down the page. Enter a friendly name, so future you doesn’t curse past you too much. For simplicity, I’ve called my three rules Qlik Business 1, 2 and 3.

Once done, click Save.

If you jump back to Qlik Business Cloud, and click on Test Connection again, you should see…

“Connection succeeded” 😊

At this point, you can click on Create, and you are ready to import data from your Azure SQL Database.

In Summary

We have gone through the steps to setup connectivity between Qlik Sense Business and an Azure SQL Database.

An Azure SQL Server can have multiple Databases, however, you only need to configure the Azure Firewall for the first time you try and import data from a particular Azure SQL Server.

Have you struggled with this? Let me know in the comments.