How To Configure SQL Server Reporting Services In Order To Deploy Reporting Services Reports In GP

The post following this will cover deploying SSRS reports within Microsoft Dynamics GP but I thought I’d cover configuring SSRS as a spearate post rather than assuming everyone has it already configured. A number of the clients I deal with have had GP for a while and never installed the SSRS reports but are now doing so.

When you install SQL Server Reporting Services at the same time as SQL Server you have the option to automatically configure SSRS with the default settings. In the case of someone who has SQL already installed but didn’t install SSRS at the same time, this is not an option.

This How To starts after SSRS has been added to the installation on the client machine at the point where you would start to configure it; if you’d like a walk through adding SSRS to an existing installation of SQL Server, let me know and I’ll sort one out.

To do the initial configuration of SSRS, or to change the configuration of an existing installation, you need to start the Reporting Services Configuration Manager (Start menu » Programs » Microsoft SQL Server 2008 R2 ?? Configuration Tools » Reporting Services Configuration Manager);

The Configuration Manager can largely be treated as a wizard as you move down the tabs on the left side of the window. The first step is to set the Service Account.I am installing this on a demo laptop so I’m just going for the easy option of picking the Local System account but for a production server I would strongly recommend using a Domain User account which is only used for SSRS. Once you’ve made you selection and entered logon details click the Apply button;

Move to the Web Service URL and either accept the default or change the Virtual Directory and Web Service Site identification details (make sure you make a note of the Report Server Web Service URL as you will need this when configuring SSRS within Microsoft Dynamics GP);

After you’ve clicked Apply, the Results section will confirm the Virtual Directory has been created and the URL reserved. If any issues are highlighted you’ll need to resolve them before proceeding;

Click the Database Tab to select, or create a new, database for use with SSRS;

Click the Change Database to open the Report Server Database Configuration Wizard. I’m leaving the default option of Create a new report server database and click Next;

Enter the Server Name and connection details for the SQL server which will hold the SSRS database and click Next;

Enter the Database and Temp Database Names, pick your language (unfortunately we’re restricted to only English (United States) without an option for English and pick whether SSRS should be in Native or SharePoint Integration Mode. For simplicity I have chosen Native Mode;

Enter the log on credentials. Again I have chosen Service Credentials but would recommend a Domain User account for a production environment;

Click Next and you’ll be presented with a summary of your selections. Review them and click Previous to make any changes or click Next to proceed;

The Wizard will verify the configuration and present confirmation where you need to click Next;

The Database tab of the main Reporting Services Configuration Manager window will be updated with the database details. Review them and click Apply;

Move to the Report Manager URL tab and enter the Virtual Directory you want to use (I left this at the default option but in hindsight I’d rather have changed this to ReportManager_SQL2008R2);

After clicking Apply, as with the Web Service URL tab, you’ll be presented with confirmation that the virtual directory has been created and the URL reserved;

Click E-mail Settings and configure the SMTP settings if you want to use Report Server e-mail and click Apply;

On the Execution Account tab enter the details for accessing remote servers that store external images (make sure you use a domain user account with minimal read-only permissions) and click Apply;

Click onto the Encryption Keys tab. Whenever I see a Backup button I feel the need to do so. In the case of a demo system this isn’t especially important, but on a production environment I’d recommend you back up the Encryption Key in case of a need to recover or move the SSRS database;

Enter a password which will be used to lock and unlock the Encryption Keys file (make sure you pick a password that you will remember);

Once done the Backup of the keys will be confirmed;

The final step is to scale out the Deployment should you wish;

When done click close and SSRS will be available for use within Microsoft Dynamics GP which I’ll cover in the next post.