How to Create a SQL Login Credentials for Power BI Data Gateway (SQL Server)

How to Create a SQL Login for Power BI Data Gateway (SQL Server)

How to Create a SQL Login for Power BI Data Gateway (SQL Server)

When you're connecting Power BI Service to an on-premises SQL Server database using the Data Gateway, it's crucial to have a dedicated SQL Login for the gateway. This provides a secure and manageable way for Power BI to access your data without relying on Windows authentication (which can be complex in cloud-to-on-prem scenarios) or using highly privileged accounts.

Let's walk through the process of creating a SQL Login in SQL Server Management Studio (SSMS) specifically for your Power BI Data Gateway.

🔧 Step-by-Step in SSMS (SQL Server Management Studio)

Follow these instructions carefully within SQL Server Management Studio to create the new SQL Login:

  • Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
  • In the Object Explorer (usually on the left), expand your server instance.
  • Expand the Security folder.
  • Expand the Logins folder.
  • Right-click on Logins and select New Login....
  • In the "Login - New" dialog box that appears:
    • Under the "General" page:
      • Select SQL Server Authentication. This is important as the Gateway will use a username and password.
      • Enter a Login name. A common practice is to use a descriptive name like pbi_gateway_user or powerbi_reader.
      • Set a strong Password and confirm it.
      • Optional: For development or specific controlled environments, you might uncheck "Enforce password policy" and "Enforce password expiration" if you want to manage the password manually. However, for production, it's generally recommended to keep password policies enforced for stronger security.
    • Go to the User Mapping tab (on the left pane).
      • In the "Users mapped to this login" section, check the box next to the database(s) that your Power BI reports will access.
      • In the "Database role membership for: [your database]" section at the bottom, ensure you assign the appropriate role. For read-only access (which is usually sufficient for Power BI reports), select db_datareader. This role grants permission to read all data from all user tables and views in the database. Avoid giving higher privileges like db_owner unless absolutely necessary and understood.
    • Click OK to create the new SQL Login.

✅ Now you have a SQL Login ready for your Power BI Data Gateway!

Login ID: pbi_gateway_user (or whatever name you chose)
Password: Your chosen password

You will use these credentials when configuring the data source within the Power BI Service's "Manage Gateways" section, ensuring your Power BI reports can securely refresh data from your on-premises SQL Server.

Raushan Ranjan

Microsoft Certified Trainer

.NET | Azure | Power Platform | WPF | Qt/QML Developer

Power BI Developer | Data Analyst

📞 +91 82858 62455
🌐 raushanranjan.azurewebsites.net
🔗 linkedin.com/in/raushanranjan

Comments

Post a Comment

Popular posts from this blog

Module 1 - Lesson 1: Getting Started with Power BI

Image
Unlocking Insights: Your Journey into Data Analysis with Power BI Lesson 1: Getting Started with Power BI Learn with Raushan Data Analysis with Power BI Hey data enthusiasts! Ever wondered how raw numbers transform into compelling stories and drive impactful decisions? Welcome to the world of data analysis, and your trusty companion on this journey is none other than Power BI! In this first lesson, we'll lay the groundwork, understanding why data analysis is crucial, the different flavors of analysis, the vital role of a Data Analyst, and how Power BI fits into their workflow. Why Data Analysis? Unearthing the 'Why' Behind the Numbers Imagine a vast ocean of data generated every second – from sales figures and website clicks to sensor readings and customer feedback. Without analysis, this data is like a treasure chest with...
Read more

Power BI Advanced learning

RR Education: Power BI Advanced Power BI Advanced Learning Learn with Raushan Welcome to the new series of blogs on Power BI Advanced! In this series, we'll delve into more powerful tools and techniques that will take your data analysis and reporting skills to the next level. Explore the Modules below to learn more about each area. Module 1: Begin with Power BI This module covers the fundamentals of using Power BI. You'll learn why data analysis is key, how Data Analysts use Power BI, and the essentials of connecting to diverse data sources via different methods (Import, DirectQuery, Live Connection). We'll also address common data import errors. Lesson 1       Lesson 2      ...
Read more

Module 1 - Lesson 2: Getting Data from Multiple Sources

Image
Module 1 - Lesson 2: Mastering Data Acquisition in Power BI Lesson 2: Getting Data from Multiple Sources Learn with Raushan Getting Data in Power BI Welcome back, aspiring data analysts! In our previous lesson, we explored the fundamentals of data analysis and the role of Power BI. Now, let's get our hands dirty with the crucial first step: bringing data into Power BI from various sources. Think of Power BI as your central hub, ready to ingest data from all corners of your business or domain. Understanding Data Sources: Where Does Our Information Live? Data can reside in a multitude of places. Understanding these sources is the first step towards connecting with them. Common data sources include: Databases: Structured collections of data organized for easy access and management (e.g., SQL Server, Oracle, MySQL)...
Read more

Module 1 - Lesson 3: Resolve Data Import Errors in Power BI

Image
Module 1 - Lesson 3: Navigating and Resolving Data Import Errors in Power BI Lesson 3: Resolve Data Import Errors Learn with Raushan Navigating and Resolving Data Import Errors in Power BI Welcome back! You've learned about connecting to various data sources in Power BI. However, the journey of data integration isn't always smooth. Sometimes, you might encounter errors during the data import process. Fear not! This lesson will equip you with the knowledge and techniques to identify and resolve common data import errors in Power BI. Web Authentication and Loading Tables: The Web Connection Hurdle Connecting to data from the web can sometimes present authentication challenges. Websites often require logins or specific authentication methods to access their data. Web Authentication Technical Definition: Web authentication is the process of verifying the i...
Read more

Module 2 - Lesson 1: Introduction to Power Query Editor

Image
Module 2 - Lesson 1: Introduction to Power Query Editor Lesson 1: Introduction to Power Query Editor Power Query Editor in Power BI Welcome back, data wranglers! We've successfully connected to our data sources and navigated potential import errors in module 1. Now, it's time to delve into the heart of data preparation in Power BI: the Power Query Editor. Think of this as your data laboratory, where you can clean, shape, and transform raw data into a polished format ready for insightful analysis and stunning visualizations. What is the Power Query Editor? Your Data Transformation Hub The Power Query Editor is a powerful tool built into Power BI Desktop that allows you to perform Extract, Transform, Load (ETL) processes on your data. It provides a user-friendly interface with a wide array of functions to manipulate data without needing to ...
Read more