Forum Discussion

alexyin1053's avatar
alexyin1053
Helper I
1 year ago
Solved

Get SQL Server Data from Power BI Report Server's Rest API

Dear all,

 

Currenly we're running on premise power bi report server, while we would like to get one of SQL Server's  table([ReportServer].[dbo].[Users]) by using Power BI Report Server's Rest API.

Any help or suggestions are appreciated!

Thanks!

 

Best Regards,

Alex

@NeedHelp
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, alexyin1053 

    Your clarification has helped me better understand your needs. You expect to get a list of users with access to each Power BI report. This is available through the API. I wrote a python code:

    key api : Power BI Reports - Get Power BI Report Policies - REST API (Power bi report) | Microsoft Learn

    key api: Power BI Reports - Get Power BI Reports - REST API (Power bi report) | Microsoft Learn

    import requests
from requests_ntlm import HttpNtlmAuth

url = "http://Yourserver/Reports/api/v2.0/PowerBIReports"
headers = {
    "accept": "application/json, text/plain, */*",
    "accept-encoding": "gzip, deflate",
    "accept-language": "en-US,en;q=0.9",
    "connection": "keep-alive",
    "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/132.0.0.0 Safari/537.36 Edg/132.0.0.0"
}

# replace your username and pwd
username = ""
password = ""

response = requests.get(url, headers=headers, auth=HttpNtlmAuth(username, password))

print(response.status_code)
reportlist = response.json()["value"]
newlist = []
newdict = {}
for item in reportlist:
    newdict = {"id": item["Id"],"ReportName":item["Name"]}
    newlist.append(newdict)
for i in range(len(newlist)):
    url2 = f"http://YourServer/Reports/api/v2.0/PowerBIReports({newlist[i]["id"]})/Policies"
    response1 = requests.get(url2, headers=headers, auth=HttpNtlmAuth(username, password))
    policies = response1.json()["Policies"]
    newlist[i]["Policies"] = policies
    

print(newlist)

    You should be able to get a dictionary like this (which contains a list of user information accessible for each report):

    You can then use this dictionary for analysis or import into Power BI.

     

     

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

10 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    Hi alexyin1053 


    To retrieve SQL Server data from Power BI Report Server’s REST API, first ensure that REST API access is enabled by testing the base URL (http://<your-report-server>/Reports/api/v2.0/) in a browser. To access report data sources, use the GET /PowerBIReports endpoint to list reports and retrieve their IDs, followed by GET /DataSources to find the relevant data source. Since the API does not directly expose SQL tables, you need to create a paginated report (RDL) with a dataset querying [ReportServer].[dbo].[Users]. You can then use the API to export the report data in CSV or JSON format by making a POST request to /Export, providing the report ID and desired format in the request body. The API will return a download URL for the exported data. Alternatively, if you have database access, querying SQL Server directly using SELECT * FROM [ReportServer].[dbo].[Users] in SSMS or Power BI Desktop would be more efficient. Let me know if you need a PowerShell or Python script to automate this process.

    • alexyin1053's avatar
      alexyin1053
      Helper I
      1 year ago

      Hi rohit1991 ,

       

      Huge thanks for your reply, which helped us better understand what PBIRS can actually do.

      Our goal is to display the information on another service website. The information is about all the dashboards currently on the ReportServer, along with the people who have browsing permissions.
      SSMS Query SQL code:

       

      SELECT DISTINCT U.UserName, C.Name as ObjectName, C.Path, 
       C.Description, MU.UserName As LatestModifiedByUserName
  FROM [ReportServer].[dbo].[Users] U
       join [ReportServer].[dbo].[PolicyUserRole] PUR on U.UserID = PUR.UserID
       join [ReportServer].[dbo].[Policies] P on P.PolicyID = PUR.PolicyID
       join [ReportServer].[dbo].[Catalog] C on C.PolicyID = P.PolicyID
       join [ReportServer].[dbo].[Users] MU on C.ModifiedByID = MU.UserID
 Where C.Type = 13 --Filter Type="PBIReport“;

       


      Ideally, we would like to retrieve the upper information directly through the API of the AP Server.
      Besides, we also have access to the SQL database, executing it directly via PowerShell or Python scripts is also a viable option.

      Let me know if more information is needed!

      Thanks!

       

      Best Regards,

      Alex

      • rohit1991's avatar
        rohit1991
        Super User
        1 year ago

        Hi alexyin1053 

         

        It's great that you have access to both the Power BI Report Server (PBIRS) REST API and the SQL Server database. Unfortunately, the PBIRS REST API does not provide direct access to the [ReportServer].[dbo].[Users] table or user permissions in a structured way. However, since you have SQL access, the best approach is to execute your SQL query directly via PowerShell or Python to retrieve the required user and report details. You can use PowerShell's Invoke-Sqlcmd or Python's pyodbc or SQLAlchemy to run your query and return the results in a structured format. If you still prefer using the PBIRS API, you can use the /api/v2.0/CatalogItems endpoint to get reports and /api/v2.0/Folders to explore folder structures, but user permission details would still need to come from the database.


         

  • Poojara_D12's avatar
    Poojara_D12
    Super User
    1 year ago

    Hi alexyin1053 

    Power BI Report Server (PBIRS) does not provide a direct REST API to retrieve SQL Server tables like [ReportServer].[dbo].[Users]. However, you can access this data by directly querying the ReportServer database using SQL. The simplest way is to connect Power BI Desktop to SQL Server and run a query like SELECT * FROM [ReportServer].[dbo].[Users], which will fetch the list of users. If your requirement is to access user-related metadata via the REST API, you can use endpoints like /reports/api/v2.0/Folders or /reports/api/v2.0/CatalogItems to retrieve information on report access and permissions. Alternatively, if API-based access is essential, you can develop a custom API that queries the database and exposes the required data. Another approach is to use Power Automate or a scheduled job to export user data into a format that Power BI can consume. The best method depends on whether you need real-time access, automation, or integration with other applications. Let me know if you need help writing a query or setting up an API!

     

     

    • alexyin1053's avatar
      alexyin1053
      Helper I
      1 year ago

      Hi Poojara_D12,

       

      Thanks for the reply, and sorry I didn't described our goal very clearly.

      Our goal is to display the information on another service website. The information is about all the dashboards currently on the ReportServer, along with the people who have browsing permissions.
      SSMS Query SQL code:

       

      SELECT DISTINCT U.UserName, C.Name as ObjectName, C.Path, 
       C.Description, MU.UserName As LatestModifiedByUserName
  FROM [ReportServer].[dbo].[Users] U
       join [ReportServer].[dbo].[PolicyUserRole] PUR on U.UserID = PUR.UserID
       join [ReportServer].[dbo].[Policies] P on P.PolicyID = PUR.PolicyID
       join [ReportServer].[dbo].[Catalog] C on C.PolicyID = P.PolicyID
       join [ReportServer].[dbo].[Users] MU on C.ModifiedByID = MU.UserID
 Where C.Type = 13 --Filter Type="PBIReport“;


      Ideally, we would like to retrieve the real-time data directly through API.



      Let me know if more information is needed!

      Thanks!

       

      Best Regards,

      Alex

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thank you rohit1991 

    Hi, alexyin1053 

    The API can obtain the user list information of the report server. However, some fields are owned in SQL Server, and they are not within the scope of API acquisition, so I will take you through how to obtain it and explain which fields cannot be obtained.

    The following is an example to obtain the list of users at the server level:

    Press F12:

    Sample:

    http://YourServerIP/Reports/api/v2.0/System/Policies

    Using python write a script:

    import requests
from requests_ntlm import HttpNtlmAuth

url = "http://YourServerIp/Reports/api/v2.0/System/Policies"
headers = {
    "accept": "application/json, text/plain, */*",
    "accept-encoding": "gzip, deflate",
    "accept-language": "en-US,en;q=0.9",
    "connection": "keep-alive",
    "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/132.0.0.0 Safari/537.36 Edg/132.0.0.0"
}

# Replace with your username and password
username = ""
password = ""

response = requests.get(url, headers=headers, auth=HttpNtlmAuth(username, password))

print(response.status_code)
print(response.json())

    Here are the results:

    Others are similar. Of course, it is still recommended that you use ssms to connect to your database and use the following SQL statement to query the entire table:

    SELECT TOP (1000) [UserID]
      ,[Sid]
      ,[UserType]
      ,[AuthType]
      ,[UserName]
      ,[ServiceToken]
      ,[Setting]
      ,[ModifiedDate]
  FROM [ReportServer].[dbo].[Users]

     

     

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

     

    • alexyin1053's avatar
      alexyin1053
      Helper I
      1 year ago

      Hi Anonymous  ,

       

      Thanks for the reply, and sorry I didn't described our goal very clearly.

      Our goal is to display the information on another service website. The information is about all the dashboards currently on the ReportServer, along with the people who have browsing permissions.
      SSMS Query SQL code:

       

      SELECT DISTINCT U.UserName, C.Name as ObjectName, C.Path, 
       C.Description, MU.UserName As LatestModifiedByUserName
  FROM [ReportServer].[dbo].[Users] U
       join [ReportServer].[dbo].[PolicyUserRole] PUR on U.UserID = PUR.UserID
       join [ReportServer].[dbo].[Policies] P on P.PolicyID = PUR.PolicyID
       join [ReportServer].[dbo].[Catalog] C on C.PolicyID = P.PolicyID
       join [ReportServer].[dbo].[Users] MU on C.ModifiedByID = MU.UserID
 Where C.Type = 13 --Filter Type="PBIReport“;

       


      Ideally, we would like to retrieve the upper information directly through the API of the AP Server so that the data is always up to date.


      Besides, we also have access to the SQL database, so querying directly to the SQL Server is also a viable option.

      Let me know if more information is needed!

      Thanks!

       

      Best Regards,

      Alex

    • alexyin1053's avatar
      alexyin1053
      Helper I
      1 year ago

      Hi Anonymous  ,

       

      Thanks for the reply, and sorry I didn't described our goal very clearly.

      Our goal is to display the information on another service website. The information is about all the dashboards currently on the ReportServer, along with the people who have browsing permissions.
      SSMS Query SQL code:

       

      SELECT DISTINCT U.UserName, C.Name as ObjectName, C.Path, 
       C.Description, MU.UserName As LatestModifiedByUserName
  FROM [ReportServer].[dbo].[Users] U
       join [ReportServer].[dbo].[PolicyUserRole] PUR on U.UserID = PUR.UserID
       join [ReportServer].[dbo].[Policies] P on P.PolicyID = PUR.PolicyID
       join [ReportServer].[dbo].[Catalog] C on C.PolicyID = P.PolicyID
       join [ReportServer].[dbo].[Users] MU on C.ModifiedByID = MU.UserID
 Where C.Type = 13 --Filter Type="PBIReport“;

       


      Ideally, we would like to retrieve the upper information directly through the API of the AP Server so that the data is always up to date.


      Besides, we also have access to the SQL database, so querying directly to the SQL Server is also a viable option.

      Let me know if more information is needed!

      Thanks!

       

      Best Regards,

      Alex

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi, alexyin1053 

        Your clarification has helped me better understand your needs. You expect to get a list of users with access to each Power BI report. This is available through the API. I wrote a python code:

        key api : Power BI Reports - Get Power BI Report Policies - REST API (Power bi report) | Microsoft Learn

        key api: Power BI Reports - Get Power BI Reports - REST API (Power bi report) | Microsoft Learn

        import requests
from requests_ntlm import HttpNtlmAuth

url = "http://Yourserver/Reports/api/v2.0/PowerBIReports"
headers = {
    "accept": "application/json, text/plain, */*",
    "accept-encoding": "gzip, deflate",
    "accept-language": "en-US,en;q=0.9",
    "connection": "keep-alive",
    "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/132.0.0.0 Safari/537.36 Edg/132.0.0.0"
}

# replace your username and pwd
username = ""
password = ""

response = requests.get(url, headers=headers, auth=HttpNtlmAuth(username, password))

print(response.status_code)
reportlist = response.json()["value"]
newlist = []
newdict = {}
for item in reportlist:
    newdict = {"id": item["Id"],"ReportName":item["Name"]}
    newlist.append(newdict)
for i in range(len(newlist)):
    url2 = f"http://YourServer/Reports/api/v2.0/PowerBIReports({newlist[i]["id"]})/Policies"
    response1 = requests.get(url2, headers=headers, auth=HttpNtlmAuth(username, password))
    policies = response1.json()["Policies"]
    newlist[i]["Policies"] = policies
    

print(newlist)

        You should be able to get a dictionary like this (which contains a list of user information accessible for each report):

        You can then use this dictionary for analysis or import into Power BI.

         

         

        Best Regards

        Jianpeng Li

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.