http://technet.microsoft.com/en-us/library/gg492249.aspx#bkmk_cmdlet_sum
SharePoint 2013 and SQL Server 2012 PowerPivot April 13, 2013
Very good on architecture and trouble shooting of that architecture. All new now with Excel Services taking the main role. Use spPowerPivot.msi to install the correct data providers on SharePoint Servers.
And the following is very important for the security planning.
The data providers establish direct TCP/IP connections to the data models on the SSAS server. This direct approach helps to minimize latencies in the client/server communication, which ultimately provides best performance, but it also requires the security accounts of the client processes to have SSAS administrator permissions. Only SSAS administrators can load data models on an Analysis Services instance running in SharePoint mode.
And the following for architecture and troubleshooting. Notice that SSRS is installed on the SharePoint Application Server.
and then install and configure SharePoint 2013 and Reporting Services Power View on a separate application server. Verify that Excel Services can load data models and then try to create a Power View report on top of a workbook. If all goes as advertised, then you should be able to interact with the workbook in the browser, but Power View won’t be able to create a report. Now, don’t jump to conclusions. Leave the Reporting Services configuration alone for a moment. Let’s first double-check the dependencies for workbooks as a data source by running some more or less straightforward PowerShell scripts. Start SharePoint 2013 Management Shell with elevated permissions and then copy and paste the following lines:
Is at least one SSAS server registered in Excel Services?
Is the correct data provider version installed?
Can you connect to the SSAS server directly?
Can you connect to a workbook in the local farm?
Is the SPClient assembly installed on the SharePoint server?
And there you have it, SPClient is missing! Apparently, I forgot to install the data providers via spPowerPivot.msi on this computer. I installed Reporting Services using SQL Setup, but in SQL Server 2012 SP1 CTP3, the Reporting Services options do not install the SPClient assembly. This is fixed in later builds. For CTP3, downloading and installing spPowerPivot.msi on the SharePoint server solves this problem. Note that you don’t need to run the PowerPivot Configuration Tool. Simply install spPowerPivot.msi to fully deploy the data providers included in the installer package, as illustrated in the following screenshot.
http://msdn.microsoft.com/en-us/library/jj218792.aspx
PowerPivot for SharePoint 2013 is a Microsoft Windows Installer package (spPowerpivot.msi) that deploys Analysis Services client libraries and copies PowerPivot for SharePoint 2013 installation files to the computer. The installer does not deploy or configure PowerPivot features in SharePoint. The following components install by default:
- PowerPivot for SharePoint 2013. This component includes PowerShell scripts (.ps1 files), SharePoint solution packages (.wsp), and the PowerPivot for SharePoint 2013 configuration tool to deploy PowerPivot in a SharePoint 2013 farm.
- Microsoft OLE DB Provider for Analysis Services (MSOLAP).
- ADOMD.NET data provider.
- SQL Server 2012 Analysis Management Objects.
http://msdn.microsoft.com/en-us/library/6401fd92-f43b-450e-8298-12db644c25bc
Configure PowerPivot and Deploy Solutions (SQL Server 2012 SP1)
This topics describes the deployment and configuration of middle-tier enhancements to the PowerPivot features in SharePoint 2013 including PowerPivot Gallery, Schedule data refresh, Management Dashboard, and data providers. Run PowerPivot for SharePoint 2013 Configuration tool to complete the following:
- Deploy SharePoint solution files.
- Create a PowerPivot service application.
- Configure an Excel Services Application to use an Analysis Services server in SharePoint mode. For information on backend services and installing a Analysis Services server in SharePoint mode, see Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1).
http://msdn.microsoft.com/en-us/library/fe13ce8b-9369-4126-928a-9426f9119424
Install or Uninstall the PowerPivot for SharePoint Add-in
A recommended best practice is to install spPowerPivot.msi on all servers in the SharePoint farm for configuration consistency, including Application servers and web-front end servers. The installer package includes the Analysis Services data providers as well as the PowerPivot for SharePoint 2013 configuration tool. When you install spPowerPivot.msi you can customize the installation by excluding individual components.
Data providers: Several SharePoint and SQL Server technologies use the Analysis Services data providers including Excel Services, PerformancePoint Services, and Power View. Installing spPowerPivot.msi on all SharePoint servers ensures the full set of Analysis Services data providers and PowerPivot connectivity is consistently available across the farm.
SharePoint 2013 and SQL Server 2012 SP1 Installation April 12, 2013
http://msdn.microsoft.com/en-us/library/jj218795.aspx
What’s New in SQL Server 2012 SP1 Installation for Business Intelligence
- PowerPivot for SharePoint: Analysis Services provides a backend service for Excel Services to load, query, and refresh PowerPivot data models so that users can interact with Excel workbooks that contain data models in the browser. Analysis Services in SharePoint mode is fully independent of and external to SharePoint, although the server running Analysis Services must be on the same network and Active Directory Domains Services (AD DS) forest as the SharePoint farm.You install and manage Analysis Services using SQL Server installation media and tools. Choose the setup option PowerPivot for SharePoint. After you install Analysis Services in SharePoint mode, the only additional configuration tasks are to grant the SharePoint services accounts server administrator permissions in Analysis Services and to configure Excel Services to point to the Analysis Services instance.For detailed steps on installing a single server, see. Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1).
- spPowerpivot.msi: A Windows Installer package available with the SQL Server 2012 SP1 Feature Pack. The installer enhances the PowerPivot for SharePoint experience with more features such as PowerPivot Gallery and Schedule Data Refresh. The .msi deploys Analysis Services client libraries, the PowerPivot for SharePoint 2013 Configuration tool, and copies PowerPivot for SharePoint 2013 installation files to SharePoint servers.After you run the .msi, you need to run PowerPivot for SharePoint 2013 Configuration to deploy the solutions and configure the farm. For more information see:
- Reporting Services: The overall Reporting Services installation remains the same with SQL Server 2012 SP1 as it was with SQL Server 2012. There is an updated Reporting Services for SharePoint add-in that supports SharePoint 2013. For more information, see Supported Combinations of SharePoint and Reporting Services Components.
Overview of Installation with SharePoint 2013
The following is a summary of the installation steps to deploy SQL Server 2012 SP1 BI Features on a single server.
- Install SharePoint Server 2013 and enable Excel Services.
- Install Analysis Services in SharePoint mode, and grant the SharePoint farm and services accounts server administrator rights in Analysis Services.For more information, see Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1).
- Configure Excel Services in SharePoint Server 2013 to use the Analysis Services instance you installed in step 2.See the section “Configure Basic Analysis Services SharePoint Integration” in Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1).
- Install Reporting Services in SharePoint mode and the Reporting Services add-in for SharePoint products.For more information, see Install Reporting Services SharePoint Mode for SharePoint 2013.
- Configure the Reporting Services SharePoint service and at least one Reporting Services service application.For more information, see the section “Create a Reporting Services Service Application” in Install Reporting Services SharePoint Mode for SharePoint 2013.
 Note |
|---|
| If you want to use both Analysis Services and Reporting Services, run SQL Server Installation Wizard twice because the PowerPivot for SharePoint option does not offer choices on the Setup Role page. |
The SQL Server setup option PowerPivot for SharePoint has no dependencies on SharePoint. It does not use the SharePoint object model or interfaces to support integration. Therefore, Analysis Services can be installed on any computer running Windows Server 2008 R2 or later version. It can be but does not have to be an application server in a SharePoint farm. One of the configuration steps is to point Excel Services to the server running Analysis Services. For load balancing and fault tolerance, it is recommended to install and register multiple Analysis Services servers running in SharePoint mode.
PowerPivot for SharePoint 2013 and Reporting Services in SharePoint mode Single Server Deployment
A single server deployment is useful for testing purposes but it is not recommended for production deployments.
| (1) | Excel Service Application. The service application is created as part of the SharePoint installation. |
| (2) | PowerPivot Service Application. Default name is Default PowerPivot Service Application. |
| (3) | Reporting Services service application. |
| (4) | Install the reporting services add-in for SharePoint from either the SQL Server 2012 SP1 installation media or the SQL Server 2012 SP1 feature pack. |
| (5) | The SharePoint content, configuration, and service application databases. |
| (6) | Analysis Services Server in SharePoint Mode. Configure the Excel Services Application Data Model Settings to use this server. |
http://whitepages.unlimitedviz.com/2012/07/using-the-new-excel-2013-data-model-with-sharepoint/
Follow the remaining prompts to complete the installation. After the installation completed, resist the urge to run either of the PowerPivot configuration tools. These are used if we actually ARE using full PowerPivot (see below), and that will require a SharePoint server. If you run it on a non SP farm machine, things can get very messed up.
Once that is set up, you need to tell Excel services to use it. Navigate to your Excel Services service application in Central Administration, and select “Data Model Settings”.
Now that the engine is baked into Excel, is there any need for PowerPivot any longer? As we’ve seen, for simple analysis, no. However, if we want to do anything advanced, like filtering import data, modifying the model, creating complex relationships, or using DAX (the tabular answer to MDX), the answer is emphatically yes.
On the client side, PowerPivot is actually included with Excel – no download required anymore. It is disabled by default, and is enabled through the Excel Com Add in interface. You can view this as your individual or team model designer. With it you can create complex models and then share them out via SharePoint. Optionally these very same models can be imported into Analysis Services projects if/when they become mission critical, or too large. Another nice thing is that models created with PowerPivot can be shared on a SharePoint farm that is not using the full PowerPivot for SharePoint (but does need “Analysis Services in SharePoint mode – confused yet?).
On the Server side, we can install PowerPivot for SharePoint, just as we did with SharePoint 2010. The reason that we would want to do this is to gain access to PP for SP features like the Pivot Gallery, but primarily to access refresh capabilities. In the scenarios that I’ve described above, using Excel Services, the data that we’re using is relatively static. Data is imported when creating the model, and is used for analysis, but there is no mechanism to refresh the data in the model in the way that Analysis Services can. PowerPivot for SharePoint offers this capability in SharePoint 2010, and this carries forward with SharePoint 2013.
http://whitepages.unlimitedviz.com/2012/07/setting-up-reporting-services-2012-with-sharepoint-2013/
Another thing that you should note is that the order of operations is important here. If you install Reporting Services – SharePoint prior to installing SharePoint on the farm, the option to create a Reporting Services application will not appear. That’s because it won’t be registered with the farm as a service application. If this happens, you can run the following PowerShell to register the Service Application
The reason that I have installed both the 2008 R2 versions and the 2012 versions is that some components in SharePoint 2013 seems to need the 2008 R2 versions while others use the 2012 versions.
The reason for this is that I now want to run a installation of SQL a second time this time I choose to add features to a existing instance
This time I add Reporting Services in SharePoint integrated mode and the Reporting Services Add-in for SharePoint Products. The reason that I am doing this in a separate installation is that I had some issues with SSRS not showing up in Central Admin when I added this in the first installation. This could probably have been solved in some other way but I wanted to ensure that the install went through without any glitches.
After the installation has finished it is time to install PowerPivot for SharePoint. So I start the installation of SQL Server 2012 SP 1 Preview a third time. This time I choose to add a new instance
After the installation has finished it is time to run the configuration for PowerPivot for SharePoint. You can see that there are two different configuration tools one for SharePoint 2010 and one for SharePoint 2013. Since I am using 2013 I start that tool.
Now it’s time to install SQL Server 2012. Note that Service Pack 1 for SQL Server 2012 is a requirement for PowerPivot for SharePoint. If you do not install it you will get an error on the “SharePoint installation requirement for PowerPivot for SharePoint” rule when you try to install PowerPivot for SharePoint.
My previous blog post, “Verifying the Excel Services Configuration for PowerPivot in SharePoint 2013,” discussed steps to verify that Excel Services can load workbook data models on an out-of-farm PowerPivot server running SQL Server 2012 SP1 CTP3 Analysis Services (SSAS) in SharePoint deployment mode so that users can enjoy full workbook interactivity in the browser. But data exploration in the browser is only the first step. The next logical step is to consume the data model as a data source in PerformancePoint Services, Power View, or in a custom solution running inside the farm. Let’s take a look at an architecture diagram to understand how this scenario works in SharePoint 2013.
And there you have it, SPClient is missing! Apparently, I forgot to install the data providers via spPowerPivot.msi on this computer. I installed Reporting Services using SQL Setup, but in SQL Server 2012 SP1 CTP3, the Reporting Services options do not install the SPClient assembly. This is fixed in later builds. For CTP3, downloading and installing spPowerPivot.msi on the SharePoint server solves this problem. Note that you don’t need to run the PowerPivot Configuration Tool. Simply install spPowerPivot.msi to fully deploy the data providers included in the installer package, as illustrated in the following screenshot. Rerunning the PowerShell script now shows that the SPClient assembly is present. Of course, you can also check Programs and Features in Control Panel to see if the spPowerPivot.msi package is installed.
SharePoint BI Setup September 9, 2012
http://technet.microsoft.com/en-us/sharepoint/ee692578.aspx
Links to setup Performance Point.
SQL Server 2012 SSRS SharePoint integrated mode.
http://technet.microsoft.com/en-us/library/ms159106
Reporting Services SQL Server 2012 jump off.
SQL Server 2012 and SSRS June 14, 2012
http://social.technet.microsoft.com/wiki/contents/articles/3942.aspx
SQL Server 2012 Reporting Services Tips, Tricks, and Troubleshooting
Went to test the Data Source link and got the error Cannot convert claims identity to windows token. Started digging around on the web and found numerous articles and blog posts about making sure the Claims to Windows Token Service (C2WTS) is set up and running in SharePoint. We had set this up for performance point in our production environment but not in our test environment. Got out the notes and set up a new service account in test to run the service. Made sure it was given the correct rights on the SSRS server of act like a part of the OS and able to impersonate another identity. Set up the delegation to the SQL server we were trying to hit and started the service. Still got the error Cannot convert claims identity to windows token. Went through this video and tried to verify we had everything set up correctly. Got Microsoft Support on the case and they started looking at the issues as well. I finally found this blog and started focusing on the bz71 error which I found. Tying the bz71 error to the Process ID I noticed that the User Account running the process was the SSRS Service Account. I thought the process of converting a claims identity to a windows token would be handled by the user running the C2WTS and the account associated with that service. It appeared that the process doing that conversion or at least the process throwing the error is actually the service account running the SSRS Service Application. I then set the permissions for the SSRS Service account in the local policy on the SSRS Server to be the same as the C2WTS Account. I restarted the Service Applications via central admin and things started working.
SSRS Sharepoint List Reporting March 23, 2011
http://technet.microsoft.com/en-us/magazine/ff686706.aspx
Native List Reporting
SQL Server 2008 R2 Reporting Services now supports SharePoint lists as a data source. This support allows you to retrieve list data from SharePoint Foundation 2010, SharePoint Server 2010, Windows SharePoint Services 3.0, and Office SharePoint Server 2007. The ability to access list data is not reliant on the add-in or running Report Server in Native or SharePoint Integrated mode. The functionality is built into Report Server. What changes in the different configurations is the method of access.
There are two methods by which SharePoint list data is accessed. One is via the lists.asmx web service and the other is via the SharePoint object model APIs. On any SharePoint installation, if you enter the URL http://<sharepoint_server_name>\lists.asmx, you’ll get an XML list of all the lists on the SharePoint site that you’re able to access. By using this method, Report Builder 3.0 is able to retrieve the lists. A Report Server configured in Native mode also uses this method.
The SharePoint object model API method can be used in two scenarios. One is where a Report Server is configured in SharePoint Integration mode and the list exists in the same SharePoint farm Reporting Services is integrated with, and this is all on the same machine; remember that in this scenario there is a copy of SharePoint running on the Report Server that gives it access to the API set. The other scenario is where you have SharePoint 2010 installed along with the add-in, but you have no Report Server. This is called local mode and is covered later in the section “Reporting Without Reporting Services.”
To use data obtained from a SharePoint list within a report first requires you create a data source, then a dataset that uses that data source. In Report Builder 3.0, there is a new connection type on the Data Source properties page called Microsoft SharePoint List, as shown in Figure 3. Along with this option, you enter the URL of your SharePoint site—no need to add lists.asmx to the URL. The data source can also be configured with different credentials to use when accessing the SharePoint server.
SSRS Host-Header And Kerberos April 21, 2010
http://technet.microsoft.com/en-us/library/cc281382.aspx
HTTP is the service class. The Report Server Web service runs in HTTP.SYS. A by-product of creating an SPN for HTTP is that all Web applications on the same computer that run in HTTP.SYS (including applications hosted in IIS) will be granted tickets based on the domain user account. If those services run under a different account, the authentication requests will fail. To avoid this problem, be sure to configure all HTTP applications to run under the same account, or consider creating host headers for each application and then creating separate SPNs for each host header. When you configure host headers, DNS changes are required regardless of the Reporting Services configuration.
The values that you specify for <computername>, <domainname>, and <port> identify the unique network address of the computer that hosts the report server. This can be a local host name or a fully qualified domain name (FQDN). If you only have one domain and are using port 80, you can omit <domainname> and <port> from your command line. <domain-user-account> is the user account under which the Report Server service runs and for which the SPN must be registered.
http://msdn.microsoft.com/en-us/library/ms155921.aspx
How to: Configure Reporting Services to Use a Non-Default Web Site (Reporting Services Configuration)
SSRS with FQDN and Loopback
To configure the report server or Report Manager to use a FQDN, you must also edit the configuration files.
- Open RSReportServer.config in a text editor.
- Append the port number to the UrlRoot setting in the rsreportserver.config file. For example, if Urlroot is set to http://server/reportserver server, set it to http://www.mikeglaser.com/reportserver instead.
- Open RSWebApplication.config in a text editor.
- Set ReportServerUrl to the same URL you specified in UrlRoot.
- Delete the value (but not the tags) for ReportServerVirtualDirectory.
- Save both files.
<foo> is considered a “Host Header”. It is an alternate name for the computer on which SSRS is installed. You will need to add the NetBIOS and Fully Qualified Domain Name (FQDN) for <foo> to the list of BackConnectionHostNames stored in the Windows Registry. Casing does not matter. Use the steps in Method 2: Specify host names in KB 896861, with the following adjustment. Step 7 of the KB article says “Quit Registry Editor, and then restart the IISAdmin service.” Instead, just reboot the computer. This is the correct solution for security reasons that I’ll explain later in this article.
For example, if <foo> is a Windows machine name like “contoso”, then it likely also can be referenced in FQDN form as “contoso.domain.com”. You will need to add both representations to the list in BackConnectionHostNames.
Cause:
Report Server and Report Manager communicate using a network connection. When configured for a scale-out deployment of Reporting Services, typically a single virtual server name is given to the deployment and the underlying machine names are never used by end-users. The same can happen even for non-scale-out deployments but I would consider it less common. For example, you might call the server www.contoso.com or on the internal network simply “contoso”. These are called Host Headers. We have only observed the issue in this post when using a Host Header; we have not seen it when using just the machine name.
By default, Report Manager is configured for Windows authentication. In this mode, it impersonates the user making the request and uses that user’s credentials to connect to the Report Server. Because the network request is local, the authentication between RM & RS can succeed without running into a double hop authentication issue. In the case described above, it fails mysteriously. Let’s see why…
The exact authentication mode used by both RS and RM is defined in rsreportserver.config in the <AuthenticationTypes> element. When <AuthenticationTypes> includes <RSWindowsNTLM/> then RM & RS can use NTLM authentication. NTLM is generally a destination agnostic authentication scheme. What this means is that the client does not have mutual authentication and cannot be sure which machine it authenticated to.
When you perform NTLM authentication, you can optionally specify an intended destination in the form of an SPN. This helps mitigate authentication reflection attacks. Not all clients (e.g. browsers) or APIs packages (e.g. .Net Framework) do this by default. The specific change we observed is that the .Net Framework 3.5 SP1 now defaults to specifying the Host Name used in the request URL in an SPN in the NTLM authentication package. So if you make a URL request to http://<foo>/reportserver then the SPN “HTTP/<foo>” is added to the authentication information. This is good news from a security perspective, but has an unfortunate consequence.
You might think that since the user accessing Report Manager accessed http://<foo>/reports and then the report manager accessed http://<foo>/reportserver, everything should just work. However, Windows has a special case when you do NTLM authentication on the local computer (i.e. loop back connection) that affords protection against authentication reflection attacks. Part of the NTLM authentication process centers on a challenge issued by the destination computer and sent back to the client computer. Windows keeps track of the challenges it issues. If Windows receives a challenge it itself generated Windows will fail the authentication unless the connection is a loop back connection.
To determine whether the connection is a loop back connection, Windows reads the destination contained in the authentication information supplied by the client. If the destination is not specified or maps to the local machine then Windows allows the authentication to proceed. In our failing case, <foo> is neither the machine name nor the loop back IP address nor the machine’s IP address, so Windows fails the authentication requests. This causes the requests between Report Manager and Report Server to fail with 401 (Unauthorized).
To address the issue, we need to tell Windows that <foo> is actually an alternate name for the local computer. The correct way to do that is to use the BackConnectionHostNames registry entry as described in Method 2: Specify host names in KB 896861. Solution Method 1: Disable the loopback check will result in a less secure system, as it disables the protection against reflection attacks. It is better to constrain the set of alternate names to only those you expect the machine to actually use.
http://www.k2underground.com/blogs/pitchblack/archive/2008/05/08/reporting-services-woes.aspx
After running successfully through the Reporting Services configuration we now need to edit the 2 following configuration files;
“RSWebApplication.config” located in the ReportManager directory, for example;
“ C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting
Services\ReportManager\RSWebApplication.config”
“Reportserver.config” located in the ReportServer directory, for example;
“C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\Reportserver.config”
In “RSWebApplication.config” locate the 2 lines;
<ReportServerUrl> </ReportServerUrl>
<ReportServerVirtualDirectory>ReportServer</ReportServerVirtualDirectory>
Comment out the “<ReportServerVirtualDirectory>” node and place your host header value with the virtual directory for your report server web service path in the “<ReportServerUrl>”, for example;
<ReportServerUrl>http://MySSRS.k2.com/ReportServer</ReportServerUrl>
<!–<ReportServerVirtualDirectory>ReportServer</ReportServerVirtualDirectory>–>
In “Reportserver.config” locate the line;
<UrlRoot>http://blackpearl/reportserver</UrlRoot>
Replace the value with your host header and report server web service path, for example;
<UrlRoot>http://MySSRS.k2.com/reportserver</UrlRoot>
Something to watch out for, if you are running multiple instances of Reporting Services on one server make sure that in your “Reportserver.config” file the instance node points to the right instance path.
<InstanceId>MSSQL.9</InstanceId>
SSRS Installation March 16, 2010
http://support.microsoft.com/kb/938245
Install SSRS 2005 in Windows 2008.
http://www.mathgv.com/sql2005docs/SQL2005ReportingServicesInstall.htm
Detailed workarounds to install in a non-default web site.
http://msdn.microsoft.com/en-us/library/ms155921.aspx
Microsoft instructions to install in a non-default web site.
Report Server Kerberos Authentication March 2, 2010
http://technet.microsoft.com/en-us/library/ee355094.aspx
- cscript adsutil.vbs getw3svc/<identifier>/root/reportserver/NTAuthenticationProviders
- cscript adsutil.vbs getw3svc/<identifier>/root/reports/NTAuthenticationProviders
- cscript adsutil.vbs getw3svc/<identifier>/root/reportserver/NTAuthenticationProviders “Negotiate,NTLM”
- cscript adsutil.vbs getw3svc/<identifier>/root/reports/NTAuthenticationProviders “Negotiate,NTLM”
If you are using Reporting Services 2005, you must create an SPN for the account that is used as the identity of the AxReportServer application pool. In the recommended configuration, the AxReportServer application pool runs as the .NET Business Connector proxy account.
- In Active Directory Users and Computers, right-click the user account and select Properties.
- On the Delegation tab, select the Account is trusted for delegation check box.
- Click OK.
RSReportServer.config
<AuthenticationTypes>
<RSWindowsNegotiate />
</AuthenticationTypes>
<EnableAuthPersistence>true</EnableAuthPersistence>
and the above nodes are at the top of the XML tree.
http://msdn.microsoft.com/en-us/library/bb326419(SQL.90).aspx
How to run the SSRS web service as a domain account – ready for Kerberos.
http://msdn.microsoft.com/en-us/library/cc281253.aspx
How to configure Windows Authentication for SSRS using configuration files.
