Create a Data Source and Enable Cloud Access

In this article


Introduction

When you create an Excel workbook with a Power Pivot model that uses data from an on-premises data source and stores it in SharePoint Online, you will not be able to refresh the workbook unless an IT Administrator enables the on-premises data source to be accessible from cloud by using the Power BI for Office 365 Admin Center. An information worker sends an IT Administrator a link to the Excel workbook in SharePoint Online or a copy of the Excel workbook via email, or the connection string for the connection used in the Excel workbook. An IT Administrator performs the following steps to enable the cloud access for the data source:

  1. Install and configure Data Management Gateway if it is not already installed. The Data Management Gateway is a client agent that provides access to on-premises data sources in your organization. To register an on-premises data source with the Admin Center, an administrator must create a gateway in the Admin Center. See Install, Configure, and Register Data Management Gateway for the detailed steps for creating a gateway in the Admin Center. If you try to create a data source without a gateway, you will be redirected to the gateway creation process.
  2. Extract the connection string from the Excel workbook if needed and use the connection string to register the on-premises data source with the Admin Center, enable cloud access for the data source, grant users/groups who can refresh the workbook on the SharePoint Online. This article provides detailed steps to do this step.

 Note    For a list of supported data sources and data types, see Supported Data Sources and Data Types.

 Important    Maximum number of data sources allowed per tenant is 1000.

Creating a data source in Admin Center

In this step, you will register a data source in the Admin Center by using the connection string that you either received directly from the information worker or extracted from the Excel workbook. The following procedure provides steps for registering the data source in the Admin Center. See the Troubleshooting topic if you still can’t refresh the workbook after performing the steps in the following procedure.

 Note    If you have multiple connections that use the same connection string, you will need to create only one data source for all the connections.

To create a SQL Server or Oracle data source

  1. In the Power BI Admin Center, switch to the data sources page. See Introduction to Power BI Admin Center for different ways to access the Admin Center.
  2. In the data sources page, click new data source > SQL Server or Oracle.
  3. In the data source usage page, select how you want to use your data source.
    • Select Enable Cloud Access to refresh your workbooks that reference in your enterprise (or on-premises) data source can be refreshed in SharePoint Online.
    • Select Enable OData Feed to enable data from an on-premises data source as an OData feed that can be accessed by user's corporate-wide from Power Query. To enable an on-premises data source as an OData feed, you also set the tables and views to be exposed in the OData feed. You set tables and views in the data settings page.
  4. In the connection info page:
  1. In the Name textbox, enter a valid data source name. A data source name has characters, numbers, hyphens and underscores. It cannot contain spaces and must start with a character.

 Note    You cannot change the name after you save the data source.

  1. You can enter a Description.
  2. Select a Gateway to use to access the on-premises data source. You can only change the gateway if the cloud credential store for the current gateway is enabled, and only the gateways using the same certificate are available. For more information about Gateway’s, see Introduction to Data Management Gateway.

 Note    You cannot change the gateway after the data source is created.

   
  1. Select a Data Source Type of SQL Server or Oracle.
  2. You connect to your data source by setting connection properties or entering a connection string. For example, you can select .NET Framework Data Provider for OLE DB for Connection Provider and copy/paste the connection string from the previous procedure.
  • To set Connection Properties:
  1. Select a Connection provider.
  2. Enter a Server name.
  3. Enter a Database name (for SQL Server only). Enter the right case if your server collation is case sensitive.
  • To enter a Connection string:
  1. Select a Connection provider.
  2. Enter a valid Connection string. You have two options to set the connection string:
  1. To set credentials for a data source, and enable access to a data source, click Credentials. The data source cannot be accessed without this information. This will install the ClickOnce application when you click for the first time. You should see the Data Source Manager dialog box. If you run into any issues with using this dialog box, see Troubleshooting issues with using the Data Source Manager.
  2. In the Data Source Manager dialog box, select Authentication Type, and enter user name and password that you want the client gateway to use to access the data source.

 Important    These credentials are used by the data management gateway running on on-premises computer to connect to the on-premises data source.


  1. Click Test Connection to test the connection. If the test is successful, OK is enabled.
  2. Click OK to save the credential settings for the data source.
  3. Click Next.
  1. In the data settings page for an OData Feed:
    • Select the tables and views you want to access as an OData feed.
    • To search keywords from an OData feed, you enable the feed. Click Index the feed to select an Index schema only option to allow, or not allow, previewing of the feed in Power Query.
    • If you index a feed, you can specify a schedule to update the index for the feed.
    • Click next.
  2. In the users and groups page you specify Office 365 users and groups allowed to search an OData feed in Power Query for Excel and/or access this data source to refresh Excel workbooks in SharePoint Online.

 Important    The user or group will have access to the on-premises data source.

  1. Click finish or save if you are updating an existing user or group.

To create a Power Query data source   

  1. In the data sources page, click new data source > Power Query.
  2. In the connection info page, enter a valid Power Query connection string, and click next. You have two options to set the connection string:
  1. In the data source info page, select a data source that has not been configured.
  2. Enter data source information including a description:
  • Name is a required setting, which is used to identify a data source in Admin Center.
  • Select a Gateway. For more information about how to create a Power BI Gateway, see Power BI for Office 365 Admin Center Help.

 Important    Please make sure that the Gateway has been upgraded to the latest version to support Power Query data sources.

  1. To set credentials for a data source, and enable access to a data source, click Credentials. The data source cannot be accessed without this information.
  2. In the Configure authentication dialog box, select Authentication Type, enter your credentials for the data source, and click ok. To validate your credentials for a connection, click test connection.
  3. You can expand View additional details to view the Connection Provider, and view the Connection String.
  4. Click save, and the data source status is changed to configured.
  5. When all the data sources within the Power Query connection are appropriately configured, click test Power Query connection to test the entire Power Query connection.
  6. Click next.

new data source

  1. In the users and groups page you specify users and groups allowed to access this data source to refresh Excel workbooks in SharePoint Online. The specified users and groups will be appended to the existing users and groups list of the data sources.
  2. Click finish or save if you are updating an existing user or group.

 Important    Currently only SQL Server (using .NET Framework Data Provider for SQL Server) and Oracle databases (using Oracle Data Provider for .NET) are supported for Power Query connections. All data sources must be hosted on the same gateway.

Power Query data source limitations

  • SQL statements are not supported. SQL statements are also known as Native Queries.
  • #literal is not supported.
  • .NET Framework 3.5 is required for gateway version 1.1.

Get a connection string from an Excel workbook

Get a connection string from a data table

1.      From the DATA ribbon tab, click Connections.

2.      In the Workbook Connections dialog box, click Properties.

3.      Click the Definitions tab, and copy the connection string from the Connection string textbox.

 Note    If the connection is used by a data model, the Connection string textbox is not enabled.

Create a connection string manually

A connection string is a string version of the initialization properties needed to connect to a data source. The connection syntax is unique to the data provider.

.NET Framework Data Provider for SQL Server

   Windows Authentication (Trusted connection)

   Server=myServerName\myInstanceName;
Initial Catalog={Case sensitive database name};Trusted_Connection=True;

   SQL Server Authentication (Requires a user name and password)   

   Server=myServerName\myInstanceName;
Initial Catalog={Case sensitive database name};User Id=myUsername;
Password=myPassword;

.NET Framework Data Provider for Oracle (Oracle 8i release 3 or later)

Standard   

Data Source={Case sensitive database name};Integrated Security=yes;

Specifying username and password

Data Source=={Case sensitive database name};User Id=myUsername;Password=myPassword;
Integrated Security=no;

To manually create a connection string

  1. Enter a connection string for the specific data provider.
  2. Use a case sensitive database name that matches the database name in the Excel connection string.

 Note    If the case of the database name does not match, the refresh of the workbook will fail.

  1. The provider selected in the connection info page must match the provider in the connection string from the Excel workbook.

See Also

 
 
Applies to:
Power BI for Office 365