Reports run under my credentials, not those stored on report

I've created a report in SSRS 2005 that sources from a SSAS 2000 cube (on another server). In BIDS (running on the SSRS 2005 server) I'm using a shared data source with "generic" credentials to access the data. The generic credentials I'm using is an account that has datareader access. In BIDS the report runs, builds and deploys just fine. No problem so far!

My problem has to do with running the report from the Report Server. The only way I can get the report to run from the Report Server web site is to modify the data source and pass my credentials in the report (thereby overriding the credentials stored in the report). Why would this be?

SSRS is running on a Windows Server 2003 machine and I've installed the SQL 2005 SP1. I've also modified the Report Server web site on the server to include the Everyone group, with appropriate permissions.

I'm at a total loss here. I know I'm an admin on the server that is running SSRS, but why is it that only my credentials can be used to run the report? I haven't tried using the credentials of another admin, but I'm assuming their's would work as well. I guess I don't understand why the generic credentials won't work.

I thought I might be experiencing the "double-hop" issue, but I don't think my credentials would work with double-hop. Or would they?

Anyway, any help is appreciated.

I guess you're storing Windows credentials. Verify that 'use as windows authentication credetials' is checked for the stored credentials on the server database credentials page.

-Lukasz

|||

Well, I found my problem...

In the cube, I added the Everyone group as a user and now everthing works fine. A little confusing since the report ran without a problem in BIDS. However, this would explain why I had to pass my NT credentials to get the report to run.

|||I've been dealing with the same kind of issues. My conclusion is

that (using NTLM and not Kerberos) RS and AS have to be on the same

machine.

I don't think it's going to work for us to use Everyone for security.

An alternative might be to add the security for the machine account

that RS runs under (ASPNET?) to one box to an AS role on the other box,

but I don't see how to do that, since the machine account is not in a

domain that's recognized on the AS box.

Jim|||Ok, I got it going, thanks to the instructions below from MS. We have a dummy userid

we use for this.

1. Publish your report & data source from VS

2. Use Report Manager to browse to the report, click Properties

3. Click Data Sources, Choose “Credentials Stored securely in the report server….”

4. Enter UID/PWD

5. Select “Use as Windows Credentials when connecting to the data source” checkbox

6. *Click Apply*

7. View report again, click

Properties, go back to Data Source and make sure the changes you made

have indeed been applied.|||

Yeah, the Everyone thing is a temporary work-a-round. I agree with you that this is a Kerberos issue. Unfortunately, RS and AS can't run on the same machine in our environment.

I'm still researching and I'll keep y'all posted.

|||

AS only allows windows credentials to access data. So you're limited to one of three options:

1) use stored credentials on the report server - all users access the data using the same credentials

2) use integrated security AND EITHER enable Kerberos OR put RS and AS on the same computer - more difficult configuration

3) prompt the user for their credentials - more onerous user experience

The balance is up to you to strike based on your business needs. Generally granting Everyone permission to your corporate data is a bad idea :-).

-Lukasz

|||

Thanks. I'll research #2.

|||

Hi Lukasz,

Our probleam seems like this.

We have an system, with only one user to access AS05. We have an security level with users, roles and groups. When our user loggin into our system (ASP.NET 2), we concatenate in connection string same roles. With this roles, the user can see only him data.

In our system, this work fine, but how can I do this in SSRS?

Thanks,

Cristiano Leite.

Reports run under my credentials, not those stored on report

I've created a report in SSRS 2005 that sources from a SSAS 2000 cube (on another server). In BIDS (running on the SSRS 2005 server) I'm using a shared data source with "generic" credentials to access the data. The generic credentials I'm using is an account that has datareader access. In BIDS the report runs, builds and deploys just fine. No problem so far!

My problem has to do with running the report from the Report Server. The only way I can get the report to run from the Report Server web site is to modify the data source and pass my credentials in the report (thereby overriding the credentials stored in the report). Why would this be?

SSRS is running on a Windows Server 2003 machine and I've installed the SQL 2005 SP1. I've also modified the Report Server web site on the server to include the Everyone group, with appropriate permissions.

I'm at a total loss here. I know I'm an admin on the server that is running SSRS, but why is it that only my credentials can be used to run the report? I haven't tried using the credentials of another admin, but I'm assuming their's would work as well. I guess I don't understand why the generic credentials won't work.

I thought I might be experiencing the "double-hop" issue, but I don't think my credentials would work with double-hop. Or would they?

Anyway, any help is appreciated.

I guess you're storing Windows credentials. Verify that 'use as windows authentication credetials' is checked for the stored credentials on the server database credentials page.

-Lukasz

|||

Well, I found my problem...

In the cube, I added the Everyone group as a user and now everthing works fine. A little confusing since the report ran without a problem in BIDS. However, this would explain why I had to pass my NT credentials to get the report to run.

|||I've been dealing with the same kind of issues. My conclusion is that (using NTLM and not Kerberos) RS and AS have to be on the same machine.
I don't think it's going to work for us to use Everyone for security.
An alternative might be to add the security for the machine account that RS runs under (ASPNET?) to one box to an AS role on the other box, but I don't see how to do that, since the machine account is not in a domain that's recognized on the AS box.
Jim
|||Ok, I got it going, thanks to the instructions below from MS. We have a dummy userid
we use for this.
1. Publish your report & data source from VS
2. Use Report Manager to browse to the report, click Properties
3. Click Data Sources, Choose “Credentials Stored securely in the report server….”
4. Enter UID/PWD
5. Select “Use as Windows Credentials when connecting to the data source” checkbox
6. *Click Apply*
7. View report again, click Properties, go back to Data Source and make sure the changes you made have indeed been applied.
|||

Yeah, the Everyone thing is a temporary work-a-round. I agree with you that this is a Kerberos issue. Unfortunately, RS and AS can't run on the same machine in our environment.

I'm still researching and I'll keep y'all posted.

|||

AS only allows windows credentials to access data. So you're limited to one of three options:

1) use stored credentials on the report server - all users access the data using the same credentials

2) use integrated security AND EITHER enable Kerberos OR put RS and AS on the same computer - more difficult configuration

3) prompt the user for their credentials - more onerous user experience

The balance is up to you to strike based on your business needs. Generally granting Everyone permission to your corporate data is a bad idea :-).

-Lukasz

|||

Thanks. I'll research #2.

|||

Hi Lukasz,

Our probleam seems like this.

We have an system, with only one user to access AS05. We have an security level with users, roles and groups. When our user loggin into our system (ASP.NET 2), we concatenate in connection string same roles. With this roles, the user can see only him data.

In our system, this work fine, but how can I do this in SSRS?

Thanks,

Cristiano Leite.

Reporting Services, MDX, large dimensions, and dimension properties.

I am using Reporting Services against a SSAS 2005 (SP2) database.

I have a very large Items dimension (about 2.8 million items), which has a hierarchy of Category - Family - Series.

I need to produce a report that has monthly orders for all items (well, it has more than this - past sales and current inventory and vendor purchase orders, but I'll simplify the scenario here).

I need to show the item number, description, series, family, and category on the report, plus the month of sales across the columns.

The query builder in SSRS designer gives me this MDX:

SELECT

NONEMPTY { [Measures].[Order Quantity] } ONCOLUMNS,

NONEMPTY {

([Items].[Category].[Category].ALLMEMBERS *

[Items].[Family].[Family].ALLMEMBERS *

[Items].[Series].[Series].ALLMEMBERS *

[Items].[Item Number].[Item Number].ALLMEMBERS *

[Items].[Description].[Description].ALLMEMBERS *

[Time].[Month].[Month].ALLMEMBERS ) }

DIMENSIONPROPERTIESMEMBER_CAPTION, MEMBER_UNIQUE_NAMEONROWS

FROM [Consolidated] CELLPROPERTIESVALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The problem is that the cross-join for all those Items attributes quickly overwhelms the 4 billion tuple limit. (2 million item numbers * 2 million descriptions * 50 categories * 50 families * 250 series = a big number)

If I add filter parameters to limit this, the query designer puts them in the FROM clause, but I don't know when the cross join is performed - from the resulting filtered FROM cube? It doesn't seem to do that.

In my reading on MDX, I see that there are dimension properties that seem like just the ticket to get around this tuple limitation...

SELECT

NONEMPTY { [Measures].[Order Quantity] } ONCOLUMNS,

NONEMPTY {

[Items].[Item Number].[Item Number].ALLMEMBERS *

[Time].[Month].[Month].ALLMEMBERS ) }

DIMENSIONPROPERTIES

[Items].[Category].[Category],

[Items].[Family].[Family],

[Items].[Series].[Series],

[Items].[Description].[Description],

MEMBER_CAPTION, MEMBER_UNIQUE_NAMEONROWS

FROM [Consolidated] CELLPROPERTIESVALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

(Assuming I can get the above to work - see this thread.)

When I run this in the MDX query window in SQL Management Studio, I don't get additional columns in the result set, but I presume that these attributes become (custom?) properties of the Items.ItemNumber column in the resultset. Are these properties accessible in the dataset created by Reporting Services? If so, how?

If not, then how can I show the descriptive attributes of the dimension in the report without the huge cross-join?

(As an aside, I am sure hoping the next generation of SSRS (or some 3rd party controls) will replace the matrix control which can only handle a 2-dimensional resultset, so I don't have to flatten out a multi-dimensional, hierarchical dataset down to a 2-d table structure. It's ugly.)

Mike

You may want to take a look at the EXISTING and/or the NONEMPTY functions. You might be able to use these creatively on your row axis to limit the number of members in the set prior to the cross join.

B.