T-SQL Tuesday #005: Smart Card Login Breaks SQL Server Reporting Services 2008

This blog entry is participating in T-SQL Tuesday #005, hosted this month by Aaron "@SQLVariant" Nelson (Blog|Twitter). You are invited to visit his blog to join the party and read more blogs participating in this month’s theme: Reporting. For my part in this month's TSQL2sday, I'm going to talk about a bug we discovered in SQL Server 2008 Reporting Services when hosted on Windows 2008 or Windows 2008 R2. We were not able to reproduce the bug it on SQL Server 2008 on Windows 2003 nor on SQL Server 2005 on Windows 2008/2008 R2. So it definitely seems to be the particular combination that results in the bug.

The Bug

In our extranet domain, smart card login is enforced by group policy. Getting an exception to that policy is a major pain as the request has to go through a fulle Security Design Review (SDR) by our ACE Security team who's job it is to enforce security policy. You can't get anything done in a production environment that requires access from the internet without full buyoff from the ACE Security team. This is a hassle, but it is also a very good thing that security policies are being enforced.

Last year, one of my colleagues, Kishore Dhonan Bhojan, was having an issue setting up reporting services for the reporting side of his application. The issue was that it was not authenticating users. I did the usual routine of verifying that everything was set up correctly. The odd part of it was that everything worked perfectly well if he manually disabled the smart card login requirement. Unfortunately, smart card login is enforced at the domain level, and it would only be a matter of time before group policy reset it back to the required value. What made it even more frustrating was the Reporting Services does not know how to handle Smart Card logins, and logging in with a smart card fails too.

All credit for finding this bug and figuring out that it was the smart card login requirement causing it goes to my colleague Kishore Dhonan Bhojan.

The error being returned by Reporting Services was:

An error has occurred during report processing. (rsProcessingAborted)
o    Cannot impersonate user for data source '<data source name removed from email>'. (rsErrorImpersonatingUser)
?    Log on failed. (rsLogonFailed)
?    For more information about this error navigate to the report server on the local server machine, or enable remote errors

And the following error could be found in the Reporting Services log:

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot impersonate user for data source 'DB'. ---> Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Log on failed. ---> System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x800F033E
processing!ReportServer_0-6!e08!05/08/2009-16:11:14:: e ERROR: An exception has occurred in data source 'DB'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot impersonate user for data source 'DB'. ---> Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Log on failed. ---> System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x800F033E
processing!ReportServer_0-6!e08!05/08/2009-16:11:14:: i INFO: DataPrefetch abort handler called for Report with ID=. Aborting data sources ...
processing!ReportServer_0-6!e08!05/08/2009-16:11:14:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing., ;
Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot impersonate user for data source 'DB'.

We determined that this was a new bug, and I filed the bug item for it on Connect while my colleague worked on submitting the group policy exception request for an SDR.

Temporary Workaround

The following command could be used to temporarily disable the smart card login requirement from a comamnd window. You could also edit the registry manually if you feel comfortable doing so. Of course, the usual caveats about backing up the registry first apply here.

reg add HKLM\software\microsoft\windows\currentversion\policies\system\ /v scforceoption /t REG_DWORD /d 0 /f

It's Fixed: in SQL Server 2008 R2

The bug on Connect was never updated to say so, but I was able to find out through internal resources that the bug has been fixed in SQL Server 2008 R2. If you are affected by this bug, you should definitely consider upgrading to SQL Server 2008 R2 instead of disabling smart card login requirement.

The Bug Report

Most of the important stuff fromt eh bug report is included above, but if you want to see the full bug report, you can go to the Connect site to see bug # 462979: https://connect.microsoft.com/SQLServer/feedback/details/462979/smart-card-login-breaking-sql-reporting-service.