Connect Azure SQL Database to Power BI Desktop

Configuring Connections on Power BI- Full Tutorial

Microsoft Azure SQL Server | Image by Author

Hello Guys!🌞 Let's get started 🏃‍♂️

Table of Contents:

1. Create SQL Database and Server Instance on Azure
2. Configuring connections on the Power BI
3. SQL Authentication
4. Possible errors while connecting

Step-1: Create SQL Database and Server on Azure

• First, you need to log in to your Azure portal(https://portal.azure.com).
• Once you are logged in, type SQL database on the search bar and then click on SQL Databases. (Alternative: You can also click on SQL databases on the left panel under favorite section)

Image by Author

• Now click on the Create SQL Database button or click on +Add
• Specify the following credentials:

1. Resource group(Create new if you haven’t got any)
2. DataBase Name: unique name
3. Server(Create new server and enter the server name, username, and password)
4. Click On Review+connect

• Wait for a minute until your resources are being deployed

Step-2: Configuring connections on power BI

• Open Power BI desktop application and then click on Get Data →SQL Server
• On your Azure portal goto Resources →SQL Database(you can also click on SQL databases on your favorites section or go to your dashboard section)

Image by Author

• On the left plane under the settings, section click on the connection string

Image by Author

• Under the JDBC tab, you will find this kind of string:

jdbc:sqlserver://test-server-powerbi.database.windows.net:1433;database=test-powerbi;user=harsh@test-server-powerbi;password={your_password_here};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

Server Name : test-server-powerbi.database.windows.net (from above string)

Database Name : test-powerbi(from above string)

• Enter these credential under Power BI Desktop and click OK

Power BI Configuration | Image by Author

Step-3: SQL Authentication

• Now you need to enter your authentication details i.e Username and password

Username →harsh@test-server-powerbi(from JDBC string — look above)

Password →(enter your password)

Image by Author

• Now click on Connect to successfully connect your SQL Database to Power BI.

Possible errors

• Make sure your Azure SQL database status is online by clicking on your database instance.
• Unable to connect — firewall problem(Some of you may encounter this error)

Image by Author

In order to solve this error, you need to configure the firewall of the Azure SQL Server where your database resides.

Remember, you need to configure the Firewall and manage the IP addresses in SQL Server, not the SQL database❗

Once you have selected the SQL Server,

• Click the Settings/Security -> Firewall
• You will be requested to add a new Rule.
• Add a name and enter the IP address(or the range) that will access the database. note: Azure automatically detects your current IP.
• Lastly, click on Save and wait for 2 mins. Then click on retry on the Power BI desktop application. Now you will be able to see tables

👉Thank you for reading this story ❤.