Found a good MSDN article about configuring service accounts for SQL 2005 Reporting Service, see below. One thing want to highlight is that for production, it is recommended to use domain accounts. Although the passord expiration needs to be managed, it is best practice for many organizations.
Configuring
Service Accounts
The
report server Windows service account is first specified during Setup when you
specify options in the Service Account
page. Although specifying the service account during Setup is required, you can
choose a different account after Setup is finished. For more information about
the permissions that each service requires, see Setting Up
Windows Service Accounts.
The
report server Web service account can be specified during Setup if you are
installing the default configuration. In the default configuration, the Web
service account always runs as the default ASP.NET account on Windows XP or
Windows 2000 server, or as Network Service on Windows Server 2003. If ASP.NET
is configured to use a different account, the report server Web service account
must be specified manually.
To
set up the service accounts manually, use the Reporting Services Configuration
tool. The tool includes two pages for specifying the accounts: the Web Service
Identity page and the Windows Service Identity page. You can specify the
service accounts in any order. For more information, see Changing Service
Accounts and Passwords.
When
configuring the accounts, you can use any of the following account types:
|
Account
|
Explanation
|
|
Network
Service
|
This
is a built-in account that is well-suited for running the report server Web
service on Microsoft Windows Server 2003 and Windows XP Service Pack 2 (Network
Service is not available on other Windows operating systems).
Choose
Network Service if you want to avoid any down-time that might occur as
a result of password expiration policies. Avoid Network Service if you
do not want to run the report server under accounts that might also be used
by other applications.
|
|
Domain
accounts
|
You
can choose a Windows domain account if the account has minimal permissions.
Windows domain accounts are the default for SQL Server services.
Choose
domain accounts if you want to precisely configure the permissions on the
service. Avoid domain accounts if do not want to change the password or
account due to expiration security policies used in your organization.
|
|
Local
Service
|
This
is a built-in account that is similar to an authenticated user account.
Services that run as the Local Service account access network
resources as a null session with no credentials.
This
account is not appropriate for most intranet deployment scenarios.
If
domain accounts are used to create subscriptions, you cannot use Local
Service for the Report Server Windows service account.
If
you use Local Service, the report server database must be on the same
computer if the service account is used to connect to the report server
database. If you want to use remote SQL Server instance, you must use SQL
Server authentication and a database user login for the report server
database connection.
Local
Service is the default service account for a report server you install
through Microsoft SQL Server 2005 Express Edition with Advanced Services.
That edition has built-in restrictions that prevent access to remote servers.
For more information, see Reporting
Services in SQL Server 2005 Express Edition with Advanced Services.
|
|
Local
System
|
Avoid
this account for report server installations. Local System is a highly
privileged account that is not necessary for running a report server. Choose
a domain account, Network Service, or Local Service instead.
|
SQL Server 2005 Service Pack 2 is coming out soon. The very important piece of SP2 is support of SSRS and SharePoint 2007 Integration. There are many bug fixes and enhancements in the service pack but here I want to highlight the integration with SharePoint 2007.
The key functionalities include:
SSRS & SharePoint Integration
The existing integration between Reporting Service and SharePoint are very limited. It is primarily done through two web parts – Report Explorer for listing the reports and Report Viewer for execution of the report. Security, Management of the UI and others are not integrated. In this coming Service Pack 2, SSRS not only starts to support SharePoint 2007 which is coming out before end of this year as part of Office 2007 product release, it also enhances the degree of the integrations.
- Seamless integration with SharePoint Document Library in Office SharePoint Server 2007
- Report server namespace and security is hosted in WSS content database
- SharePoint database stores Report Server reports, data sources, models, resources etc.
- Report Server database used for additional metadata (schedules, caching, subscriptions)
- Enables collaboration, workflow, versioning
- Centralized user interface for Report Management through Office SharePoint Server 2007
- Upload, manage, render and deliver Reports all through Office SharePoint Server 2007 UI
- Administration using Office SharePoint Server 2007
- Rich reporting experience to "light up" Office SharePoint Server 2007
- Filter web parts enhancing dashboards in Office SharePoint Server 2007
In SP2, Reporting Service also integrates with Hyperion, and Report Builder supports for Oracle Data Sources.
The list of reports requested in Requirement or Functional Specification by customers is one thing, the actual list of reports used for day-to-day business after Production is another thing. While we know customers have the challenges of changing business needs, how do we understand and measure what are the reports that are actually being used by customers after Production? To some degrees, it is important to understand this fact so that future iteration of developments and/or maintenance can be placed in the right order of priorities.
I found an easy way to do that - if you are using SQL Server 2005 Reporting Service. The idea is to upload the IIS log for the Reporting Service into an IT or operation oriented database (anywhere in the internal network) and then organize the data for a few handy and useful reports through Reporting Service. To do that, there are two tools you should download:
Log Parser 2.2: Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows operating system such as the Event Log, the Registry, the file system, and Active Directory. You can download the Log Parser 2.2 here.
SQL 2005 Reporting Pack for Internet Information Service: a set of 12 predefined reports and a sample database to easily visualize and author managed reports such as site summary statistics, time period, page views and user statistics, based on information extracted from IIS log files.
Download it here.
Install them both on your Reporting Server. Create a database for your IT reporting, say name it ITRPT. Use the following scripts to populate the log data into the database:
C:\Program Files\Log Parser 2.2>logparser -i:IISW3C -o:SQL "SELECT * INTO ITRPT.IISLog FROM \\myreportingserver\REPORT_LOGS\*.*" -oConnString:"Driver={SQL Server};Server=Mydatabase;db=ITRPT" -database:ITRPT -createTable:ON |
The above scripts will upload the IIS Log file (located in \\myreportingserver\REPORT_LOGS\*.* into a backend database ITRPT in a server Mydatabase. A new table IISLog will be created automatically.
You can now have access to a set of pre-defined reports to manage your Reporting Service site. The reports available including when users visit your Reports, where the users coming from, etc. In addition, by leveraging the IIS Log database table, you can perform some transformation to extract useful information:
SELECT replace(replace(substring(csUriQuery, 10, 300), '%2f', '/'), '+', ' ') as Report, count(*) as total
FROM iislog
WHERE csUriQuery like 'ItemPath=%' and csUriQuery not like '%ViewMode=%' and csUriQuery not like '%SelectedSubTabId=%' and csUriQuery not like '%SelectedTabId=%'
GROUP BY csUriQuery
ORDER BY Total DESC |
Basically what the above scripts does, is to check the Report folders in your home pages and then perform calculations on the total visits on the reports. Once you got there, by using Report Designer, you can create some very useful reports. For example, Top 20 Most Active Reports within 30 Days, Most Active Reports by Project, etc.
For a long time, Reporting to most of organizations is known
as “second tier” priority compare to “first tier” online transaction systems.
This is still somewhat true in today’s business environment. However, Enterprise reporting is becoming increasingly critical in terms
of the roles it plays in enterprise decision marking. It is not unusual that
companies spend more time and money investing in Enterprise Reporting than online transaction systems. In some organizations, Reporting is equally or more important than online transaction systems.
Normally, A reporting system is built on top of a throughout understanding organzation's business needs, data model and daily information flows. However, to play the key roles in information decision making and
maintain high level of organizational function for long period of time, there
are new challenges have been emerging for Enterprise Reporting solution developers to consider, which I call it ARRA challenges,
Agile Construction – Ability to deliver
enterprise reporting solution incrementally through quick cycles and iterative
approaches to avoid investing one big bet upfront. In fact, most of those big
bet failed miserably because of the changing nature of enterprise business information and data. Today, Reporting solutions in enterprise need more adaptability compare
to online transaction system. The traditional ways of developing Reporting
solutions typically have a few well-known problems - big upfront cost, long
development time and stable business information flow. Agile approach will allow enterprises to enter into a healthy
build-and-use, rebuild-and-reuse cycles. Partial but early production allows enterprise
information workers’ feedback and adjustment be built in to the solutions.
Rapid Development and Deployment – Today’s
organization can not wait for the traditional waterfall cycles of writing reporting
requirement, having developers to design reports, testers to QA and operation
to deploy them to production. Business just does not have time time or the “spec” that IT
teams are looking. Instead, users are demanding rapid development
and deployment for reports. Many ad-hoc reports are just “ideas” or “try-and-see”
type of information analysis for business users. They are keen to leverage a
rapid development and deployment reporting infrastructure to probe new findings
of enterprise data in order to provide more adaptive data analysis and consumptions cycles.
Real-Time Reporting – Another tradition
in Reporting solution is that data and information provided norally are not near-real-time.
24 Hours or even longer delay in data refresh cycles are very usual solution due to the
problems of conflicting resources utilization between instantaneous online
transaction system needs and complex aggregation reporting queries. However,
today’s enterprise is driven by fast cycle of information decisions. Reporting
architect and designers need to invest in solutions that bring enterprises information and data more
quickly and timely in front of decision makers.
Availability – when mentioning high availability
and 24*7, people will think immediately for online transactional system. However,
in today’s increasing complex enterprise business environment, data needs to be
available any time around the clock and globally. This is becoming more and more
important to create and maintain enterprise wide competitiveness. You may slowly notice that your enterprise information workers are demanding the high availability of Enterprise Reporting system just like in the past of online transaction system.
It is still not easy to build an Enterprise
Reporting solution to meet the above needs. However, I believe the trend is
inevitable. Is your system ready for Enterprise Reporting?
If you have developed a solution using Reporting Service, the Report Builder is obvious choice for end user designing ad-hoc reports. In some situations, business users want to design a report, execute it, export the results back to excel, and then using Excel to format it. If these happen a lot, you will find using another product OfficeWriter is better for your users’ needs.
OfficeWriter is a product from SoftArtisans (www.softartisans.com). It leverages Reporting Service web services and offices integration with Microsoft Excel and Word. Users can use Excel to design a SSRS report, together with the powerful formatting and calculation controls available in Excel. See a case study here:
http://members.microsoft.com/customerevidence/search/EvidenceDetails.aspx?EvidenceID=14783&LanguageID=1
If you want to know more about the product, check this out
MSDN Webcast: Design Reports from SQL Server Reporting Services Using Microsoft Office Programs (Level 200)
In one of my customer scenarios, OfficeWriter is used with Excel to design a report that allows information can be formatted exactly as what the end-user needed. Also, the information was distributed to multiple worksheet in the excel workbook. If a user subscribes to this report, it will be generated and emailed to the user’s mailbox very day in the morning.
I really like the fact that it simplifies many steps in between and offers the final formatted reports to end users. Thing about it – all these are done by end users themselves with Excel (OfficeWriter is a front end user tool, with subscription permission in your reporting solution) and no IT efforts involved!
For Enterprise Reporting, it means empowering the business end users! :-).
I have been trying to run SQL Server 2005 Reporting Service on Windows Longhorn but haven’t got enough luck previously. Today I finally got it right and here are the details I’d like to share with you.
1. The Proper Environment
You need to have SQL 2005 Service Pack 2 CTP1 and Windows Longhorn Server RC1 (Build 5600) to get them work together. Please find the following path for these two products:
· Windows Longhorn RC1 (Build 5600): \\winbuilds\release\vista_rc1\5600.16384.060829-2230\x86fre\media\Client_en-us.
If you don’t have access to these folders, too bad L. But soon I’m sure it will be available to http://productsweb and external download sites.
I don’t know if builds prior to this works together (for installing SQL Reporting Service on Vista or Longhorn) as I have been failing so many times. Do let me know if you have successful stories.
2. Installing IIS 7
The very first thing you need to do is to ensure IIS7 is installed and configured properly on the Longhorn server. By default, the IIS7 is not installed and configured.
|
Installed IIS 7 in Longhorn
Start -> Server Manager. Select Manage Roles in the left panel manu then go with Add Roles. Select Web Server (IIS) and install.
|
I have the IIS run a complete install for the roles. Alternatively, you can try Ketaanh Shah’s comment for the minimum installation of IIS:
Common HTTP Features
Static Content
Default Document
HTTP Redirection
Directory Browsing
Application Development
ASP.Net
ISAPI Extension
ISAPI Filters
Security
Windows Authentication
Management Tools
IIS Metabase
IIS 6 WMI
Note: Please reboot the Longhorn server after you completed the IIS 7 installation. (I know! The Longhorn supposes to eliminate the reboots but…..maybe in next RCsJ).
3. Installing SQL Server 2005 with Reporting Service
If during the installation, Reporting Service checkbox is not enabled for you to install, check your IIS settings and installation.
4. Installing Service Pack 2 CTP1
After the installation of SQL, Reporting Service and SP2, you should use Management Studio to connect to the database as well as the Reporting Service. If you cannot connect due to error “In sufficient permission”, do the following:
Go to Start à All Programs à Microsoft SQL Server 2005 à SQL Server Management Studio. Right click your mouse and select “Run as Administrator”.
You should be able connect to the Database and Reporting Service. Add yourself and the service account to have sufficient rights for Database as well as the Reporting Service home folders.
5. Configure Reporting Service
Configure your Reporting Service using the Reporting Service Configuration Tool under here:
Go to Start à All Programs à Microsoft SQL Server 2005 à Configuration Tools à Reporting Services Configuration
You might find some errors with trying to configure the Reporting Service. Here is what I have encountered:
Report Server Database is an Invalid Version – You will get an error on Event logs for this. And the message is
|
The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'C.0.8.40'. The expected version is 'C.0.8.43'. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase)
|
What you should do is to use the Reporting Configuration Tool, go to the Database Setup listed on the left navigation pane and then click the Upgrade button.
The reason for this error that that in SQL Server Service Pack, the report database schema is changed.
6. Browsing the Report Manager Web Folder
If you are successful, an empty folder should appear under the home page with “Home” when you try to access the Report Manager. Deploy your data sources and reports to start using Report Service!
If you see nothing in the Report Server after you deploy the reports, “run as administrator” with your IE!.
Most of organizations might be using different type of data
reporting for these business needs but there are a lot of similarities too. Here
are the various types of Enterprise Reporting I have collected so far:
- List Reporting – The most common
usage of Enterprise Reporting is the formatted displays or presentations
of organizational data lists through list, text, graphics or other
rendering formats for periodic business operation. Various levels of
itemized rows and aggregated summaries are typically used in List
Reporting. Data rows and summaries might be assembled from one or more
than one functional discipline areas within the enterprises.
- Interactive Analysis
– Enterprise
users needs to perform analysis upon large set of data to understand or
find presentation of the data. These analysis typically are interactive
and allow users to directly select dimensions (location, department, time,
etc) to compare measurements (sales growth, cost distributions, amount
etc). The interactive analysis requires data readily available when
different dimentions, measurement are chosen so typically data are
pre-calculated or aggregated using specific data model like OLAP.
- Ad-hoc Querying - Ability to allow advanced business users for ad-hoc data needs and play "what-if" scenarios to determine what are the best use of enterprise data. Most of time it involves Excel or other front-end easy tools for those users to analysis or querying data.
- Metric Management - In many organizations, business performance is managed and measured through
outcome-oriented metrics. These metrics are agreed measurement to track
and compare the business performance over a period of time. Within the
organization, these are mostly called Key Performance Indicators (KPIs). For
external organizations, they are Service Level Agreement (SLAs).
- Dashboard - Another way for enterprise to consume their reporting data is publishing
them into customized dashboard views, mostly hosted within enterprises’
internet portal. These dashboards might use graphic to mimic color-coded
auto dashboard indicator for easy but grand overview of enterprise’s key
performance.
- Balance Scorecards - A method attempts to present an
integrated view of success in an organization. In addition to financial
performance, they also include customer, business process and learning and
growth perspectives.
- Data Mining - When enterprise holds large
amount of data, many of them started to analysis using Data Mining
technique neural networks and machine learning to study and find potential
common patterns in the data. Most of the data mining takes time and
dimensions into account to try to predict or forecast the learning.
I have collected many free training resources here for people to learn about SQL 2005 Reporting. This list includes various levels of materials including some introduction and advanced topics. Please let me know if links don't work or you have new links for me to add on. I'll update them in future posts.
Information Web Portal
General SQL 2005 Reporting Services
Advanced Reporting Services
Ad-Hoc Reporting - Report Model and Report Builder