Is it possible to pass-through Windows User logins from Datazen through to SQL Server?
Scenario:
I created a Dashboard which uses a SQL Query as a data source.
The data source is of type "SQL Server" and the flag Integrated Security is set to YES.
I've also configured the data source to be "Real Time," to avoid any issues with caching.
I'm expecting the data view to execute on SQL Server with the credentials of the user which is browsing the final dashboard, unfortunately this is not the case.
Problem:
In this scenario the authentication against SQL Server is now done with the Windows user account, under which the Service "Datazen Server Data Acquisition Service" is running. I would expect that the "Acquisition Service" will delegate the effective user. Is this possible? Or will the authentication always be done with the service account?
I know about the "personalize for each member" setting, which passes-through the username to a data view query, but this is not the same as my requirement (leverage existing MSSQL-DB-Security for effective windows-users).
Your observations are correct that by default, the service account will be recognized as being logged into SQL Server.
There's no way to get around that with settings, but you can use some T-SQL magic to switch users at runtime. You have to lead your queries with an EXECUTE AS statement, like so:
EXECUTE AS USER = 'DomainName\' + '{{ username }}'
SELECT TOP 1 login_name -- This is just a nice quick test to echo the username.
FROM sys.dm_exec_sessions -- You can swap it out for your real query.
WHERE session_id = @@SPID
This, of course, also requires the "Personalize for each Member" setting to be turned on, so that the username is passed through.
It's pretty self-explanatory what's going on here, but basically you have to explicitly impersonate the request via your service account, as SQL Server will be connected to via the database using that account. Once you run this EXECUTE AS statement, it will use that user account for the remainder of the session.
Note that your service account will need permission the IMPERSONATE permission set, or else this will fail. It will also fail, of course, for any users that exist in your Datazen Server but do not have permissions against your SQL Server, and vice-versa. That's definitely the desirable behavior, but it's worth keeping in mind if you ever add users to one, you'll also have to add them to the other.
Disclaimer: I'm a Microsoft Support professional, paid to support Datazen.