Programmatically Setting and Verifying Data Source Credentials for Power BI Reports

With the recent release of Power BI Report Server (October 2017), users are now able to consume data from a wider range of data sources in their Power BI Reports than previously possible. Unlike in the past where users could only get data via Live Connect to Analysis Services, users can now either directly import data into their Power BI reports or can specify a Direct Query data source, which the Report Server will fetch data from at the time of rendering the report. In this blog post, I am going to focus on how to set credentials for Power BI Report Data Sources using the new REST API 2.0.

Prerequisites

The primary assumption made in this blog post is you are using Power BI Report Server (October 2017). The reason behind this is the APIs described here were first shipped in this release. If you haven’t had a chance to explore https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0 so far, I highly recommend you to have a look at this page.

DataSources API

Background

If you look through the SwaggerHub Doc, you will notice the word “DataSources” appears several times:

  • /DataSources
  • /DataSets(…)/DataSources
  • /PowerBIReports(…)/DataSources
  • /Reports(…)/DataSources

Without going into too much detail, each API is for managing either Shared Data Sources or embedded Data Sources for Shared Data Sets, Power BI Reports, and Paginated Reports. If you are would like to learn more about this, please leave a comment at the end of this post.

For the purpose of this blog post, we are going to focus just on the /PowerBIReports(…)/DataSources API.

Retrieving Power BI Report Data Sources

In order to retrieve data sources associated to a Power BI Report, you need to perform a GET request to one of following APIs:

  • Retrieve Data Sources using Power BI Report ID
    http://localhost/Reports/api/v2.0/PowerBIReports(<Id>)/DataSources

    Please substitute <ID> with the ID of your Power BI Report. (e.g. http://localhost/Reports/api/v2.0/PowerBIReports(00000-00000-00000-00000-00000)/DataSources)

  • Retrieve Data Sources using Power BI Report Path
    http://localhost/Reports/api/v2.0/PowerBIReports(Path=‘<path>’)/DataSources

    Please substitute <path> with the path of your Power BI Report. (e.g. http://localhost/Reports/api/v2.0/PowerBIReports(Path=‘/My PBI Report’)/DataSources)

When you make a request to this API, you will get a response like the following.

Click here to see the response
{
  "@odata.context": "string",
  "@odata.count": 0,
  "value": [
    {
      "Id": "01234567-89ab-cdef-0123-456789abcdef",
      "Name": "string",
      "Description": "string",
      "Path": "string",
      "Type": "Unknown",
      "Hidden": true,
      "Size": 0,
      "ModifiedBy": "string",
      "ModifiedDate": "2017-04-13T15:51:04Z",
      "CreatedBy": "string",
      "CreatedDate": "2017-04-13T15:51:04Z",
      "ParentFolderId": "01234567-89ab-cdef-0123-456789abcdef",
      "ContentType": "string",
      "Content": "string",
      "IsFavorite": true,
      "IsEnabled": true,
      "ConnectionString": "string",
      "DataModelDataSource": {
        "AuthType": "Windows",
        "SupportedAuthTypes": [
          "Windows",
          "UsernamePassword"
        ],
        "Kind": "SQL",
        "ModelConnectionName": "string",
        "Secret": "string",
        "Type": "Import",
        "Username": "string"
      },
      "DataSourceSubType": "string",
      "DataSourceType": "string",
      "IsOriginalConnectionStringExpressionBased": true,
      "IsConnectionStringOverridden": true,
      "CredentialsByUser": {
        "DisplayText": "string",
        "UseAsWindowsCredentials": true
      },
      "CredentialsInServer": {
        "UserName": "string",
        "Password": "string",
        "UseAsWindowsCredentials": true,
        "ImpersonateAuthenticatedUser": true
      },
      "IsReference": true,
      "Subscriptions": {
        "Id": "01234567-89ab-cdef-0123-456789abcdef",
        "Owner": "string",
        "IsDataDriven": true,
        "Description": "string",
        "Report": "string",
        "IsActive": true,
        "EventType": "string",
        "ScheduleDescription": "string",
        "LastRunTime": "2017-04-13T15:51:04Z",
        "LastStatus": "string",
        "ExtensionSettings": {
          "Extension": "string",
          "ParameterValues": {
            "Name": "string",
            "Value": "string",
            "IsValueFieldReference": true
          }
        },
        "DeliveryExtension": "string",
        "LocalizedDeliveryExtensionName": "string",
        "ModifiedBy": "string",
        "ModifiedDate": "2017-04-13T15:51:04Z",
        "ParameterValues": [
          {
            "Name": "string",
            "Value": "string",
            "IsValueFieldReference": true
          }
        ]
      }
    }
  ]
}

 

Setting credentials for Power BI Report Data Sources

Once you have retrieved data sources associated to your Power BI Report, the next thing you need to do is find and update the “DataModelDataSource” property for each data source whose credentials you want to update. From the sample response shown above, the DataModelDataSource property looks like the following:

"DataModelDataSource": { 
   "AuthType": "Windows", 
   "SupportedAuthTypes": [ "Windows", "UsernamePassword" ], 
   "Kind": "SQL", 
   "ModelConnectionName": "string", 
   "Secret": "string", 
   "Type": "Import", 
   "Username": "string" 
}

Once you find DataModelDataSource object, you need to look at the “SupportedAuthTypes” property. This array will tell you all the different types of authentication you can use for that data source. The AuthType you choose will determine which additional properties you need to specify to use that AuthType. The following is a list of possible AuthTypes you can expect to see along with which properties you will also need to specify to use that AuthType:

  • Unknown: this is the default auth type. You shouldn’t use this AuthType.
  • Anonymous: for this AuthType, you don’t need to specify username or secret.
  • Integrated: for this AuthType, you don’t need to specify username or secret.
  • Windows: for this AuthType, you need to specify both username and secret.
  • UsernamePassword: for this AuthType, you need to specify both username and secret. (If you are looking to connect to your Database using SQL Server Authentication, choose this AuthType.)
  • Key: for this AuthType, you need to specify just secret.
  • Impersonate: for this AuthType, you need to specify both username and secret.

NOTE: Not all data sources support all of the above AuthTypes.

After you have finished setting the credentials for all your data sources, you need to make a PATCH request to the same URL which you had made a GET request to. In the body of your request, you will need to provide an array of ALL the Data Sources, which you received when you performed GET request regardless of whether you modified 1 or all of them. The following is an example of request body.

Click here to see request body
[
  {
    "Id": "01234567-89ab-cdef-0123-456789abcdef",
    "Name": "string",
    "Description": "string",
    "Path": "string",
    "Type": "Unknown",
    "Hidden": true,
    "Size": 0,
    "ModifiedBy": "string",
    "ModifiedDate": "2017-04-13T15:51:04Z",
    "CreatedBy": "string",
    "CreatedDate": "2017-04-13T15:51:04Z",
    "ParentFolderId": "01234567-89ab-cdef-0123-456789abcdef",
    "ContentType": "string",
    "Content": "string",
    "IsFavorite": true,
    "IsEnabled": true,
    "ConnectionString": "string",
    "DataModelDataSource": {
      "AuthType": "Windows",
      "SupportedAuthTypes": [
        "Windows",
        "UsernamePassword"
      ],
      "Kind": "SQL",
      "ModelConnectionName": "string",
      "Secret": "myPassword",
      "Type": "Import",
      "Username": "myUsername"
    },
    "DataSourceSubType": "string",
    "DataSourceType": "string",
    "IsOriginalConnectionStringExpressionBased": true,
    "IsConnectionStringOverridden": true,
    "CredentialsByUser": {
      "DisplayText": "string",
      "UseAsWindowsCredentials": true
    },
    "CredentialsInServer": {
      "UserName": "string",
      "Password": "string",
      "UseAsWindowsCredentials": true,
      "ImpersonateAuthenticatedUser": true
    },
    "IsReference": true,
    "Subscriptions": {
      "Id": "01234567-89ab-cdef-0123-456789abcdef",
      "Owner": "string",
      "IsDataDriven": true,
      "Description": "string",
      "Report": "string",
      "IsActive": true,
      "EventType": "string",
      "ScheduleDescription": "string",
      "LastRunTime": "2017-04-13T15:51:04Z",
      "LastStatus": "string",
      "ExtensionSettings": {
        "Extension": "string",
        "ParameterValues": {
          "Name": "string",
          "Value": "string",
          "IsValueFieldReference": true
        }
      },
      "DeliveryExtension": "string",
      "LocalizedDeliveryExtensionName": "string",
      "ModifiedBy": "string",
      "ModifiedDate": "2017-04-13T15:51:04Z",
      "ParameterValues": [
        {
          "Name": "string",
          "Value": "string",
          "IsValueFieldReference": true
        }
      ]
    }
  }
]

 

Verifying credentials are valid (aka Test Connection)

Once you have set the credentials, you would also want to verify that Report Server is able to connect to your data source using the specified AuthType and credentials. In order to do this, you have to make a POST request to one of the following APIs:

  • Perform Test Connection using Power BI Report ID
    http://localhost/Reports/api/v2.0/PowerBIReports(<Id>)/Model.CheckDataSourceConnection

    Please substitute <ID> with the ID of your Power BI Report. (e.g. http://localhost/Reports/api/v2.0/PowerBIReports(00000-00000-00000-00000-00000)/Model.CheckDataSourceConnection)

  • Perform Test Connection using Power BI Report Path
    http://localhost/Reports/api/v2.0/PowerBIReports(Path=‘<path>’)/Model.CheckDataSourceConnection

    Please substitute <path> with the path of your Power BI Report. (e.g. http://localhost/Reports/api/v2.0/PowerBIReports(Path=‘/My PBI Report’)/Model.CheckDataSourceConnection)

In the body of your request, you need to specify the Data Source ID, which you want to perform Test Connection on. Unfortunately, you can only perform Test Connection at 1 data source at a time. If you are not sure what the ID of your data source is, just look at the response you got when you fetched all data sources for your Power BI Report. Here is how your request body should look like.

{ 
    "DataSourceName":"<enter Data Source ID>" 
}

Once you make that request, in the response you receive, you will see a JSON object with 2 properties: IsSuccessful and ErrrorMessage. If Report Server is able to connect to your data source using your selected AuthType and credentials, IsSuccessful will be true, otherwise IsSuccessful will be false and ErrorMessage will contain some information on what went wrong.

Summary

Power BI Report Server (October 2017) added support for a lot of different data sources. In this blog post, I tried to cover how to set the data source credentials via the REST API 2.0 and also how to verify that Report Server is able to connect using those credentials. Usually, I will explain how to do all of this via different PowerShell commands that were added to ReportingServicesTools GitHub project. But in a previous attempt to check in some sort of PowerShell, one of my colleagues (Jaime Tarquino) suggested that we should add some sort of an example on how to use this command. In a way, “Setting credentials of Power BI Report Server” section of this blog post is the sort of examples that should be added to the PowerShell command. I will try to add that to the commands and try publishing them soon.

3 Comments

Add a Comment

Your email address will not be published. Required fields are marked *