When an information worker creates an Excel workbook with a Power Pivot model that uses data from an on-premises data source (currently only SQL Server database) and stores it in SharePoint Online, the information worker will not be able to refresh the workbook unless you, as an IT Administrator, enable the on-premises data source to be accessible from cloud by using the Power BI for Office 365 Admin Center. The information worker sends you 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. You (the IT Administrator) need to perform the following steps to enable the cloud access for the data source:
- 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.
- 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.
The following sections provide instructions to extract connection string from an Excel workbook and to register the on-premises data source using the connection string.
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.
Extracting connection string from Excel workbook
The following procedure provides steps to get connection string for a data connection used by a Power Pivot data model in an Excel 2013 workbook.
- Open workbook in EXCEL and switch to the Power Pivot tab.
- Click Manage button from the Data Model tab on the ribbon.
- Click Existing Connections in the Get External Data tab on the ribbon.
- In the Existing Connections dialog box, select the connection from the list and click Edit button.
- Copy the connection string from the Connection string text box.
In Excel 2010, switch to Power Pivot tab, click Power Pivot Window on the ribbon, switch to Design tab in the Power Pivot window, and click Existing Connections.
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.
- In the Power BI Admin Center, switch to the data sources tab. See Introduction to Power BI Admin Center for different ways to access the Admin Center.
- Click + add data source on the toolbar.
- On the data source usage page, select Enable Cloud Access option, and click Next.
- On the data source usage page, specify how you want to use the data source. Select Enable Cloud Access option, and click Next.
- On the connection info page, perform the following:
- Specify a name and description for the data source. Note that you cannot change the name after you save the data source.
- Select a gateway from the drop-down list and that you want to associate with this data source from the drop-down list for Gateway. This gateway is used to access the on-premises data source. You cannot change the gateway after the data source is created.
- Select a data source (for example: SQL Server) for Data Source Type.
- Select Connection String option, if you want to select a Connection provider and enter the Connection String in the text box. 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.
Copy the connection string from the Excel workbook and paste it into the Connection String text box. If you enter the connection string manually, ensure that the database name matches the database name in the Excel connection string. The database name is case sensitive, so if you type the name in a different case from the connection string in the Excel workbook, the refresh of the workbook will fail. The provider you select here also must match the provider in the connection string from the Excel workbook.
Select Connection Properties option if you want to select a provider and specify the Server name, Database (for SQL Server only), and Authentication Type (for SQL Server only).
Note Database name is case sensitive. If you did not copy/paste the connection string from the Excel workbook and entered the database name manually, ensure that you have entered the name as it is in the Excel connection string.
- Click Credentials button. This will install the ClickOnce application when you click for the first time. You should see the Data Source Management dialog box. If you run into any issues with using this dialog box, see Troubleshooting issues with using the Data Source Manager.
- In the Data Source Manager dialog box, 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.
- Click Test Connection to test the connection. If the testing is successful, OK button is enabled.
- Click OK to save the credential settings for the data source.
- Click Next on connection info page.
- On the users and groups page, click + on the toolbar, search Office 365 uses and groups with a name or group, and select the user/group from the result list. This user/group will have access to the on-premises data source and will be able to refresh the workbook in SharePoint Online.
- Click Finish to finish creating the data source and to get back to the data sources page.