Prev: Export to excel fit a matrix to one page
Next: how to download an image into excel ?
From: Alex Thomas on 23 Apr 2010 00:50
Our SSRS instance is hosted in infrastructure that does not have Active
Directory.

The SSRS has a custom security extension and the process is running under
the Network Service account. The custom security extension populates the
'User!UserID' runtime variable within SSRS.

I have Oracle colleagues who want to know if there is a way that their
sys_context('USERENV', 'OS_USER') calls can be populated with the User!UserID
variable.

This is my response:

I am virtually certain that there is no practical way that SSRS can
substitute the 'OS_USER' value within the Oracle driver, because as I
understand it the SSRS process would need to make a very low-level call
within the OS stack to a DHS customised variant of the Oracle driver.

This issue has been raised a number of times, and my understanding is that
what is trying to be achieved here is to avoid having to re-implement a suite
of SSRS report queries to dynamically employ the SSRS 'User!UserID' value,
where the queries for the reports are currently employing the
sys_context('USERENV', 'OS_USER') call.

To help understand what is going on, this is my understanding.

Because the CRT infrastructure is EAZ hosted there is no Integrated Windows
Authentication, and so the CRT processes are run Anonymously under the
Network Service account.

My understanding is that the sys_context('USERENV', 'OS_USER') call is being
populated not by the SSRS process, but by the Oracle driver reading the SSRS
process at the OS level.

Where the query for a report is developed using a client tool on HSNet
(where there is Integrated Windows Authentication) and employs the
sys_context('USERENV', 'OS_USER') call, the query cannot be implemented by
simply copying and pasting the P/SQL into the report's dataset definition.
The query will need to be reimplemented in a parameterised form, where the
parameter is populated using the SSRS's User!UserID runtime variable.

Reports that employ the sys_context('USERENV', 'OS_USER') call will run in
the HSNet-hosted CRT Dev environment just fine and return the expected value
(equivalent to the User!UserID variable), however while they are functionally
equivalent values when run within HSNet the underlying mechanism of their
population is entirely different.

When a report such as the above gets deployed to CRT staging or Production
the only way to get the user and use it within the report query is to
parameterise the query and populate the parameter with the User!UserID SSRS
runtime variable.

Could someone correct me or verify this?

Thanks, Alex
From: Bruce L-C [MVP] on 23 Apr 2010 13:05
I think you have a strong understanding of the issue. I do not see any holes
in your description of what is happening. You have User!UserID. No way
around it, the queries will need to use that (or it needs to be passed to
the stored procedure).

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Alex Thomas" <AlexThomas(a)discussions.microsoft.com> wrote in message
news:4C68730E-7ED8-4AD6-83F9-658C2902A994(a)microsoft.com...
> Our SSRS instance is hosted in infrastructure that does not have Active
> Directory.
>
> The SSRS has a custom security extension and the process is running under
> the Network Service account. The custom security extension populates the
> 'User!UserID' runtime variable within SSRS.
>
> I have Oracle colleagues who want to know if there is a way that their
> sys_context('USERENV', 'OS_USER') calls can be populated with the
> User!UserID
> variable.
>
> This is my response:
>
> I am virtually certain that there is no practical way that SSRS can
> substitute the 'OS_USER' value within the Oracle driver, because as I
> understand it the SSRS process would need to make a very low-level call
> within the OS stack to a DHS customised variant of the Oracle driver.
>
> This issue has been raised a number of times, and my understanding is that
> what is trying to be achieved here is to avoid having to re-implement a
> suite
> of SSRS report queries to dynamically employ the SSRS 'User!UserID' value,
> where the queries for the reports are currently employing the
> sys_context('USERENV', 'OS_USER') call.
>
> To help understand what is going on, this is my understanding.
>
> Because the CRT infrastructure is EAZ hosted there is no Integrated
> Windows
> Authentication, and so the CRT processes are run Anonymously under the
> Network Service account.
>
> My understanding is that the sys_context('USERENV', 'OS_USER') call is
> being
> populated not by the SSRS process, but by the Oracle driver reading the
> SSRS
> process at the OS level.
>
> Where the query for a report is developed using a client tool on HSNet
> (where there is Integrated Windows Authentication) and employs the
> sys_context('USERENV', 'OS_USER') call, the query cannot be implemented by
> simply copying and pasting the P/SQL into the report's dataset definition.
> The query will need to be reimplemented in a parameterised form, where the
> parameter is populated using the SSRS's User!UserID runtime variable.
>
> Reports that employ the sys_context('USERENV', 'OS_USER') call will run in
> the HSNet-hosted CRT Dev environment just fine and return the expected
> value
> (equivalent to the User!UserID variable), however while they are
> functionally
> equivalent values when run within HSNet the underlying mechanism of their
> population is entirely different.
>
> When a report such as the above gets deployed to CRT staging or Production
> the only way to get the user and use it within the report query is to
> parameterise the query and populate the parameter with the User!UserID
> SSRS
> runtime variable.
>
> Could someone correct me or verify this?
>
> Thanks, Alex

 | 
Pages: 1
Prev: Export to excel fit a matrix to one page
Next: how to download an image into excel ?