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/

2 comments:

Ashok Raman said...

Thanks for the insightful articles - they have helped to better my understanding...
However I have one problem that maybe you can throw some light. I am installing report services in a domain controller environment. I get page not found when I do server.domain/Reports I get a login window, that I am unable to get past... after which it says you are not authorized...
any ideas?
Thanks, again

KV Krishnamurthy said...

Ashok:
I dont know if you have trackbacks enabled and whether you can read this, but nevertheless:
It appears to me that you have windows integrated authentication disabled on your reports website. Fire your inetmgr and ensure that windows integrated auth is enabled. Are you able to access the page locally?