SQL database permissions required to use impersonation from SSRS connection

Question

I am currently encountering the following error when I try to specify a datasource from SSRS to SQL Server db (2008) using "credentials that are stored securely in the server" - this is specified as a windows account "s2\killian" with the option to "impersonate the authenticated user after a connection has been made" checked.

Msg 15157, Level 16, State 1, Line 1 Setuser failed because of one of the following reasons: the database principal 's2\killian' does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission.

Obviously this method of impersonation uses the SetUser() function behind the scenes and the MSDN documentation states that dbowner permissions are required in order to use this function. However I am getting the above error when using dbowner. I am not in a position to use sysadmin because of security policy.

Does anyone know how to get this mechanism of authentication and impersonation working without assigning sysadmin priveleges on the SQL Server database to the windows account used by the SSRS data source.

Is there a way of getting SetUser() to work without sysadmin priveleges?

Thanks Killian

Answer 1

I have been able to confirm as well that you must have Sysadmin rights to run the SETUSER function which appears to be the mechanism leveraged by SQL reporting services when "Impersonate the authenticated user after connecting" option is chosen.

MSDN's documentation for SETUSER mentions this but makes it sound like it should also be possible to run this function as just a db_owner but I have not gotten reporting services to work with an account that only has db_owner and I have not even been able to run setuser manually under an account that only has db_owner. The only way I have gotten it to work is with sysadmin privileges.

Answer 2

The clue is "the database principal 's2\killian' does not exist"

Any connection uses context change on the database side (eg SETUSER, EXECUTE AS etc) requires that the impersonated user exists in sys.server_principals and/or sys.database_principals.

Assuming you use an AD group, not individual logins, then this will cause the same error as above. Outside of any permissions. If you are db_owner, then you can impersonate at the database level. If you can't, then it's because of the previous paragraph.

I've always thought that this is an idiotic option for SSRS to have. MSDN says it uses SETUSER which is also deprecated. Ideally, you'd impersonate before connection which is possible for asp.net apps but don't know for SSRS (which is an asp.net app of course)

Sorry for lack of "do this and it works" answer...