Showing posts with label SSRS. Show all posts
Showing posts with label SSRS. Show all posts

Friday 9 June 2017

SharePoint on-premise reporting options

Overview:  As always this really depends on the circumstances but my broad view on reporting for SharePoint is outlined below.

Thoughts:

  • Power BI on-prem. has not been release (at the time of this blog) but will work for SSRS reports and have the Power BI functionality that shall be embedded using an iFrame into SharePoint 1st edition only.
  • The upgrade path from SSRS SharePoint integrated mode is going to be hard, whereas the upgrade path from SSRS native mode will be simple.
  • Power-BI on-prem is not going to support SAML.  Use WAP (Web Application Proxy)/reverse proxy to get NTLM ot Kerbros tickets for authentication.  Only config is ADFS+WAP to access PowerBI on the Extranet.  If you use another Federation service will need to do a passive claim.  For Example if you use SiteMinder, you need to pass onto ADFS + WAP
  • SSRS SharePoint integrated mode shall be available only in SP2013 and SP2016 and not have any continued path going forward.


SharePoint 2013 Options:

SharePoint 2013 BI Options SharePoint 2013 SQL BI Options
Excel Services
PerformancePoint
SharePoint KPI/Filters
SSRS SharePoint Integrated Mode
Power Pivot for SharePoint

The table below shows what you client probably uses historically and the challenge is to map them out to a supportable solution on SharePoint going forward.

BI in SharePoint and the Microsoft stack has drastically changed over the past 2 years.  The next 12 months should keep this pace up with the release on Power BI.  As my knowledge in this area improves I'll update this post.

Other Posts that may be useful:
A good post to read as of EOY 2016 on BI  for SharePoint
SP2013 with SSRS 2012 SharePoint integrated Mode Overview
Installing SSRS on SP2013 Reporting Post

Thursday 1 May 2014

SSRS 2012 with SP2013 component diagram

I like diagrams as they help me understand faster.  I wrote a post about 9 months ago about installing SSRS for SP2013, and recently a friend called me to moaned about the documentation available and I remember it being very poor.  My posts cover the topic be re-reading the posts and it could be clearer.  The key is to understand the pieces and where they sit.  He had seen my posts but some related to SP2010 and the 1 specific post relating to SP2013 using SSRS 2012 is not instantly understandable.  It has valuable info but where the parts/components reside is not perfectly clear so I put this diagram together.


There are only 4 parts, the fewer servers to have the easier it is to do theses steps.  I.e. on 1 server farms like dev it's pretty easy as all the SQL components are installed hopefully during the initial SQL Server 2012 SP1 install.

Part 1, you need a SP 2013 farm
Part 2, you need to have a database with the SSRS components/functionality install.  Now if you are using the same database as your SP database ensure the SSRS components were installed at the initial build.  This diagram assumes you have a separate SQL instance which is pretty reasonable.
Part 3, you need to run a core SQL install (only needs the SQL component relating to SSRS) on the SharePoint App/web server.  This 1 or more servers containing the SharePoint binaries.  This is the step that is new/different and most people don't do.
Part 4, run some Powershell to create the SSRS SSA and hook up the relationship.  This is done on any of the SharePoint servers such as your main CA box.

This post couple with the overview should help you understand the components needed for large or automated installs.
http://blog.sharepointsite.co.uk/2013/08/installing-ssrs-2012-on-sp2013-within.html


====================


Tip: If you need to move your Reporting Services database to another server, you need to manually add the RSexecRole to the new SQL Server.

Sunday 26 January 2014

Fixing SSRS on SP2013

    Error: "For more information about this error navigate to the report server on the local server machine, or enable remote errors".  When opening a report in SharePoint I get the error shown above.
    Perform these 2 steps to view the error:
    1.> In IE, navigate to the "Reports Library", click "Site Settings" click "Reporting Services Site Settings".  Select "Enable remote errors in local mode".
    2.> Open CA > "Manage service applications" > Select the "SSRS" service application > System Settings > "Enable Remote Errors".
     TBC
     

Thursday 9 January 2014

SP 2013 SSRS failing after RBS enabled and disabled

Problem: I have SSRS (SharePoint mode) enabled on my SharePoint 2013 farm using SQL 2012 which was working, I enabled AvePoint's RBS provider on the farm and enabled RBS on 1 out of 2 web applications.  I then disabled RBS on the web applications and assumed all was good, RBS stopped working and threw 1 of 2 errors on the RBS enabled then disabled web application:
"For more information about this error navigate to the report server on the local server machine, or enable remote errors" or ....

Note: Rdl in the system before RBS was enabled work, during RBS don't work and rdl's added after disabling RBS both fail

Initial Hypothesis/Error tracking:
1.> SSRS and WCA errors unfortunately don't get correlationId's, so I turned off all the SSRS SSA instances except 1 so I know which server to find the error on in ULS log. 
2.> I ran the request for the report (rdl, this report just displays a label so I know it is good) again so the error is captured in the ULS log.
3.> I painfully open the latest log using ULS viewer and scan for errors and I find: 
System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object 'rbs_fn_get_blob_reference', database 'SP_Content_PaulXX', schema 'mssqlrbs'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

4.> Now I am going nuts as RBS has been disabled and I decide to trace the request in SQL Profiler, I can't find the call in SQL Server profiler and while looking for it I realise the function is not in the database.  I also find a post suggesting changing permissions but as I don't have the function, permissions isn't my issue. 
5.> I start looking at RBS on the farm using PowerShell PS>
cls
$cdbs=Get-SPContentDatabase
foreach ($cdb in $cdbs)
{
 $rbs=$cdb.RemoteBlobStorageSettings
 Write-Host "Content DB:" $cdb.Name
 Write-Host "Enabled:" $rbs.Enabled

}
I notice that the content database  'SP_Content_PaulXX' mentioned in the ULS log has the RemoteBlobStorageEnabled flag set to true.

Resolution:

*************************************************
Problem: I have SP2013 + SP2012, I am using SSRS in SP mode.  My app pool accounts for my web app and my SSRS SSA are different and on separate servers.  So for this to occur, you need SP2013, SSRS, RBS Enabled (or the Content database still thinks RBS is enabled), additionally the service account used by the SSRS SSA needs to have minimal permissions.  Existing rdl files display correctly however any rdl files added throw an exception.  The diagram below further explains the scenario:
Initial Hypothesis: Trawling through the ULS logs show the error: System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object 'rbs_fn_get_blob_reference', database...

A snippet of the ULS is shown below:

 
Resolution:  The app pool account used by the SSRS Service Application needs to have permissions to run the function. 
1.> Figure out the app pool account used by the SSRS SSA if you don't know it as shown below:

2.> Give the SP_Services account permissions over the erroring execution calls.  To prove it give the account dbo rights.

 
Thanks to Sam Keytel and Mark Oburoh for looking at this with me.
 

More Info:
 
 


Monday 16 September 2013

SSRS - Reporting Library lables for SQL Reporting Services

Problem:  I am building a 14 SP 2013 server farm for my client.  I automate the installation of SSRS onto the SP farm (the SQL installation is also automated).  I use Powershell to create a reports library and add the SP SSRS CT's to the library.  On smaller farm installs it all works on my farm the labelled to the CT look odd e.g. $Resource.....


Environment: 10 Search servers, 2 WFE's and 2 App servers.  I have 3 SQL nodes made up of 6 SQL Servers to hold the databases.  This is an Always on Availability Group per 2 SQL servers.  So a dedicated search database, dedicated SP and a SSRS/SSAS SQL cluster.  This is SP2013 with SQL 2013 SP1 on Windows 2008 R2 on VMware.  The install of SP is based off AutoSPInstaller.

Initial Hypothesis:  I was concerned of adding the SQL roles or the version on the app servers where the SSRS service resides, this is not the issue.  It looks like the labels to the CT's are missing in the UI.  It appears to be working from my initial testing, so it is purely a cosmetic bug.

Resolution: I installed the SQL_RS feature on the 2 WFE's and added the services to each VM (i.e. install SSRS Service app on the web front end servers).  The labels show up correctly.  My next test will be to stop the SSRS services on the WFE's. I believe this will still run correctly. 

Note: This is probably a good idea to do anyway as I can easily add the SSRS service at a later stage to more servers without having to install the SQL_RS feature.

Update 2014-03-03:  On a daily build environment consisting of 2 WFE and 4 App servers, I recently had a issue whereby SSRS installed and the SSA for SSRS is created using Powershell.  I can see the SSA and the service running on 2 of the APP servers in the farm.  CA is running on another app server and SSRS is not showing up in CA.  If I install SSRS on the CA box it all shows up in CA.  I assume the feature is not being activate and if activate would show up correctly.

 

Thursday 22 August 2013

SSRS deployment for a developer machine

Problem:  I need to use SSRS 2012 in SharePoint mode on a single SharePoint 2013server.

Background:  On my developer VM I has SQL 2012 and SP2010.  SQL was installed with the "Shared Features":
1) Reporting Services - SharePoint &
2) Reporting Services Add-in for SharePoint products.
I installed the services and SSA and was successful in setting up SSRS.  The problem is when I went to UAT which consisted of WFE1, WFE 2 and SQL Server that will use SSRS.

Determine the Components you will need.  In my case this is:
SQL Server 2012 SP1SQL Server 2012 SP1SharePoint 2010.

Single server SharePoint 2010 and 2013 farms and SQL Server are easy to setup as all the components are installed as part of a Full SQL installation.

Once SharePoint and SQL 2012 SP1 is installed you will see the SQL reporting Services on the farm.  I needed to setup an SSRS SSA:
PS> Install-SPRSService
PS> Install-SPRSServiceProxy
PS> get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance


Create a SQL Server Reporting Services Service Application within Central Admin

Other Info:
Install Reporting Services SharePoint Mode as a Single Server Farm

Summary:  AutoSPInstall proved too difficult for me to implement SSRS on SP2010.  There are a lot of permutations and writing a plug-in for autoinstaller does not warrant the effort.  Farm deployments get complex and it probably best to figure out you own scenario and automate from there.

See my blog post on Installing SSRS 2012 in SharePoint Mode on SP2010 as a starting point.
 

Sunday 18 August 2013

Installing SSRS 2012 on SP2013 within a farm

Overview: SSRS in SharePoint mode had a big architecture change before this exact combination of software.  Since SP2010 and SQL 2012 the architecture has been improved to use SharePoint Service Applications. 

Below is a diagram to explain how SSRS works when using SQL Server 2012 SP1 and SharePoint 2013 on-prem.


The high level steps I followed are:
1.> Install SSRS - SharePoint mode using SQL Server 2012 SP1
2.> Install the SQL Server SharePoint Add-in for SSRS feature on 1 or more WFE/App SharePoint servers
3.> On the WFE server ensure the SQL Server Reporting Services Service status is "Online" as show below:
PS> Install-SPRSService
PS> Install-SPRSServiceProxy
PS> get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance
4.> Create a SQL Server Reporting Services Service Application within Central Admin:
5.> Activate PowerView Site Collection Feature. Verify the site collection feature "Power View Integration Feature" is activated.

6.> Verify installation:

  • CA > Manage Servers in the farm > Verify the Service "SQL Server Reporting Services Service"is on the WFE/App server.
  • CA > Manage Services on Server > Select the Server where the SSRS-add-in has been installed and > Verify the "SQL Server Reporting Services Service" service is "Started".
  • Using the Discovery Reports discussed above verify that the SQL Server with SSRS (SP mode is the same SQL version as the web/app servers in the farm (all my servers are version 11.1.3000.0 - SQL 2012 SP1).

  • 7.> Open the Site Collection and create a new "Report Library" as shown below:

    8.> Navigate to the reports library e.g. SSRS Demo.  Click the "Library" tab and the "Library Settings" Ribbon button.  Click "Advanced Settings" and verify the setting "Allow management of content types?" is set to "Yes". 

    9.> Add 3 SSRS reporting content types to the reporting library.  Navigate to the reports library e.g. SSRS Demo. Click the "Library" tab and the "Library Settings" Ribbon button. Click "Add from existing site content types", then add the 3 Report Content Types shown below (if you don't see this check your install steps especially versions of SQL):

    Dodgy Note: Depending on the order that the Site Collection and adding the SSRS service is installed in you may need to activate the following features: on the Site Collection go > Site Settings > Site collection features > Ensure the "Report Server Integration Feature" is enabled.  If you don't see the SSRS reporting Content Types, check out this post.

    10.> Add reports Navigate back to the library and ensure you have the options to add Reports ect.  Upload or create reports and verify SSRS in SharePoint mode is working.


    Note: For more detailed steps look at my series on SharePoint 2010 SSRS using SQL 2012.  As mentioned the architecture is the same so and the steps work on SP2010 and SP2013.  The 1st post is here.

    Part 3 Update (This Post)  - Installing SSRS 2012 SP1 onto a SP2013 Farm



    Updated 01 May 2014: Here is a diagram to show the SSRS components in diagram form so you can understand where the pieces sit.


    More Info:

    http://stevemannspath.blogspot.co.uk/2012/10/reporting-services-2012-integrated-mode.html
    http://msdn.microsoft.com/en-us/library/jj219068.aspx
    http://technet.microsoft.com/en-us/library/bb326289.aspx
    http://get-sharepoint.com/2013/06/sql-2012-ssrs-integration-with-sharepoint/
     
    ============================================================

    Problem:  Installed SSRS on 2 App/WFE servers, PS working but the Servers in farm and services not seeing the SSRS Services are not displaying in CA.


    Resolution:  Installed the SSRS components on the CA machine.
    ============================================================

    Problem: When using PowerGui to create the 'Report Library' that will host my SSRS reports, I can't create the library, while debugging I notice that when i look at the current web I can't retrieve the available 'ListTemplates' whichis returning "$null", I get the following error message on the property: "This operation failed because the QueryInterface call on the COM component for the interface with IID '{BDEADF28-C265-11D0-BCED-00A0C90AB50F}' failed due to the following error: Bad variable type"

    Resolution: My work around was to run the Powershell code out of the native Windows PowerShell cmd prompt.  I think PowerGui is the issue.

    Thursday 11 July 2013

    Importing an SSRS report and displaying it on a new environment

    This is a 3 Series Post on SSRS 2012 for SharePoint 2010


    Part 2 - (This Post) Importing an SSRS report and displaying it on a new environment

    Overview:  This post looks at moving your SQL Server Report Services Report into a new farm (e.g. production).  I use the UI in this step-by-step guide.  I am using SQL 2012 for SSRS and SP 2010 as created in this post.

    Assumptions:
    1. SP 2010 WFE's
    2. SQL 2012 SP1 is the database server
    3. Report (.rdl) is already created and uses a .rsds file to specify the connection string. 
    Steps to migrate and deploy an existing SSRS report:
    1. CA > Application Management > Manage Service Applications.
    2. New > SQL Server Reporting Services Service Application > Fill in as shown below:
















    3. Verify the Service Application is running.













    4. Once Completed.  Open a Web Application that has the SSRS Service Application associated to it so we can test it out.  Login to a site collection and create a new library based on the "Reports Library" template.

    5. Upload a report (.rdl) and the Connection (.rsds) file int he new "Report library". Then click the dataset/connection (.rsds) file, and test it is working.

    6. Edit the binding of the rdl to it rsds as shown below:

    7. Ensure you have the SP, scheme ect on the database server to perform the query logic.
    8. Click on the report (rdl) file to see it working.
    Tip: The install is fragile in that if the order, versions or settings are not right it doesn't work without giving error often.  Make sure SQL Servsions on all machines are the same!  I got caught by not having the same SQL version on 1 WFE as the backend SQL SSRS server.
    This is a 3 Series Post on SSRS 2012 for SharePoint 2010

    Part 2 - (This Post) Importing an SSRS report and displaying it on a new environment
     
    Also I have an updated post on using SP2013 to us SSRS in SharePoint mode here.
     
    Update: 24 Jan 2014 - PS to upload rdl's and data sources into a reporting library
     
    $spWeb = Get-SPWeb "http://demo.dev/"
    $spList = $spWeb.Lists["SSRS"]
    $file = [io.file]::ReadAllBytes('C:\test.rdl')
    $spList.RootFolder.Files.Add("test.rdl",$file)
    # This code finds the SSRS reporting library called "SSRS" and takes a rdl file off the local drive and adds it to the library.

    Installing SSRS 2012 in SharePoint Mode on SP2010


    This is a 2 Series Post on SSRS 2012 for SharePoint 2010.  Note: SP2013 steps are the same.

    Part 1 - (This Post) Installing SSRS 2012 in SharePoint Mode on SharePoint 2010
    Part 3 Update - Installing SSRS 2012 SP1 onto a SP2013 Farm
     
    Overview:  Install SSRS 2012 onto an existing SP 2010 farm.  The image below shows a 4 server SP2010 farm.  2 of the web server roles (WFE) have the "SQL Server Reporting Services Service" running.  The 2 images below show the setup we are aiming for. 
    Goal: SSRS working on a SP2010 farm using SQL 2012 / SSRS 2012
    Another way of displaying what I am trying to achieve is document below:
    There are essentially 2 parts:
    1) Install SSRS on the database server (this does not need to be the same as the SharePoint database server/s) that will support SSRS in SharePoint mode mode.  This relies on SQL Server 2012 Enterprise edition (I used SQL Server 2012 SP1). 
    2) Install minimal SQL Server 2012 with the 2 features mention: "Reporting Services - SharePoint" and "Reporting Services Add-in for SharePoint Products" on each WFE that will run the SSRS role.

    The image below shows the SQL Server 2012 installation required for the two roles:
    1) For the SSRS server click/select all features as shown below:
    2) For the SQL/SSRS role on the WFE's install the 2 features marked in red (ignore the checks on the other features). 


    If you have an existing SQL Server instance you can verify which features are installed as shown below:
     
    You can do this on the SQL Server and the WFE's to check the correct versions and features are being used.
    Tip: Ensure the SQL Server Server 2012 versions are identical on the datbase server and the WFE/App servers.

    These are the 2 SQL Server 2012 features to needed for SSRS (SharePoint Mode) on the SharePoint App/WFE servers that will run the SSRS Service (Features if you automate the SQL installs -FEATURES=RS_SHP,RS_SHPWFE):
    RS_SHPReporting Services – SharePoint (Installed on the SharePoint WFE where the Reporting Service Application will\ be run).
    RS_SHPWFEReporting Services Add-in for SharePoint Products (Installed on the SharePoint WFE where the Reporting Service Application will\ be run).

    Provision the SSRS Service on the Farm using PowerShell:
    Install-SPRSService

    Install-SPRSServiceProxy
    Get-SPServiceInstance -all | where {$_.TypeName -like "SQL Server Reporting*"}| Start-SPServiceInstance



    Validate the installation:
    • CA > Manage Servers in the farm > Verify the Service "SQL Server Reporting Services Service"is on the WFE/App server.
    • CA > Manage Services on Server > Select the Server where the SSRS-add-in has been installed and > Verify the "SQL Server Reporting Services Service" service is "Started".
    • Using the Discovery Reports discussed above verify that the SQL Server with SSRS (SP mode is the same SQL version as the web/app servers in the farm (all my servers are version 11.1.3000.0 - SQL 2012 SP1). 
    More Info:

    Errors to Lookout for:

    Problem: Install SSRS on my SP2010 farm - I get the following error:
    Exception calling "Provision" with "0" argument(s): "Installation Error: Could not find SOFTWARE\Microsoft\Microsoft SQL Server\110 registry key.

    Initial Hypothesis:  Originally I thought I could overcome the issue by updating the folder layout and registry on each WFE that would run the reporting role.  I could not get this to work.

    Resolution:  Don't use the rsSharePoint.msi file (even the SQL 2012 SP1 version).  You need to install the 2 features (1)Reporting Services – SharePoint and 2)Reporting Services Add-in for SharePoint Products.) during/using a SQL installation.

    This is a 2 Series Post on SSRS 2012 for SharePoint 2010
    Part 1 - (This Post) Installing SSRS 2012 in SharePoint Mode on SharePoint 2010
    

    Tuesday 21 May 2013

    AccessDeniedException when deploying Rdl to SP2010 Claims based site


    This Post by Adam Saxton saved me hours of time today.

    http://blogs.msdn.com/b/psssql/archive/2012/07/12/sharepoint-adventures-access-denied-errors-when-using-rs-2012-with-a-claims-sharepoint-site.aspx

    I setup SP2010 with SQL 2012, all appeared to be working until I create a rdl and tried to deploy it into SharePoint from BIDS in VS2010.  Got the error: The permissions granted to user are insufficient for performing this operation. reporting services + AccessDeniedException.

    Resolution: This is a permissions issue on the SharePoint site, I opened the url http://<sitecollection>/_layouts/people.aspx?membershipGroupId=0

    I had 2 identical accounts: demo\administrator & DEMO\Administrator.  I ran the select statement explained by Adam to check the user table on the content db where the site collection sites:
    SELECT tp_login, tp_title, tp_delete from UserInfo

    I deleted the NT account (not the claim account) using the SP UI (http://<sitecollection>/_layouts/people.aspx?membershipGroupId=0).  Re-ran the T-SQL query and the offending NT account (DEMO\Administrator) has been virtually deleted by SP.

    I can now publish the report.



    More Info on setting up SSRS for SharePoint: