Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Insert user data through power bi report database to access reports

Hi All,

 

I have installed one Power bi Report server in my local machine. While installation i configured the database name and can access the same database tables using SSMS.

 

We have one requirement here to access any power bi report server without asking authentication. To complete that we found like those user which is present in Power bi Security with System User Role permission can access the Power bi report without authentication.

To achieve this one we have verified the Power bi DB and found like Users and PolicyUserRole table is mapped with the power bi report server when add new user through UI.

 

We added the same user in both of the tables and found one miss of SID column in Users table. What we found here like SID is nothing but system machine key. That we added also. But still the inserted user is not exist in Power bi report server UI Security.

 

How could we achieve the same to complete it. If we are able to manage this then user can access reports without authentication.

 

Please suggest. It is an urgent task.

 

Thanks,

Vikash

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi d_gosbell,

 

Thank you for the suggestion. I have gone through each documentation as you mentioned and finally able to set the new user with role. It works fine for the 1st user but when again i tried to add another user with same role then got below error:

 

"A custom role cannot contain both system-level and non-system-level tasks in the same role definition. You must specify different roles for each category of tasks"

 

Below is my code which i used so far.

 

 

bool isPolicies = true;
                ReportingService2010 rs = new ReportingService2010();
                rs.Url = "http://<domainName>/ReportServer/ReportService2010.asmx";
                rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
                var policies = rs.GetPolicies("/", out isPolicies);
               
                Role browserRole = new Role();
                browserRole.Name = "System User";
                Role[] r1 = new Role[1];
                r1[0] = browserRole;
                Policy po = new Policy();
                po.GroupUserName = @"<domain>\<username>";
                po.Roles = r1;
                policies[0] = po;
                rs.SetPolicies("/", policies);

 

One more things i wanted to add here is suppose i added one user with some role and when again i am going to add some other user then in getPolicies i am getting the previously inserted user and his role instead of BuiltIN/Administrator.

 

I tried to use my crednetials but in Policies getting the same user again.

Please provide some suggestion based on the credentials should get only those related policies.

 

Please suggest how do i fix this issue.

 

Thanks,

Vikash

View solution in original post

11 REPLIES 11
d_gosbell
Super User
Super User

So first off you should NEVER manually insert data into the underlying SQL database, this is not a supported way of adding users. You should either use the user interface through the PBIRS portal or use the powershell or REST apis. We typically do not add a lot of individual users, we normally get an AD group setup, then add that group to the portal, then you just add/remove people from the AD group to control access. If everyone in your organization should be able to access the reports you can even add one of the built-in groups like "Domain Users"

Anonymous
Not applicable

Hi d_gosbell,

 

Thank you for the suggestion.

 

i have gone through the swagger api where all the api is mentioned and found one forums questions also to manager users using api. But did not get the specific way mentioned like what api url could you to insert user for the role so that he can access the Power bi reports without authentication?

 

Please suggest me to get the api url with the steps so that i can use it accordingly.

 

Thanks,

Vikash

Yes you are right the ability to set security seems to be missing from the REST API, this means that you would need to use the Web Service endpoint instead. (see https://docs.microsoft.com/en-us/sql/reporting-services/report-server-web-service/accessing-the-soap...)

 

Have you looked at the PowerShell option? The ReportingServicesTools module https://github.com/microsoft/ReportingServicesTools has a Grant-RsCatalogItemRole function which will allow you to grant access to reports or folders (and you can see from the source code that it uses the web service api to do this)

Anonymous
Not applicable

Hi d_gosbell,

 

Thank you for the suggested link, I have gone through each link but no idea how to start.

 

For testing purpose i used the reporting service web url in postman and verified like in below screen shot:

 

ReportingServerWebServiceNot.png

As you can see in above screen shot i have tried to access Report server using web service where i have given my report server domain. and used NTLM authentication where i passed my windows credentials but still getting Unauthorized access.

 

Kindly provide some example or steps which i could use to add new user through web service and provide the role for him to access Power bi report.

Anonymous
Not applicable

Hi d_gosbell,

 

I access the reporting server web service through web browser and got all the service in xml format.

 

Could you please let me know in this xml which element i could use to insert new user and assign the specific role?

 

Thanks,

Vikash

I've never used the raw XML against the Reporting Services SOAP endpoint. In Visual Studio I just right click on references and use the "Add Service Reference" option, tell it the url of the asmx file and let it generate a proxy object for me. And I believe many other programming languages have similar tools, utilities or libraries for doing a similar thing.

 

Then the general steps are as follows:

  1. call GetPolicies against the object you want to secure to get any existing permissions
  2. construct a new Policy object and add it to the array of policies you got from Step 1 (a policy has the user or AD Group and the Role they should have)
  3. call SetPolicies and pass in the updated array of Policy objects from Step 2

 

You can see an example in the source code for the Microsoft PowerShell module  here https://github.com/microsoft/ReportingServicesTools/blob/master/ReportingServicesTools/Functions/Sec...

Anonymous
Not applicable

Hi d_gosbell,

 

Thank you for the suggestion. I have gone through each documentation as you mentioned and finally able to set the new user with role. It works fine for the 1st user but when again i tried to add another user with same role then got below error:

 

"A custom role cannot contain both system-level and non-system-level tasks in the same role definition. You must specify different roles for each category of tasks"

 

Below is my code which i used so far.

 

 

bool isPolicies = true;
                ReportingService2010 rs = new ReportingService2010();
                rs.Url = "http://<domainName>/ReportServer/ReportService2010.asmx";
                rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
                var policies = rs.GetPolicies("/", out isPolicies);
               
                Role browserRole = new Role();
                browserRole.Name = "System User";
                Role[] r1 = new Role[1];
                r1[0] = browserRole;
                Policy po = new Policy();
                po.GroupUserName = @"<domain>\<username>";
                po.Roles = r1;
                policies[0] = po;
                rs.SetPolicies("/", policies);

 

One more things i wanted to add here is suppose i added one user with some role and when again i am going to add some other user then in getPolicies i am getting the previously inserted user and his role instead of BuiltIN/Administrator.

 

I tried to use my crednetials but in Policies getting the same user again.

Please provide some suggestion based on the credentials should get only those related policies.

 

Please suggest how do i fix this issue.

 

Thanks,

Vikash

So there are 2 levels of permissions in PBIRS item-level and and system-level (see https://docs.microsoft.com/en-us/sql/reporting-services/security/grant-user-access-to-a-report-serve...)

 

"System User" is a system level permission so I think you would need to use SetSystemPolicies to grant this. 

(Note: I've never used the security APIs so I'm just theorising based on reading the docs)

 

But granting "System User" will not give access to any reports, you would also need to grant at least "Browser" rights using SetPolicies against a folder or report ( I usually try to only set permissions at the folder level if I can)

 


@Anonymous wrote:

 

One more things i wanted to add here is suppose i added one user with some role and when again i am going to add some other user then in getPolicies i am getting the previously inserted user and his role instead of BuiltIN/Administrator.

 


This is because you are not adding a new role to the collection of roles you got from the GetPolicies call, you are overwritting the first policy (so this is probably removing the BUILTIN\Administrators). You need to add your new policy object to the array of policies instead of assigning over the top of policies[0]

 

So instead of overwriting the first item in the array:

policies[0] = po;

you should probably be doing something like the following to add your new "po" object on to the end of the array:

policies = policies.Concat( new Policy[] { po } ).ToArray();

 

Anonymous
Not applicable

Hi d_gosbell,

 

Thank you for the suggestion. It is working fine for me.

The only issue here is when the user which is added through policy. That user if trying to access Report server it is asking the user name and password credentials. For that if manually add the domain as Trusted Sites in Internet options and again refresh the url he can able to access the reports.

 

Can we bypass this authentication for the Powerbi User?

 

Can we delete user and assigned role using web service?

 

Thanks,

Vikash

 


@Anonymous wrote:

Hi d_gosbell,

 

Thank you for the suggestion. It is working fine for me.

The only issue here is when the user which is added through policy. That user if trying to access Report server it is asking the user name and password credentials. For that if manually add the domain as Trusted Sites in Internet options and again refresh the url he can able to access the reports.

 

Can we bypass this authentication for the Powerbi User?

 


You can't do anything from the report server side to change this, it's a browser security setting. You will need to talk to your domain or network admins as they can set this via group policy so that you site is automatically added to either Intranet sites or Trusted sites for all users.

 


Can we delete user and assigned role using web service?

 


My guess is that you just call GetPolicies find the user in the array of policies, remove the items from the array that you no longer want then call the SetPolicies method passing in the array without the policies that are no longer required.

Anonymous
Not applicable

Hi d_gosbell,

 

Thank you for the suggestion. I have got the answer which i asked.

 

I am marking it as answer.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.