So today we’re setting up new SQL Server 2008 R2 servers from existing SQL Server 2005 server. One of the parts is Reporting Services reports running using Data Driven Subscriptions. I inherited the design of this system, where I feel Reporting Services has been turned sideways to simply use the Enterprise Edition feature of Data Driven Subscriptions to simply allow users to schedule reports to email to end users.
In the existing SQL Server 2005 system, the service account being used to run Reporting Services is a Domain Administrator account (Yeah, I know!), and the “administrator user” who set up the schedules, reports, and subscriptions is in the Local Administrators group in the OS and in the SysAdmin role in SQL Server. Again, this was inherited.
So, when I set this up and installed 2008 R2 and SSRS on the new server, there was absolutely no way I was going to set the service to run under a domain admin, and we’re also enforcing no administrator accounts for developers on the production instances. Code deployments are going through TFS and any DDL changes that they don’t script out in source control will go through the DBA team. Set up SSRS to run with a dedicated AD service account with minimal rights on the OS and in SQL. Everything works via the Web UI. However, no emails were sent for the scheduled reports.
The reports will email the user reqeusting the report with the PDF embedded. The error the ReportServer database table was “rsConfigError” and the error in the trace file was a generic “Configuration Error”. After checking permissions on the data sources for the reports to make sure the configured user had permissions, and trying to set up an execution account yielded no improvement, I switched the service account to run under LocalHost\System account. That yielded no better results, actually there were some errors for AuthzInitializeContextFromSid and Access denied trying to look up the AD account. So then I decided to throw out a curve ball and set the service account to a domain admin account. I know! I did it simply for testing. So after this, the trace log showed new information… about not being able to authenticate the “administrative” user’s account… WTF, where was that coming from!!?? I then added that account in the Administrators group in Windows, and BLAM! Reports were emailed. W.T.F.?!!!?
I switched the service account back to my domain service account (no way I’m running this as a domain admin)… and back to the generic error. So then I started digging to the ReportServer database, and found the “Subscriptions” table with an OwnerID column and a GUID. Cross-referencing with the Users table, and blam, there’s the “administrative” user’s account as the owner. Damn you MS! The user that configures the subscriptions needs elevated permissions to send emails with attachments. And… there’s no way to change the owner of the subscription via the GUI. So I updated the OwnerID column to NT Authority\System, removed the “administrative” user’s account from the Administrators group in Windows. Everything works as expected. Unfortunately, I have no idea of what might not work properly going forward with this manual change. Also, I’ve got to do this work around if the “administrative” user ever creates new reports and schedules and subscribes them for end users.