Configure Data Gateway With SQL Server Database Using Recommended Or Enterprise Mode

Overview

In this article, we will discuss how we can configure Data Gateway for On-Premise SQL Server Database for live data connection using Recommended Mode.

Power BI provides the functionality to configure Data Gateway using

1. Personal Mode
2. Recommended Mode

We have already discussed the difference between Personal Mode and Recommended Mode in my previous article.

In this article, we will configure the data gateway using Recommended Mode. If you want to learn the installation in Personal Mode – check this article.

Before we start the installation of Data Gateway, visit my previous blogs for better understanding.

1. Overview of Data Gateway in Power BI

This article covers two parts.

• Installation of Gateway for On-Premise Environment
• Configuration of Gateway using Power BI Online

Installation of Gateway for On-Premise Environment

Step 1

Download setup using URL -

http://go.microsoft.com/fwlink/?LinkID=820925

Step 2

Right-click on Setup > Run as Administrator.

Step 3

It is showing two options.

1. Personal Mode
2. Recommended Mode

Here, we will choose Recommended Mode.

Choose Recommended Mode > click Next

Step 4

It will prompt a message like this.

Step 5

Choose a path for installation.

Step 6

Enter the Power BI work email address and click "Sign In".

Step 7

Once authentication is successful, it will ask to Register a Gateway for On-Premise Data Gateway.

Step 8

Now, let’s fill following information to configure a gateway.

New On-Premise Data Gateway Name = Name of a Gateway which you want to create.

Recovery Key = any 8-character password to recover a Gateway.

Step 9

It shows a message “The Gateway is Online and ready to use”

Configuration of Data Gateway with Power BI Online

Step 1

Open Power BI Online Account.

Step 2

From Dataset Select your Data Source > Click Schedule Refresh

Step 3

From Gateway Connection > Click on Manage Gateways

Step 4

From Gateway Cluster > Select the Gateway that we have installed.

Click on > Add data source to use the Gateway.

Step 5

Add the following information and save changes

• Data Source Name = Any appropriate name for Data Source
• Data Source Type = Select SQL Server from dropdown
• Server Name = Name of SQL Server
• Database Name = Name of a database
• Authentication Method = Basic for SQL Server Authentication
• Username and Password

Step 6

If we want to add Users to use this data source add then to Users section.

Step 7

If we want to share Gateway with other users > Add them to Administrative Section.

Step 8

Now, From Gateway Connection > Select Use a data gateway

Step 9

Set a Schedule Refresh for your data source.

Conclusion

This is how we can configure Gateway for our On-Premise SQL Server Database using Recommended Mode.