Sql Server Role for Website Read Only

03 Jul

EN – Ability BI Written report Server Configuration and Security Implementation

One of the recent information visualization tools that is being very popular among the customers is Ability BI. As the tool is new and Microsoft is enhancing their features day by twenty-four hours, at that place are issues, a programmer could face up that has never been encountered earlier.

As a developer, I take a chance to work dedicatedly from installing the report server configuring them, utilize the Brand Bundle and manage security implementation on the reports.

This certificate will depict configuration and security implementations steps for Power BI Report Server, hoping to aid other people with similar Implementation.

What is Power BI Study Server?

Power Bi Written report Server is an on premise report server with a web portal in which you brandish and manage reports and KPIs. Along with it come up the tools to create power bi reports, paginated reports, mobile reports and KPIs. Power BI Report Server is an extension of SSRS with similar wait and feel, just enhanced functionality to host PBIX reports.

Create, deploy, and manage Power BI, mobile and paginated reports on premises with the range of fix-to-utilise tools and services that Power BI Written report Server provides.

Components and Requirements

Power BI Written report Server

The installation is straightforward and Microsoft documents are helpful enough to install successfully.

You can follow the link beneath,

 https://docs.microsoft.com/en-united states of america/power-bi/report-server/install-written report-server

We take used below system configuration:

RAM: 32 GB

Windows Server: 2016 Datacenter

Organisation Type: 64-chip Operating System, x64-based processor

Database: SQL Server 2016 SP2 Enterprise Edition

ORACLE Client

Reports, we are working, are based on ORACLE. Once the reports are published into the report server, it will utilize the server automobile settings to run them. Therefore, it needs ORACLE customer to be installed on the server itself.

SQL Server Database

Just similar SSRS, Power BI Written report Server besides uses 2 SQL Server DBs to store report server object metadata. Therefore, at that place should be a SQL server enterprise instance installed locally or remotely.

Challenges and Mitigations

• The user must be a part of local admin group.
• TNSNAMES.ORA file should exist configured properly with TNS alias proper noun for the DB connection.
• The path of the TNSNAMES.ORA file to be ready in environment variable 'TNS_ADMIN'. Check Oracle bin binder for ORACLE.Fundamental file to run into where to add together TNS_ADMIN variable.
• The Oracle bin path to be fix with admin privileges.
• That should be the Enterprise Edition of SQL server. Developer Edition or RDS will not assistance.
• Nosotros take used SQL Server 2016 SP2 Enterprise Edition

Configure Report Server

After installing the Power BI Written report Server, we demand to configure the report service from Report Server Configuration Manager.

The steps are simple and directed in Microsoft docs. PFB the link:

https://docs.microsoft.com/en-u.s./ability-bi/report-server/install-written report-server

We have used below details, which worked successfully.

• Service Account

My domain user business relationship credentials.

Web Service URL

• Virtual Directory: <Any meaningful proper name that describes the purpose>
• IP Address: All Assigned
• TCP Port: fourscore
• The URL will exist generated automatically

Database

Go to Change Database and bespeak the report server to the SQL Server Enterprise Edition case. PFB the link:

https://docs.microsoft.com/en-gb/sql/reporting-services/install-windows/ssrs-report-server-create-a-report-server-database?view=sql-server-2017

Web Portal URL

• Virtual Directory: <Any name that would be used in the URL>
• The URL will be generated automatically

Note: The residual is non required for the basic configuration.

Challenges and Mitigations

• Network Problems:

If you become any network related error,

1. Yous tin can cheque if the connection properties have "Obtain DNS Server Address Automatically" checked. You can cheque information technology from, Control Console >> Network and Internet >> Network Connections >> Right Click on the Connexion >> Properties >> TCP IPv4 >> Backdrop >> Select Obtain DNS Server Address Automatically.
2. Bank check if TCP/IP is enabled on the car.
3. Check if the TCP port is open.
4. Cheque if the SQL Server metadata DB is in the same domain and firewall is not blocking anything.

• Database Permission Issues:

If you become any DB related error,

1. The SQL server should exist the Enterprise Edition. RDS instance or Developer Edition will not work.
2. The User credentials used to configure the DB should accept SYSADMIN and Public roles on the SQL Server.
3. Local installation and remote instance both will work. PFB the link:

https://docs.microsoft.com/en-gb/sql/reporting-services/install-windows/configure-a-report-server-database-connexion-ssrs-configuration-managing director?view=sql-server-2017

Connecting to Study Server DB

You lot can connect to the report server DB instance from SQL Server Direction Studio to check if the user has the roles required or the DBs are created.

Challenges and Mitigations

• If in that location is whatever issue logging in, first bank check with the admin if you have the correct credentials.
• The server name will exist <hostname>, <port no>.
  Ex. 150.ix.eight.15, 1166

• If at that place is any problem logging in from the server machine, attempt to log in from any other machine in the aforementioned domain.

Connecting to Report Service

After successfully configuring the report server, you should be able to log in to the reporting service from SQL Server Management Studio. This tin be used to bank check the default roles, create or edit new roles and manage the scheduling.

If the server is configured correctly, yous will be able to see 'PowerBIReportServer' service running on the machine where server is installed.

Y'all can connect to the reporting service from SQL Server Management Studio. Get to object explorer and select 'Reporting Services'. You should see the Reporting Server instance in the dropdown. Enter credentials and Log in.

Challenges and Mitigations

• If in that location is no instance in the dropdown, you demand to register the service in SSMS. In order to do that,

View >> Registered Servers >> Reporting Services >> Local Server Groups >> Right Click >> New Server Registration >> Put the 'Spider web Service URL' and test.

Beginning the web portal

1. Open up your web browser. Learn about what browser versions are supported for managing and viewing Power BI Report Server and the Report Viewer Controls. See this list of supported web browsers and versions.
2. In the accost bar, type the spider web portal URL.
3. By default, the URL is https://[ComputerName]/reports.
4. The study server might be configured to use a specific port. For example,
   https://[ComputerName]:80/reports
   Or
   https://[ComputerName]:8080/reports

Yous can use the web portal to perform the following tasks:

• View, search, print, and subscribe to reports.
• Create, secure, and maintain the binder bureaucracy to organize items on the server.
• Configure office-based security that determines access to items and operations.
• Configure report execution properties, report history, and report parameters.
• Create shared schedules and shared information sources to make schedules and data source connections more manageable.
•  Create data-driven subscriptions that role out reports to a large recipient list.
• Create linked reports to reuse and repurpose an existing report in different ways.
• Download common tools such as Report Builder and Mobile Report Publisher.
• Create KPIs.
• Transport feedback or make feature requests.

Publishing Report to the Study Server

Later you are done with configuring the report server successfully, you should be able to access it through the Web Portal URL. You can use the same URL to save the reports to the report server.

Challenges and Mitigations

• Cheque if the URL is correct
• Check if you lot are logged in to the server with the same credentials
• You can go some "Unexpected Error Encountered" while saving the reports to the report server. You lot can utilize the Upload functionality from the web portal itself.

Manage Security on the Server

There are cases when you might have to set some security to the folders, objects and the reports in the server based on the users or user groups created in the domain. For this, you can use Manage functionality from the web portal and assign roles to the users. PFB the link:

Y'all have essentially iii layers of access to the report file security in Ability BI Study Server.

• The portal itself tin be secured. Yous tin and should limit access to the reports past only assuasive specific users or grouping'due south admission to the report portal.
• Folders can be used to provide more granular security over a group of assets in the written report portal. In the prototype higher up, I created a folder chosen PBI Secure Reports. A specific Advert group has access to this folder. If a user does non have permissions to the folder, the folder does not evidence up in the portal and they cannot access the folder or the avails, including Ability BI reports, stored in this folder.
• Individual reports can be secured as well. I never recommend this pick as it becomes administratively difficult to manage. However, the capability is in that location is a single nugget needs to be secured in this manner.

Mapping SSRS Roles to Ability BI Functions

The portal, folders, and files are secured using SSRS roles. Here is a high-level summary of the roles and how information technology affects Ability BI Reports.

• Browser – This is like to a read only part or if you would evangelize the written report as a Power BI App in the service. This should exist used by anyone who needs access to the report, simply does not create content.
• Content Manager – This is for a content creator with admin privileges. This role tin can manage content and user admission.
• Publisher – This is for content creators who practise not need to manage users. Most content creators fall into this category.
• Report Builder – does not utilise to Power BI Reports, which utilize Power BI Desktop to develop reports.

https://docs.microsoft.com/en-us/ability-bi/report-server/admin-handbook-overview

Challenges and Mitigations

• The user must be granted access to the written report server.
• In gild to manage security from the web portal, the user must be a role of local admin group.
• The user, on which security is practical, cannot be a role of local ambassador grouping. Users in admin grouping will by default accept admission to all the folder objects irrespective of the security.

Avoid Deploying to the Portal Home Page

I would recommend yous not allow Power BI reports to be deployed to the master portal, only create folders to manage the group of content creators and managers based on the department or group level needs.

Reports deployed to the habitation page of the portal should be managed as enterprise avails. Use the folders and related security groups to manage who can deploy at this level.

Branding the web portal

You lot can alter the appearance of the web portal by branding it to your business organisation. This is done through a brand package. The brand bundle is designed and then you do not demand deep cascading style sheet (CSS) noesis to create information technology.

Creating the brand package

A brand package for Reporting Services consists of 3 items and is packaged as a zip file.

• colour.json
• metadata.xml
• logo.png (optional)

Applying the brand package to the web portal

To add, download, or remove a brand bundle, you can do the post-obit.

1. Select thegear in the upper right.
2. SelectSite Settings.
3. SelectBranding.

Currently installed make package will either brandish the name of the package that has been uploaded, or it will display none.

Upload make packet will apply the package to the web portal. Yous volition see it take effect immediately.

Y'all tin alsoDownload orRemove the parcel. Removing the bundle will reset the web portal to the default brand immediately.

The steps are unproblematic and directed in Microsoft docs. PFB the link:

https://docs.microsoft.com/en-united states of america/sql/reporting-services/branding-the-web-portal?view=sql-server-2017

For Case,

Before Applying brand package – This is a past default bundle for Power BI Written report Server

After Applying make package – This is a XBOX brand Parcel

Wrap Upwardly

I would like to extend a special thanks to Nityanand and Rudra for their guidance and support on implementing Power BI Report Server. Thanks Nitin for the assistance on this topic.

nakamuraroyhorder.blogspot.com

Source: https://www.bconcepts.pt/power-bi-report-server-configuration-and-security-implementation/

Share this post