Application Role And SSRS

Hi dear reader

I made an application that uses a Sql Server 2005 Express DataBase.

In the database I made a application role.

When the user logs into my application I run this procedure:

If Not sqlConnectionCR Is Nothing Then

If Not sqlConnectionCR.State = ConnectionState.Open Then

sqlConnectionCR.Open()

SqlConnection.ClearAllPools()

ConsultasSqlCommand = New SqlCommand

ConsultasSqlCommand.CommandType = CommandType.Text

ConsultasSqlCommand.CommandText = "sp_setapprole 'appRole', 'drowssap"

ConsultasSqlCommand.Connection = sqlConnectionCR

ConsultasSqlCommand.ExecuteNonQuery()

End If

Else....

I understand that this procedure connects to my sqlserver database as my application role

Ok, so far no problems in reading and manipulating data.

The problem comes with the reports in my application. For example: I have a reportviewer with a serverreport but when I try to show the report gives an error about permissions and grant access....

I think that is because the Server Report uses the user account (domain/user) to read the database. No user (besides admin) has access permissions in the database (only admin and application role).

So, my cuestion is: How can I tell Report Server to use the application role to display reports?

Thank you for your time and help.

Giber

Your report application should also set the application role, which should have access to the database.

Thanks
Laurentiu

|||

I develop my Server Reports in SQL Server Business Intelligence (VS2005)

I can't seem to find where or how to tell BI how to connect to the DB using the application role.

To connect im using just: "Data Source=localhost\SQLEXPRESS;Initial Catalog=ConsultasSql" in the data view seccion of the report (rdl) designer.

Greetings

|||

I am not familiar with Server Reports or SQL Server Business Intelligence, but hopefully explaining how application roles work may be useful.

Application roles or approles are database-scoped principals that require no permissions (other than being able to access the database where they are defined) to be set as the execution context at run time, but require the knowledge of a password. Approles can only be set at ad-hoc level, this means that the call to sp_setapprole cannot be part of a stored procedure body or within a user defined transaction. More detailed information is available on BOL: Sp_setapprole http://msdn2.microsoft.com/en-us/library/ms188908.aspx

If the Server Reports definition allow you to define ad-hoc statements, it may be possible to call sp_setapprole as the first statement. I would recommend asking for information on how to customize these reports in the SQL Server Integration Services forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1).

An alternative could be to use the new impersonated mechanism (EXECUTE AS) or digital signatures in a stored procedure or user defined function to retrieve the data from the database without granting permissions beyond executing the SP. You can find more information on this topic in BOL, a good starting point is Context switching: http://msdn2.microsoft.com/en-us/library/ms188268.aspx

-Raul Garcia

SDE/T

SQL Server Engine

|||

Im putting this thread under SQL Reporting Services Forum.

Also Im going to check that Execute As clause.

Tnx a lot

Giber