404: Page not found

I just installed SQL Server 2005 on my home PC. When I opened http://localhost/reports to check out if my Reporting Service is working properly, I got the following error:

The report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError)

• Access to the path ‘C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\RSReportServer.config’ is denied.

I found this post by Teo Lachev that shed some light on the problem. I figured that the rsreportserver.config file must be accessible by the MACHINE\ASPNET user. From Lachev’s post, I figured I could right click on the file and simply add another user to the ACL (I remember doing that sometime). But when I click on the file, I dont find any security tab anywhere. I went up to the folder “C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer” and right clicked on it:

So I was fretting that I cannot edit the ACL for the file or the folder. I dont know if this is just my machine, or if this is generic. I somehow remember right clicking on a file/folder and adding another user to the ACL of that particular file/folder. But I have done that on my work machine which is part of a domain. Is it not possible to edit ACLs on a stand alone machine which is not part of a domain? Somebody please tell me that.

Meanwhile I was desperate to get my SSRS running, so I wrote this small code snippet in C# to add the ASPNET user to the ReportServer folder:

DirectoryInfo directoryInfo = new DirectoryInfo(@"C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer");
 
DirectorySecurity dirSecurity = directoryInfo.GetAccessControl();
 
try
 
{
	DirectorySecurity directorySecurity = directoryInfo.GetAccessControl();
	FileSystemAccessRule newAccessRule = new FileSystemAccessRule(new System.Security.Principal.NTAccount("MYCOMP", "ASPNET"), FileSystemRights.FullControl, AccessControlType.Allow);
 
	directorySecurity.AddAccessRule(newAccessRule);
	Directory.SetAccessControl(directoryInfo.FullName, directorySecurity);
}
catch (System.Runtime.InteropServices.SEHException sehException)
{
       Console.WriteLine(sehException.Message);
}
 
catch (Exception e)
 
{
	Console.WriteLine(e.Message);
}

After that when I viewed the ACL for this folder, I saw that ASPNET user was successfully added. But this still did not solve my problem. So I wrote a similar piece of code to allow full permissions to the ASPNET user for every file inside the ReportServer folder.

try
{
	foreach (FileInfo fileInfo in directoryInfo.GetFiles())
	{
		FileSystemAccessRule newAccessRule = new FileSystemAccessRule(new System.Security.Principal.NTAccount("MYCOMP", "ASPNET"), FileSystemRights.FullControl, AccessControlType.Allow);
		fileSecurity = fileInfo.GetAccessControl();
		fileSecurity.AddAccessRule(newAccessRule);
		File.SetAccessControl(fileInfo.FullName, fileSecurity);
	}
}
 
catch (System.Runtime.InteropServices.SEHException sehException)
{
	Console.WriteLine(sehException.Message);
}
 
catch (Exception e)
{
	Console.WriteLine(e.Message);
}

After this when I tried to access the ReportServer website, it worked!! Anyway, this was just a one time fix. If I reinstall SQL Server again, then I might have run this code again. Is there some way to edit ACLs for a particular file/folder? Maybe I have not done enough searching.

Update: Found this link for the security tab: http://www.mydigitallife.info/2006/07/19/missing-or-no-security-tab-found-in-windows-xp-professional/

Reporting

Information is supposed to be the second most important resource of any business (the most important resource being “people”). Having relevant information at the right time makes all the difference while making important business decisions. When the size of the business organization is small, maintaining business data and extracting the required information is a fairly simple task. But consider an organization the size of a huge corporate, with businesses spread across the world, working in different domains, maintaining several huge databases containing millions of records.
So, there you go; you have all the data you need, but there is this small catch: the data is spread across multiple databases, flat files, excel sheets, CSV files. The databases don’t have a uniform architecture, as they were designed to serve a very specific purpose: store some very relevant business data.
What I mean by this, is that it takes a whole lot of re-organization of the raw data so you can put it to use, and derive any sensible conclusions regarding your business statistics. Most business databases store data directly related to the business operations, and any report generation has to extract the data from these databases and convert them into a suitable format before the reports can be generated.
The reports are the end product of the entire report generation process, which gives the business enterprise all the facts and figures that enable them to take the appropriate business decisions.
One might ask, “why is that such a big deal? Isnt that why the databases are there? Just run an SQL query and you get all the data that you want”
Point well taken, and for a simple enterprise with small busines operations, this might work fine. But we are talking about a different situation here. What works for a small organization, in this case does not scale well when the case is extended to a huge business enterprise like the one described above. It may not be fasible to run a distributed query across multiple databases, data files, excel sheets, and CSV files. The kind of processing power and time required to complete such an operation would be simply not feasible. Moreover, any small change in any of the data sources will bring the entire process crashing down.
So, the point is that all the data needs to be consolidated into a central repository, into a suitable format, from where it can be queried, in order to improve the processing and time efficiency. Aha, we are talking about a data warehouse.
Another important consideration is that delivering reports is more than just processing data through queries and making it available. It is providing information when you need it, on any platform, in any format. A common solution is generating reports in HTML format and viewing it on an internet browser. This is fine if you have a machine that can connect to a server. But you may need reports when you are on a flight, when you are with the customer, when you are in your car. This includes platforms like your pager, your PDA, you fax machine, your laptop.
What if you need those reports in a suitable format like PDF, CSV, TIFF or XML for further analysis. You may even need the reports in some custom extension/format. It is not enough. Even now, a single format and a single delivery channel may not be enough. How would you know which ones you will need at a particular point of time?

Enter Reporting Services

To take care of all these issues, we have Reporting Services. Reporting Service provides you a layer of abstraction and hides all these details from you. With a reporting server, all the reports may be rendered in any of the above specified formats and may be delivered on any channel.
Reporting services provide a clear separation between the report design and the rendering of the reports in a desired format and the way in which it will be consumed.
Report Designing and Generation
Thus the entire process of report generation and delivery consists of two separate processes: Report designing, and report generation & delivery. Both these processes are independent of each other. Hence, a report designer need not worry about the format report will be rendered in, or the channel/platform where it will be delivered.
Similarly, the report generation process does not need to know how the reports are designed. All it needs to do is pass the relevant parameters that are required as inputs for the report generation process. This report generation process renders the report in the desired format and finally delivers it to the end user.

SQL Server Reporting Services

SQL Server Reporting Services, also known as SSRS comes as a part of the SQL Server 2005 suite free of cost. The SSRS includes all the development and management modules needed to create/define reports, publish them on to a reporting server, and render them in HTML, PDF, EXCEL and CSV formats. Whats more, you can even develop your own custom format and register it with the SSRS.

As you can see from the diagram, the SSRS installs a dedicated web service on your machine. This web service has an interface which can be accessed from http://reportServerName/Reports
The SSRS provides a full-featured set of APIs that you can use to integrate Reporting Services with custom business applications. This includes the full featured web service that provides access to the complete functionality of the reporting server. The SSRS can use any data source like an SQL Server database, or an OLE DB data source as the primary database from which the reports will be generated.
:To understand all this better, lets just consider the entire reporting life cycle:
Report Authoring: Developers can define reports to be published on to the Reporting Server using a report design tool. One popular report design tool is proided by the Business Intelligence Development Studio (BIDS) which is installed along with the full suite of SQL Server 2005. These report design tools use the Report Definition Language (RDL) which is an XML based industry standard. The term report in this case might sound a little ambiguous, as this is not really a business report; but simply an XML file which specifies the connection string to the datasource to be used, the query to be executed, and other metadata. There is a corresponding Report Data Source (RDS) file which has the connection string to the real data source. The RDS and RDL files are then deployed on the reporting server. To deploy these files, you can use the BIDS tool, or do it programmatically using the web methods provided by the report server web service.
Report Generation and Delivery: Once the RDL and RDS files are deployed, the reports can be generated on request or as a response to a particular event. Reports can be viewed using the web interface provided for the report server i.e. http://reportServerName/Reports . Reports can also be generated and rendered in the desired format using the web methods provided by the report server web service.
Report Management: Administrators can use the Management Studio to organize reports and data sources, schedule report execution and delivery, and track reporting history. Reports can be executed on demand or on a specified schedule.
In order to get reporting services up and running on your machine, do a default installation of SQL Services 2005 and let the installation configure the reporting service for you. Once the installation is completed, point your browser to: http://localhost/Reports, and you will be greeted by something like this:

Click on the project1 folder and you will see the reports deployed. Clicking on a report runs it and the report generated can been seen on the page. You can then export it in any of the supported formats.
An introduction to SSRS programming can be found at:
http://msdn2.microsoft.com/en-us/library/ms155076.aspx