Integrated Security Problem

Tom Bean

unread,

Aug 16, 2005, 5:37:27 PM8/16/05

to

We are trying to control the access an individual user has to a report, for
example: 1) Bob has access to the customer report; 2) Judy has access to
all the reports; and 3) James has access only to the vendor report.

In order to accomplish this we are trying to use 'Integrated Security'
credentialing for out Data Sources. When we try to access reports after
setting 'Inetgrated Security', we get the error: "Login failed for user
'(null)'. Reason: Not associated with a trusted SQL Server connection."

It appears that the user's credentials are not being passed from the Web
Server to the SQL Server when trying to access the reports, however, Report
Manager functions perfectly. Both the Web Server and SQL Server are running
on Windows 2003 Server.

What can we do configure the Report Server so it behaves like Report
Manager?

Thanks,
Tom

Peter Yang [MSFT]

unread,

Aug 17, 2005, 1:36:28 AM8/17/05

to

Hello Tom,

To understand the issue better, I'd like to know how users access the
reports. Do they access reports via remport manager or via a customized web
application that using report server?

If the issue occurs within report manager when users try to access the
report, it seems that this is caused by the configuration of the credential
to access the data source of the specific reports.

I suggest that you configure the shared or custome data source of the
reports with the following configuration:

1. credentials supplied by the user running the report.

Each user need to input crediential to access data source each time.

2. Credential stored securely in the report server.

Report server save this credential and use this credential to access data
source no matter which user request the report

3. Windows NT Integrated security.

Each client use his log on credential to access data source of the reports.
You have to add login for the domain account and create users/grant
permission on the database the report requsts.

It seems that you check "Windows NT Integrated security" but the client
domain user does not have proper login added on the data source sql server
for the specific reports.

Thanks & Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: "Tom Bean" <tb...@newsgroup.nospam>
| Subject: Integrated Security Problem
| Date: Tue, 16 Aug 2005 16:37:27 -0500
| Lines: 21
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <OmIWKrqo...@TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 71.4.140.141.ptr.us.xo.net 71.4.140.141
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:50514
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs

Tom Bean

unread,

Aug 17, 2005, 3:33:04 PM8/17/05

to

Peter,

Our users need to access reports via the ReportServer web site, i.e.
http://domain/ReportServer, customized web applications, and Windows
applications. In addition, a few users need to access reports with Report
Manager to set the report properties and security.

We need to use Integrated Security to control a user's access to a
particular report, however, we can't get this to work.

For example, one of the reports has its data sources set up with these
options selected: 'A custom data source', "Connection Type: Microsoft SQL
Server', 'Connection String: data source=Dev01Sql;initial catalog=Vendor',
'Windows NT Integrated Security'.

I am an administrator for Dev01Sql and can access every database on the
server, but when I try to run the report from Report Manager or from the
ReportServer web site, I get the Reporting Services Error page with the
message "Login failed for user '(null)'. Reason: Not associated with a
trusted SQL Server connection."

Since Report Manager is accessing the ReportServer and ReportServerTempDB on
Dev01Sql using my credentials, I don't understand why the report cannot be
rendered. Is there some setting for the ReportServer web site that can be
changed to allow the same access to render the reports that Report Manager
has?

Thanks,
Tom

"Peter Yang [MSFT]" <pet...@online.microsoft.com> wrote in message
news:Mc24g2u...@TK2MSFTNGXA01.phx.gbl...

Peter Yang [MSFT]

unread,

Aug 18, 2005, 5:05:49 AM8/18/05

to

Hello Tom,

It seems that your credential only has permssion on ReportServer and
ReportServerTempDB other than the data source of the report itself.

Please double check if you could connect to the SQL server hosting the data
source of the report itself by using Query Analyzer. I assume it is a
different server from the report server. If not, please add your domain
accunt to the login of the server and add the proper database user mapping
to the login.

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: "Tom Bean" <tb...@newsgroup.nospam>

| References: <OmIWKrqo...@TK2MSFTNGP10.phx.gbl>
<Mc24g2u...@TK2MSFTNGXA01.phx.gbl>
| Subject: Re: Integrated Security Problem
| Date: Wed, 17 Aug 2005 14:33:04 -0500
| Lines: 131

| Message-ID: <#S4$PK2oFH...@TK2MSFTNGP09.phx.gbl>

| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 71.4.140.141.ptr.us.xo.net 71.4.140.141

| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:50575
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs

Tom Bean

unread,

Aug 18, 2005, 11:06:55 AM8/18/05

to

Peter,

As I told you in my previous message, I am an administrator on the SQL
Server hosting all the databases used to manage and render the reports. I
can access every database on the server. Therefore, that is not the
problem.

Do you have any other suggestions?

Tom

"Peter Yang [MSFT]" <pet...@online.microsoft.com> wrote in message

news:vBo$GQ9oFH...@TK2MSFTNGXA01.phx.gbl...

Peter Yang [MSFT]

unread,

Aug 19, 2005, 5:08:18 AM8/19/05

to

Hello Tom,

Going forward, I'd like to know the following information:

1. Which identity the applciation pool uses for the default web
site/reporting service? Is it Network service? If you temporarily add
Network service account or any identity for the applicaiton pool into local
admin groups, does it make any difference?

2. Did you try to run Query Analyzer to connect to the data source of the
specific report by using Windows authentication, is there any problem?

3. Did you check in reporting service log to see if there is any detailed
errors for this problem?

4. Does the issue occur with all domain users with local admin rights and
SQL server admin rights?

Thanks & Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: "Tom Bean" <tb...@newsgroup.nospam>

| References: <OmIWKrqo...@TK2MSFTNGP10.phx.gbl>
<Mc24g2u...@TK2MSFTNGXA01.phx.gbl>
<#S4$PK2oFH...@TK2MSFTNGP09.phx.gbl>
<vBo$GQ9oFH...@TK2MSFTNGXA01.phx.gbl>

| Subject: Re: Integrated Security Problem

| Date: Thu, 18 Aug 2005 10:06:55 -0500
| Lines: 217

| Message-ID: <u0dgMaAp...@tk2msftngp13.phx.gbl>

| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 71.4.140.141.ptr.us.xo.net 71.4.140.141

| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:50644
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs

Tom Bean

unread,

Aug 19, 2005, 5:02:50 PM8/19/05

to

Peter,

The answers to your questions are:

1. The application pool for the web site is running under NetworkService.
We added NetworkService to the local admin group and it still doesn't work.

2. We have connected to the databases used by the reports with Query
Analyzer using Windows authentication with no problem. We did this logged
on as users with permissions ranging from Users to Administrators.

In addition, when we set up the 'Connect Using' property of the data sources
to 'The credentials supplied by the user running the report' and check 'Use
as Windows credentials when connecting to the data source', we can supply
the same login name and password as the used to start Windows and
successfully render the report.

3. I checked the reporting service log and found many entries in the
ExecutionLogs table that failed with a StatusCode = 101
(rsProcessingAborted) but couldn't find any detailed information about what
caused the failure.

4. Yes, the problem occurs with all domain users with local admin rights
and SQL server admin rights.

Thanks,
Tom

"Peter Yang [MSFT]" <pet...@online.microsoft.com> wrote in message

news:LjuVM2Jp...@TK2MSFTNGXA01.phx.gbl...

Peter Yang [MSFT]

unread,

Aug 21, 2005, 11:12:53 PM8/21/05

to

Hello Tom,

Before we go further, I'd like to confirm if SQL server (data source of the
report) and IIS are in the same machine. The issue seems to be a problem
that IIS/Report server are in different machine hosting SQL server.

If it is the case, I suggest that you change the following configuration if
you are using Win2k/2k3 AD so that delegation is properly enabled

1. In AD: The Middle Computer should be trusted for delegation

2. In AD: The domain account under which SQL server is running should not
be marked as "sensitive for delegation", and "Accont is trusted for
delegation" shall be marked.

810572.KB.EN-US HOW TO: Configure an ASP.NET Application for a Delegation
Scenario
http://support.microsoft.com/default.aspx?scid=KB;EN-US;810572

For Win2003, you have to do both the above steps as under Win2k, and
additionally you have to do the following

1. In the middle computer: the domain account that IIS 6 application pool
associated with default Website MUST have Set ImpersonatePriviledge
granted. By default the application pool used by reporting services is the
deafult applciaton pool.

Note that this priviledge is new to Windows 2003.

2. The name of the privilege is "Impersonate a client after
authentication", you can grant it using Local Security Policy.

3. "Account is trusted for delegation " must be set to for above account.

Please refer to the following article for more details about
troubleshooting this issue

Troubleshooting Kerberos Delegation
http://www.microsoft.com/downloads/details.aspx?FamilyID=99b0f94f-e28a-4726-
bffe-2f64ae2f59a2&displaylang=en

How To Configure IIS to Support Both Kerberos and NTLM Authentication
http://support.microsoft.com/default.aspx?kbid=215383

Information about SQL Server 2000 Kerberos support, including SQL Server
virtual servers on server clusters
http://support.microsoft.com/?id=319723

Note: By using Windows authentication, each user access the report shall
have the proper permission on the sql server of data source.

Hope this information is helpful.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: "Tom Bean" <tb...@newsgroup.nospam>
| References: <OmIWKrqo...@TK2MSFTNGP10.phx.gbl>
<Mc24g2u...@TK2MSFTNGXA01.phx.gbl>
<#S4$PK2oFH...@TK2MSFTNGP09.phx.gbl>
<vBo$GQ9oFH...@TK2MSFTNGXA01.phx.gbl>

<u0dgMaAp...@tk2msftngp13.phx.gbl>
<LjuVM2Jp...@TK2MSFTNGXA01.phx.gbl>

| Subject: Re: Integrated Security Problem

| Date: Fri, 19 Aug 2005 16:02:50 -0500
| Lines: 338

| Message-ID: <uf1hzFQp...@TK2MSFTNGP15.phx.gbl>

| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 71.4.140.141.ptr.us.xo.net 71.4.140.141

| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:50786
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs

Tom Bean

unread,

Aug 29, 2005, 6:47:15 PM8/29/05

to

Peter,

Sorry I took so long getting back to you but we wanted to be sure we had
solved our problem before responding.

First, we set "Trust computer for delegation" in Active Directory for the
middle computer and the problem went away. Then, because we had tried so
many different settings, we uninstalled Reporting Services and reinstalled
it to ensure we were starting with an unmodified installation. Once
Reporting Services was reinstalled, the only change we made was to again set
"Trust computer for delegation" in Active Directory for the middle computer
and the problem was solved.

We had tried this setting before but it didn't solve the problem. The only
explanation for the setting not solving the problem the first time is that
we must have tried to access the reports before the change had time to
propogate through our network.

Thanks for your assistance. I hope this thread will save someone else some
of the headaches.

Tom

"Peter Yang [MSFT]" <pet...@online.microsoft.com> wrote in message

news:lIKJnds...@TK2MSFTNGXA01.phx.gbl...

Tom Bean

unread,

Aug 29, 2005, 7:56:27 PM8/29/05

to

Peter,

I wonder if you could help me with another problem. I am trying to call a
static method in a custom assembly with one of my reports but get the error
"[BC30469] Reference to a non-shared member requires an object reference".
The declaration of the method I am calling is: public static string
Decrypt(string encryptedText). The only thing references used in my custom
assembly are System, System.Data, and System.XML.

I am having the same problem with calling the
System.Web.HttpUtility.UrlDecode method.

I have added references to both my custom assembly and System.Web via the
References tab in Report Properties.

I can't think of anything else to do. Please help.

Thanks,
Tom

"Peter Yang [MSFT]" <pet...@online.microsoft.com> wrote in message

news:lIKJnds...@TK2MSFTNGXA01.phx.gbl...

Peter Yang [MSFT]

unread,

Aug 29, 2005, 10:59:43 PM8/29/05

to

Hello Tom,

Glad to hear the issue is resolved! You may want to post this issue in a
new thread so that it could be traced properly by others in the community.

For shared function, you shall call it via Code component. For example

public shared function Fn(input As Integer)
if input = 1
return "Hello!"
else
return "Bye!"
end if
end function

In the report, you refer to it as: =Code.Fn( CInt(Fields!CustomerID.Value ))

Hope this is helpful.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: "Tom Bean" <tb...@newsgroup.nospam>
| References: <OmIWKrqo...@TK2MSFTNGP10.phx.gbl>
<Mc24g2u...@TK2MSFTNGXA01.phx.gbl>
<#S4$PK2oFH...@TK2MSFTNGP09.phx.gbl>
<vBo$GQ9oFH...@TK2MSFTNGXA01.phx.gbl>
<u0dgMaAp...@tk2msftngp13.phx.gbl>
<LjuVM2Jp...@TK2MSFTNGXA01.phx.gbl>

<uf1hzFQp...@TK2MSFTNGP15.phx.gbl>
<lIKJnds...@TK2MSFTNGXA01.phx.gbl>

| Subject: Re: Integrated Security Problem

| Date: Mon, 29 Aug 2005 18:56:27 -0500
| Lines: 509

| Message-ID: <Ox0UmVPr...@TK2MSFTNGP15.phx.gbl>

| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 71.4.140.141.ptr.us.xo.net 71.4.140.141
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl

| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:51381
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs