Summary:
There are two basic deployment scenarios:
- The SSRS ReportServer database resides on the same instance of SQL as the Spitfire database.
- The SSRS ReportServer database resides on a different instance of SQL than does Spitfire, regardless of where the SSRS web service and web application are located.
Please note that under normal deployment scenarios, all of these requirements are automatically assured by ICTool. This article is meant for when tightened security has prevented ICTool from completing this process.
When the Spitfire database and SSRS are on the same database instance, then
- The ReportServer database should have a role [SpitfireMain]
- [SpitfireMain] should be granted read access to the SSRS Catalog
- The SQL ID [SPITFIRE] must be a user of the report server database and must be a member of [SpitfireMain]
This script would likely complete the requirements above:
USE [ReportServer]
GO
CREATE ROLE [SpitfireMain] AUTHORIZATION [dbo]
GO
GRANT SELECT ON [Catalog] TO SpitfireMain
GO
CREATE USER [Spitfire] FOR LOGIN [Spitfire] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N‘SpitfireMain‘, N‘Spitfire‘
GO
When SSRS data is located on a remote server, then you need to define a linked server on the SPITFIRE SQL instance for the SSRS SQL instance. In SQL Management Studio Object Explorer for the Spitfire SQL instance, look for
- Server Objects
- Linked Servers
Review the list of previously defined Linked Servers (if any). Right click on Linked Servers and choose New Linked Server or right click on the SSRS server name and choose Properties. In either case, the linked server type will be SQL Server.
Select the Security Page in the Linked Server Properties dialog. You need to make decisions in this dialog that are appropriate for your site. Some examples:
- In the section ‘For a login not defined in the list above‘, you could simply choose the ‘Be made using this security context‘ and provide static credentials, as long as those credentials can connect to the SSRS ReportServer database and SELECT from the dbo.Catalog table.
- If your SSRS instance of SQL also hosts a Spitfire site, *and* each ICTool configuration file for all such related Spitfire sites has been provided with the same password for the [Spitfire] login, you can add a mapping entry for the local [Spitfire] SQL login and check the Impersonate option.
- Most secure and most likely, you will want to map the [Spitfire] login to some custom SQL login on the SSRS instance of SQL.
- Create a custom login on the SSRS server (for example, [SpitfireSSRSLinkReader]), providing a strong random password
- Substitute SpitfireSSRSLinkReader for all the places the script above uses [Spitfire] or ‘Spitfire‘
- Add the mapping entry for [Spitfire] to [SpitfireSSRSLinkReader], and specify the password.
Test your work by running EXEC dbo.p_SynchRSFolder. If the command runs successfully, you should test again with a Management Studio window connected using the [Spitfire] login.
Additional Comments:
These instructions focus on the ability for the Spitfire Web Application to read the SSRS catalog at the SQL data layer. There are other integration points:a) ICTool uses windows authenticated access directly to the SSRS web service to upload reports. You configure this access using SSRS Report Manager and can test the access by running report manager from the same windows session where you run ICTool.b) The Spitfire Web Application uses domain credentials created using windows domain tools and configured in SSRS Report Manager with access to the SSRS Report folders where Spitfire reports have been uploaded. You use ICTool to configure Spitfire to use these credentials. These credentials are used only to run and render report output. These credentials do not require any update access to the report catalog.
KBA-01417; Last updated: October 10, 2016 at 12:06 pm