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

Loading

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.

Personal Mode
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 a better understanding.

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 shows two options.

Personal Mode
Recommended Mode

Here, we will choose the Recommended Mode.

Next

Choose Recommended Mode > click Next.

Step 4. It will prompt a message like this.

Prompt

Step 5. Choose a path for installation.

Path

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

Sign IN

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

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.

Configure

Step 9. It shows the message “The Gateway is Online and ready to use”

Status

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

Manage Gateways

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

Click on > Add data source to use the Gateway.

Data Source

Step 5. Add the following information and save the changes

Data Source Name: Any appropriate name for the Data Source.
Data Source Type: Select SQL Server from the 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 them to the Users section.

User section

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

Administrative Section

Step 8. Now, From. Gateway Connection > Select Use a data gateway.

Connection

Step 9. Set a Schedule Refresh for your data source.

Schedule Refresh

Conclusion

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