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 computer account (domain/user) to read the database. No user (besides admin and application role) has access permissions in the database.

So, my cuestion is: How can I tell Report Server to connect to the DB using the application role in the DB?

Thank you for your time and help.

Giber

I don't see how you could do this. You could try experimenting by placing the call to sp_setapprole within the query window just before the select to see if it would execute the query in the correct context. I'm not sure whether RS reuses connections to execute multiple dataset queries so you may need to put this in every query. Like I said, never tried it, so you'll need to experiment.|||

All my reports use stored procedures to populate the DataSet.

I put Execute As in the stored procedure to impersonate the user:

ALTER PROCEDURE [dbo].[usp_Cheque]

@.CRS nvarchar(50)

EXECUTE AS 'dbo'

AS

BEGIN

DECLARE @.Temp AS nvarchar(50)

SET NOCOUNT ON;

CREATE TABLE #Temp(CRS BigInt)

WHILE LEN(@.CRS) > 0

IF CHARINDEX(',',@.CRS) = 0

BEGIN

SET @.Temp = @.CRS

INSERT INTO #Temp VALUES(@.Temp)

SET @.CRS = ''

END

ELSE

BEGIN

SET @.Temp = LEFT(@.CRS,CHARINDEX(',',@.CRS)-1)

INSERT INTO #Temp VALUES(@.Temp)

SET @.CRS = RIGHT(@.CRS,LEN(@.CRS)-LEN(@.Temp)-1)

END

END

SELECT

Nombre,

NomCheque,

NumCR,

FechaCR,

Importe

FROM

CatProveedores INNER JOIN ContraRecibos

ON CatProveedores.CveProveedor = ContraRecibos.CveProveedor

WHERE

NumCR IN (SELECT CRS FROM #Temp)

But Im still getting that EXECUTE PERMISSION denied on usp_Cheques .....dbo' Error when trying to display the report

I have to give users Domain\Administrator Windows Role so they view the reports and then change their users back to Domain\Users when reporting is done...

|||

Let me try to explain it again (my english is not so good)

When a user try to log in the database (throught my application), the application run a setapprole. So the user is not the one connecting directly to the database, if fact, no user can connect to the database (only domain\administrators). Is the application role (that as all privileges) the one that give access to tables, sp, view, etc...

How can I do so in Reporting Services? RS tries to enter the database as the computer user, but as I said before, no computer user can enter the database, I need to tell RS that when trying to connect to the database, run the set approle command.

How do I do that?

Thank's for your attention.

Giber

|||

Hi Giber,

What are the credentials you are using for your datasource for stored procedures?

If you use sa or some admin role for data source credentials, the report should be able to run the stored procedure in the database. You can try running the report(preview) in local mode.

Also, if you are trying to control access for your reports, you can create different roles and groups and assign groups to these roles in SQL Server 2005 Reporting services. Or you can control the access for each report by giving appropriate permissions directly to that particular user or group. You will have to manuaaly ensure that the groups you make for Reporting Services match with the ones in SQl Server database.

Hope this will solve your problem.

Regards