{"id":4028,"date":"2022-03-01T19:39:40","date_gmt":"2022-03-02T03:39:40","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=4028"},"modified":"2022-03-01T19:39:43","modified_gmt":"2022-03-02T03:39:43","slug":"how-to-add-or-remove-a-gateway-data-source-from-powerbi-com","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=4028","title":{"rendered":"How to Add or Remove a Gateway Data Source from Powerbi.com"},"content":{"rendered":"\n<p>We&#8217;ve split the on-premises data gateway docs into&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/service-gateway-onprem\">content that&#8217;s specific to Power BI<\/a>&nbsp;and&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/data-integration\/gateway\/service-gateway-onprem\">general content that applies to all services<\/a>&nbsp;that the gateway supports. You&#8217;re currently in the Power BI content. To provide feedback on this article, or the overall gateway docs experience, scroll to the bottom of the article.<\/p>\n\n\n\n<p>Power BI supports many&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/power-bi-data-sources\">on-premises data sources<\/a>, and each has its own requirements. A gateway can be used for a single data source or multiple data sources. For this example, we show you how to add SQL Server as a data source. The steps are similar for other data sources.<\/p>\n\n\n\n<p>Most data sources management operations can be performed by using APIs as well. For more information, see&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/rest\/api\/power-bi\/gateways\">REST APIs (Gateways)<\/a>.<\/p>\n\n\n\n<p>If you don&#8217;t have a gateway installed yet, see&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/data-integration\/gateway\/service-gateway-install\">Install an on-premises data gateway<\/a>&nbsp;to get started.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"add-a-data-source\"><a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/service-gateway-data-sources#add-a-data-source\"><\/a>Add a data source<\/h2>\n\n\n\n<ol class=\"wp-block-list\"><li>From the page header in the Power BI service, select&nbsp;<strong>Settings<\/strong>&nbsp;<img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/media\/service-gateway-data-sources\/icon-gear.png\" alt=\"Settings gear icon\">&nbsp;&gt;&nbsp;<strong>Manage gateways<\/strong>.<img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/media\/service-gateway-data-sources\/manage-gateways.png\" alt=\"Manage gateways\"><\/li><li>Select a gateway and then select&nbsp;<strong>Add data source<\/strong>. You can select the header text&nbsp;<strong>ADD DATA SOURCE<\/strong>&nbsp;or hover your cursor next to the gateway entry to reveal the more options menu.<img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/media\/service-gateway-data-sources\/add-data-source.png\" alt=\"Add data source\"><\/li><li>Assign a name to your data source, then select the&nbsp;<strong>Data Source Type<\/strong>. In this example, we&#8217;ll choose SQL Server.<img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/media\/service-gateway-data-sources\/select-sql-server.png\" alt=\"Select SQL Server\"><\/li><li>Enter information about the data source. For SQL Server, provide the&nbsp;<strong>Server<\/strong>&nbsp;and&nbsp;<strong>Database<\/strong>.<img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/media\/service-gateway-data-sources\/data-source-settings.png\" alt=\"Data source settings\"><\/li><li>Select an&nbsp;<strong>Authentication Method<\/strong>&nbsp;to use when connecting to the data source. For SQL Server, choose&nbsp;<strong>Windows<\/strong>&nbsp;or&nbsp;<strong>Basic<\/strong>&nbsp;(SQL Authentication). Enter the credentials for your data source.<img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/media\/service-gateway-data-sources\/basic-auth.png\" alt=\"Basic authentication settings.\">&nbsp;NoteIf the selected authentication method is OAuth:<ul><li>Any query that runs longer than the OAuth token expiration policy may fail.<\/li><li>Cross-tenant AAD accounts are not supported If the selected authentication method is Windows:<\/li><li>Make sure that account has access on the machine. If not sure, make sure to add NT-AUTHORITY\\Authenticated Users (S-1-5-11) to the local machine \u201cUsers\u201d group.<\/li><\/ul><\/li><li>Under&nbsp;<strong>Advanced settings<\/strong>, you could configure&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/service-gateway-sso-overview\">Single Sign-On (SSO)<\/a>&nbsp;for your data source.<img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/media\/service-gateway-data-sources\/advanced-settings-02.png\" alt=\"Screenshot of advanced settings. \">You can either configure&nbsp;<strong>Use SSO via Kerberos for DirectQuery queries<\/strong>,&nbsp;<strong>Use SSO via Kerberos for DirectQuery And Import queries<\/strong>&nbsp;or&nbsp;<strong>Use SSO via Azure AD for DirectQuery queries<\/strong>&nbsp;for DirectQuery-based reports and&nbsp;<strong>Use SSO via Kerberos for DirectQuery And Import queries<\/strong>&nbsp;for refresh-based reports.If you use&nbsp;<strong>Use SSO via Kerberos for DirectQuery queries<\/strong>&nbsp;and use this data source for a DirectQuery-based report, it will use the credentials of the user that signs in to the Power BI service. For a refresh-based report, it will use the credentials that you enter in the&nbsp;<strong>Username<\/strong>&nbsp;and&nbsp;<strong>Password<\/strong>&nbsp;fields and the&nbsp;<strong>Authentication<\/strong>&nbsp;method chosen.When you use&nbsp;<strong>Use SSO via Kerberos for DirectQuery And Import queries<\/strong>, you don&#8217;t need to provide any credentials. If this data source is used for DirectQuery-based reports, it will use the user that&#8217;s mapped to the (Azure) Active Directory user that signs in to the Power BI service. For a refresh-based report, it will use the dataset owner&#8217;s security context.For more information on&nbsp;<strong>Use SSO via Kerberos for DirectQuery queries<\/strong>&nbsp;or&nbsp;<strong>Use SSO via Kerberos for DirectQuery And Import queries<\/strong>, see&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/service-gateway-sso-overview\">Overview of single sign-on (SSO) for gateways in Power BI<\/a>.If you use&nbsp;<strong>Use SSO via Azure AD for DirectQuery queries<\/strong>&nbsp;and use this data source for a DirectQuery-based report, it will use the Azure AD token of the user who signs into the Power BI service. For a refresh-based report, it will use the credentials that you enter in the&nbsp;<strong>Username<\/strong>&nbsp;and&nbsp;<strong>Password<\/strong>&nbsp;fields and the&nbsp;<strong>Authentication<\/strong>&nbsp;method chosen. The&nbsp;<strong>Use SSO via Azure AD for DirectQuery queries<\/strong>&nbsp;option will be available only if the tenant admin allows Azure AD SSO via the on-premises data gateway and for the following data sources:<ul><li>SQL Server<\/li><li>Azure Data Explorer<\/li><li>Snowflake<\/li><\/ul>For more information on&nbsp;<strong>Use SSO via Azure AD for DirectQuery queries<\/strong>&nbsp;please see&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/admin\/service-admin-portal#azure-ad-single-sign-on-sso-for-gateway\">Azure AD Single Sign-On (SSO) for Gateway<\/a>.&nbsp;NoteSSO for Import Queries is available only for the list of SSO data sources using&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/service-gateway-sso-kerberos\">Kerberos constrained delegation<\/a>.<\/li><li>Under&nbsp;<strong>Advanced settings<\/strong>, optionally configure the&nbsp;<a href=\"https:\/\/support.office.com\/article\/Privacy-levels-Power-Query-CC3EDE4D-359E-4B28-BC72-9BEE7900B540\">privacy level<\/a>&nbsp;for your data source (doesn&#8217;t apply to&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/desktop-directquery-about\">DirectQuery<\/a>).<img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/media\/service-gateway-data-sources\/privacy-level.png\" alt=\"Privacy level selections.\"><\/li><li>Select&nbsp;<strong>Add<\/strong>. You see&nbsp;<em>Connection Successful<\/em>&nbsp;if the process succeeds.<img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/media\/service-gateway-data-sources\/connection-successful.png\" alt=\"Connection successful\"><\/li><\/ol>\n\n\n\n<p>You can now use this data source to include data from SQL Server in your Power BI dashboards and reports.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"remove-a-data-source\"><a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/service-gateway-data-sources#remove-a-data-source\"><\/a>Remove a data source<\/h2>\n\n\n\n<p>You can remove a data source if you no longer use it. Removing a data source breaks any dashboards and reports that rely on that data source.<\/p>\n\n\n\n<p>To remove a data source, go to the data source and then select&nbsp;<strong>Remove<\/strong>&nbsp;from the more options menu. The more options menu appears when you hover your cursor next to the data source name.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/media\/service-gateway-data-sources\/remove-data-source.png\" alt=\"Remove a data source\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"use-the-data-source-for-scheduled-refresh-or-directquery\"><a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/service-gateway-data-sources#use-the-data-source-for-scheduled-refresh-or-directquery\"><\/a>Use the data source for scheduled refresh or DirectQuery<\/h2>\n\n\n\n<p>After you create the data source, it&#8217;s available to use with either DirectQuery connections or through scheduled refresh. You can learn more about setting up scheduled refresh in&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/refresh-scheduled-refresh\">Configure scheduled refresh<\/a>.<\/p>\n\n\n\n<p>&nbsp;Note<\/p>\n\n\n\n<p>Server and database names must match between Power BI Desktop and the data source added to the on-premises data gateway.<\/p>\n\n\n\n<p>The link between your dataset and the data source in the gateway is based on your server name and database name. These names must match. For example, if you supply an IP address for the server name, in Power BI Desktop, you must use the IP address for the data source in the gateway configuration. If you use&nbsp;<em>SERVER\\INSTANCE<\/em>&nbsp;in Power BI Desktop, you must use the same in the data source configured for the gateway.<\/p>\n\n\n\n<p>If you&#8217;re listed in the&nbsp;<strong>Users<\/strong>&nbsp;tab of the data source configured in the gateway, and the server and database name match, you see the gateway as an option to use with scheduled refresh.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/media\/service-gateway-data-sources\/gateway-connection.png\" alt=\"Gateway connection\"\/><\/figure>\n\n\n\n<p>&nbsp;Warning<\/p>\n\n\n\n<p>If your dataset contains multiple data sources, each data source must be added in the gateway. If one or more data sources aren&#8217;t added to the gateway, you won&#8217;t see the gateway as available for scheduled refresh.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"considerations-and-limitations\"><a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/service-gateway-data-sources#considerations-and-limitations\"><\/a>Considerations and limitations<\/h3>\n\n\n\n<p>OAuth is a supported authentication scheme only for custom connectors with the on-premises data gateway.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"manage-users\"><a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/service-gateway-data-sources#manage-users\"><\/a>Manage users<\/h2>\n\n\n\n<p>After you add a data source to a gateway, you give users and email-enabled security groups access to the specific data source (not the entire gateway). The access list for the data source controls only who is allowed to publish reports that include data from the data source. Report owners can create dashboards and apps, and then share those items with other users.<\/p>\n\n\n\n<p>You can also give users and security groups administrative access to the gateway.<\/p>\n\n\n\n<p>&nbsp;Note<\/p>\n\n\n\n<p>Users with access to the data source can associate datasets to the data source, and connect, based on the security options (either the stored credentials or Single Sign-On) selected while creating a data source.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"add-users-to-a-data-source\"><a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/service-gateway-data-sources#add-users-to-a-data-source\"><\/a>Add users to a data source<\/h3>\n\n\n\n<ol class=\"wp-block-list\"><li>From the page header in the Power BI service, select&nbsp;<strong>Settings<\/strong>&nbsp;<img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/media\/service-gateway-data-sources\/icon-gear.png\" alt=\"Settings gear icon\">&nbsp;&gt;&nbsp;<strong>Manage gateways<\/strong>.<\/li><li>Select the data source where you want to add users.<\/li><li>Select&nbsp;<strong>Users<\/strong>, and enter the users and mail-enabled security groups from your organization who will access the selected data source. Select&nbsp;<strong>Add<\/strong>, and the added member&#8217;s name is added to the list of people who can publish reports that use this data source.<img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/media\/service-gateway-data-sources\/add-user.png\" alt=\"Add user\"><\/li><\/ol>\n\n\n\n<p>Remember that you need to add users to each data source that you want to grant access to. Each data source has a separate list of users. Add users to each data source separately.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"remove-users-from-a-data-source\"><a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/service-gateway-data-sources#remove-users-from-a-data-source\"><\/a>Remove users from a data source<\/h3>\n\n\n\n<p>On the&nbsp;<strong>Users<\/strong>&nbsp;tab for the data source, you can remove users and security groups that use this data source.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/media\/service-gateway-data-sources\/remove-user.png\" alt=\"Remove user\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"store-encrypted-credentials-in-the-cloud\"><a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/service-gateway-data-sources#store-encrypted-credentials-in-the-cloud\"><\/a>Store encrypted credentials in the cloud<\/h2>\n\n\n\n<p>When you add a data source to the gateway, you must provide credentials for that data source. All queries to the data source will run by using these credentials. The credentials are encrypted securely. They use symmetric encryption so that they can&#8217;t be decrypted in the cloud before they&#8217;re stored in the cloud. The credentials are sent to the machine that runs the gateway, on-premises, where they&#8217;re decrypted when the data sources are accessed.<\/p>\n\n\n\n<p>Ref: https:\/\/docs.microsoft.com\/en-us\/power-bi\/connect-data\/service-gateway-data-sources<\/p>\n","protected":false},"excerpt":{"rendered":"<p>We&#8217;ve split the on-premises data gateway docs into&nbsp;content that&#8217;s specific to Power BI&nbsp;and&nbsp;general content that applies to all services&nbsp;that the gateway supports. You&#8217;re currently in the Power BI content. To provide feedback on this article, or the overall gateway docs experience, scroll to the bottom of the article. Power BI supports many&nbsp;on-premises data sources, and <a class=\"read-more\" href=\"https:\/\/SUMMALAI.COM\/?p=4028\">Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,621],"tags":[1122,1124,1123],"class_list":["post-4028","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-power-bi","tag-add-or-remove-a-gateway-data-source","tag-add-or-remove-a-gateway-data-source-power-bi","tag-remove-a-gateway-data-source"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/4028","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4028"}],"version-history":[{"count":2,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/4028\/revisions"}],"predecessor-version":[{"id":4030,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/4028\/revisions\/4030"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4028"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4028"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4028"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}