Sunday, August 19, 2007

SQL Server Reporting Service woes

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/

No comments: