MS SQL Server: Reporting Services

Launching the configuration tool is quite simple. The tool is installed as a component of SQL Server Reporting Services and is located under Microsoft SQL Server 2005\Configuration Tools\Reporting Services Configuration in the Start Menu. When you initially launch the tool you'll be prompted to connect to an instance of SQL Server Reporting Services. Enter the values that are reflective of the environment you wish to configure and continue.


The first form that is presented in the SQL Server Reporting Services Configuration Tool provides you with the basic information of the instance, and its status. Along the left margin of the tool you'll see an overview of each category that you're able to configure (each category will have a separate form in the configuration tool). The status for each category is displayed and the legend for these statuses are provided at the bottom of the tool.


As you proceed through each category, you'll see the status pane on the left side of the
Reporting Services Configuration tool change accordingly. Let's now move onto the right
pane of the tool and proceed through each category in depth.
Server Status
This is the first screen you'll encounter after logging into the tool. This screen only allows
you to view the instance you are connected to, and gives you control over the running
status of the service.

Report Server Virtual Directory and Report Manager Virtual Directory
SQL Server Reporting Services' Report Server and Report Manager are ASP.NET
applications that are accessed via a URL composed in the following manner:
http://server/website/virtual_directory (https://server/website/virtual_directory if the
server in question is configured for Secure Sockets Layer (SSL).) The server component
can be a server name, fully-qualified domain name, or even localhost. It all depends upon
how you're planning on accessing Reporting Services.
If Reporting Services will be accessed from
here...
Server Name component in URL will
be:
Within your local network or Intranet The Server (DNS) name
Remotely via the Internet A fully-qualified domain name
Locally on the same server localhost
The virtual directory is the only component of the URL that you're able to configure
within Reporting Services. The default values are presented below. Just as is the case
with any defaults in SQL Server, if you're concerned about security I strongly suggest
you avoid using the default values.
You can also configure Reporting Services for SSL connections. To do so, simply check
the Require Secure Socket Layer (SSL) connections checkbox and enter the appropriate
values into the drop-down supplied.

In addition to the virtual directory name, you are also able to assign the virtual directory
to an existing website or a default website. You are not able to create the website from
with this tool. If you do not wish to assign the virtual directory name to the default
website you will need to configure a new website first via Internet Information Services
(IIS) before proceeding. You can use the same website for both Report Manager and
Report Server.

By default, anonymous access is disabled; the Reporting Services virtual directories are
configured for intranet access only. In order to configure Reporting Services for Internet
connections, you will need to modify the report server configuration files - specifically
the RSWebApplication configuration file. Configuration file modifications are outside
the scope of this article, however. For more information please review the detailed
articles available from Microsoft TechNet that touch upon that subject:
Configuring Report Server Virtual Directories


Web Service Identity
By default the Report Server Web service runs under Network Service in IIS 6.0 and the
ASP.NET account in IIS 5.0. However, you can use the Reporting Services Configuration
Tool to modify the Web service to fit your environment's requirements. You are able to
set up the application pools for both Report Server and Report Manager in this screen as
well.

Database Setup
Installing SQL Server Reporting Services does not automatically create the databases that
Reporting Services requires to function properly. You are able to create a new
ReportServer and ReportServerTemp databases from within this form. Optionally, you
can connect to an existing instance of these databases. This is convenient when migrating
Reporting Services instances to new hardware or upgrading existing instances of
Reporting Services.
Below, we will take a look at each of the options for setting up or connecting to an
existing instance of the Reporting Services databases available from this form
Connect: Connect simply allows you to establish a connection to an existing instance of
the ReportServer database. Select a server name to connect to, and credentials with the
necessary rights.

New: If you wish to create a new instance of the ReportServer and ReportServerTemp
databases select this option. It will open the following dialog box, allowing you to specify
an existing Microsoft SQL Server instance, method of connection, Report Server
database name, and language. You are also able to set the SharePointIntegrated property
at the time of creation. Supplied credentials will need the rights necessary to create a
database on the SQL instance. After configuration you may chose to connect to the
databases in SQL Server Management Studio and change ownership if necessary.
Upgrade: Clicking this button will automatically upgrade the tables in-place to the new
schema for SQL 2005. Perform this task if you are upgrading an existing instance of
Reporting Services 2000 to Reporting Services 2005. Make a point of backing up your
databases prior to executing this command. You can not roll back this action once
completed.
Change: Changing from Native to Sharepoint-Integrated mode for Reporting Services
requires the creation of a new Reporting Services database. Selecting this command will
open the New Database dialog box. The process from that point is identical to the
creation of a new Reporting Services database.
You are able to script the changes made on this screen to be run at a later date by
selecting the Script... button located at the bottom of the form.

Configuring Reporting Services in Sharepoint Integrated mode will allow you to
synchronize Reporting Services content with a Sharepoint site. Setting up Sharepoint
Integration is outside the scope of this tip.
This form allows you to backup, restore, or make changes to the symmetric key that is
used for encryption and decryption of data in this instance of Reporting Services. The key
is initially generated and activated upon creation of the ReportServer database. Under
certain circumstances, you'll need to make sure you have a backup copy of the key. Such
circumstances include:
• Migration of a Reporting Services installation to a new server
• Changing the service account associated with the Report Server Windows service
• Configuration of a new report server in order to re-use an existing report server
database previously established with another instance of Reporting Services
Restoration of an encryption key is required if you make Reporting Services service
account changes without using the Reporting Services Configuration tool or if you're
migrating Microsoft SQL Server Reporting Services to a new server. According to
Microsoft: You should restore a symmetric key only if it is the same key that is currently
used to encrypt and decrypt data in the report server database. If you restore a symmetric
key that is not valid, you will no longer be able to access sensitive data. If this should
occur you would need to delete the key and recreate it; both tasks would be accomplished
via this form. You will be prompted when deleting the symmetric key. It is important that
you are aware of the consequences before proceeding.

The process of deleting a symmetric key is not reversible. When you delete a key, you
delete all data that is encrypted with the key. This would include, but not be limited to,
such valuable data as subscriptions, report data sources, and connection strings. It is also
important to note that only the Report Server Windows service account is able to unlock
and use the key. If you make changes to this service you'll be unable to use the key.
One of the most-important services provided by Reporting Services is the ability to
schedule and deliver reports via email. As is the case with Microsoft SQL Server 2005
Database Mail, this is accomplished via the Simple Mail Transport Protocol (SMTP). In
order to properly configure this component of Reporting Services, you'll need to supply a
sender address, and the SMTP server/gateway to use. Though a textbox is supplied for
deliver method, the only method allowed in Reporting Services is SMTP. The only
requirements is that the sender email address has permissions to send email from the
SMTP server you're specifying and that the SMTP server is a local server or SMTP
server on your network. The email address you supply will be in the "From" field of all
outgoing email from this Reporting Services instance


Setting up an Execution Account allows Reporting Services to operate under certain
circumstances when credentials are not supplied. Connecting to data sources that do not
require authentication, such as XML for instance. Best practices states that this account
should be different from any of the service accounts set up previously for Reporting
Services. Any Windows account is acceptable for use, though the login should have
sufficient rights to read locations holding the data or files you'll be accessing. It will also
need to have network login permissions.
At this point, if you've completed the forms in a manner that correctly conforms to your
environment you should be ready to continue on with building and deploying reports to
this instance of SQL Server Reporting Services.