SSIS As SSRS DataSource.. SSRS Credentials Passing

[sam01m]

From what I've come to understand, a user logs into the SSRS site and his/her
Windows Identity is passed into the SSRS server. We, on the SSIS server-side,
had to grant access to "Domain\Domain Users" into DCOM in order for anything
to work. So now everyone is able to use SSIS within SSRS, as a datasource.
The problem is, at what point in the process does the user's credentials stop
being forwarded in favor of "NT AUTHORITY\NETWORK SERVICE" and how can I
change that behavior so the database knows WHO is truly attempting to access
the data and is able to filter access accordingly.

We've had to grant "NT AUTHORITY\NETWORK SERVICE" access to any database
we've attempted to provide via SSIS, which has worked fine up til now, but we
need to be able to filter access at the database level instead of at the SSRS
level, but I cannot figure out how to propogate the Windows Login Name (not
even the password, just the name) to the underlying SSIS datasource.

There simply must be a way to, even manually, grab that value and force it
forward, even if I have to pass it as a variable, but where does it get
dropped? Where must I pick up the name and push it forward so that it carries
onward?

[Charles Wang [MSFT]]

Hi Sam,
For the account NT AUTHORITY\NETWORK SERVICE, when it tries connecting to
your remote SQL Server instance, the passed network credential will be
DOMAINNAME\COMPUTERNAME$. You can use SQL Profiler (with the event "Audit
Login Failed" checked under Security Audit) to monitor your SQL Server and
you will know which user is trying to connect to your SQL Server.

Best regards,
Charles Wang

[sam01m]

I have seen the credentials that you speak of... DomainName\ComputerName$ get
passed into SQL Server, but SQL Server is configured to use Windows
Integrated Security (i.e. Active Directory) and the ComputerName is not
authenticated on Sql Server. I need to find a means of extracting the actual
logged in user on that machine and passing that DomainName\UserName to SQL,
or something similar.

Currently, I'm using impersonating a DomainName\ServiceAccount prior to
accessing SQL Server. This works, as I'd mentioned, but it does not show who
is at the requesting end. When I comment out the Impersonation code, I see
what you had mentioned DomainName\ComputerName$, but like I said, that still
doesn't tell me who is at the requesting end, and ComputerNames do not have
SQL rights in our configuration.

Post by Charles Wang [MSFT]
Hi Sam,
For the account NT AUTHORITY\NETWORK SERVICE, when it tries connecting to
your remote SQL Server instance, the passed network credential will be
DOMAINNAME\COMPUTERNAME$. You can use SQL Profiler (with the event "Audit
Login Failed" checked under Security Audit) to monitor your SQL Server and
you will know which user is trying to connect to your SQL Server.
Best regards,
Charles Wang

[Charles Wang [MSFT]]

Hi Sam,
You are in a double-hop scenario. Kerberos authentication is required if
you want to see your client's credential authenticated at your SQL Server
side. You need to make sure that:
1. Both your SQL Server service and IIS for your report server has correct
SPN registered in your domain controller.
2. Delegation for IIS has been configured
3. Enable Integratede Windows Authentication on your client computer's IE
(by default enabled).

For detailed steps, please refer to this KB article:
How to use Kerberos authentication in SQL Server
http://support.microsoft.com/kb/319723

Best regards,
Charles Wang