1 of 1 people found this helpful
I've done it on a test server and it worked well. However the configuration with SQL Server is quite... hmmm... fragile. This is more of a statement on configuring SQL Server in the right way and giving the user the right permissions to impersonate (sysadmin or specific IMPERSONATE permissions). The other aspect is creating your view that uses the impersonated user to filter out the data based on the rules set in your user access table.
Anyone else tried the SQL Impersonation feature yet? Would be good to hear other perspectives.
Thanks Francois, I shall pass this on to our team.
1 of 1 people found this helpful
We have it implemented on a test server also. I echo Francois comments.
You have to give the active directory account that Tableau Server is running under specific permissions (IMPERSONATE). You then need to add EVERY SINGLE user you want to access the database individually. You can't just add a Windows Group to the server and call it good. We like to manage our users via groups & roles. So, if you have a Windows Group called TableauUsers, and add that as a SQL login and even if you give it sysadmin privleges, you still have to go to the specific database and add each users active directory id individually.
And he's right, if you're then going to implement row level security at the database level, you need to have some sort of "security table" in which you determine the users name and the values they're authorized to view and then use a view using the SUSER_NAME() function most likely. This is what we've done and it works good.
It's nice for a user to log into the web site and see only the values they're authorized to see in the quick filters.
Glad to hear that you got it to work @ihavenosuperpower. Have you tried making the user a sysadmin? I believe that sysadmin is the only role that can impersonate users in a group without requiring you to add each AD user to SQL Server and without requiring IMPERSONATE rights for each user.
Andy somehow roped me into writing a blog post outlining the process to get this work. I'll try to get this done next week.
1 of 1 people found this helpful
The way it appears to work...at least this is the way I got it to work was this:
1. The Tableau Server service account needs to have IMPERSONATE permissions on the SQL Server. (fortunately for us this service account also happens to be the same account the actual SQL Server service is running under)
2. Add the users as logins to SQL Server. We do this via a local Windows Group. (that way we don't have 100's of logins within SQL Server to individually audit and manage)
3. Here's the part which I think kinda stinks. You then need to add each user individually as a database user for the specific database. You can't just give the local Windows Group access to the database. I understand this since the service account needs to impersonate an actual active directory user and the local Windows Group doesn't qualify. I'm sure this has to do with Windows SIDs etc.
The one nicety is that if your browser is using windows authentication, it can make for a seamless user experience. There's no need to log into Tableau Server or the underlying data source, and authentication can all be managed with your existing active directory processes. You don't have to manage a bunch of extra user id's/passwords.
It also allows for another way to audit specific user activity at the data level for highly confidential data. Otherwise, all the DBA sees is activity for the service account. (the way it currently is in 6.0)
Thanks for your feedback, I'm glad to hear that this is working for you.
I believe what Francois was suggesting is that you could have the service account be a member of the SQL Server sysadmin group, which is the only role that supports IMPERSONATE rights on an entire group. This saves the burden of having to explicitly grant rights for each user, but it comes with the risk of relying on such a powerful SQL Server account.
Are you certain that SUSER_NAME is working correctly? We have just been testing the beta, and this is what we found:
We created a view using the SUSER_NAME() function e.g. select * from fact_balances where user_name = SUSER_NAME().
A power user created a report using this live view and published it to the server. We used the "impersonate as" option in the data source. The power user had access to all data.
We then logged in as a basic user, who only had access to a small portion of the data. When she ran the report, she could see the same data as the user who published the report - not just a subset. When I traced what was happening under the bonnet using SQL Profiler, I could see Tableau verifying that the current logged in user had database access - but it didn't actually run the SELECT to execute the query. It appeared to be using cached data. I also setup a Database Audit on SELECT for the particular view, but Tableau never actually ran the query.
The testing was done on different machines with a cleared cache each time.
We have setup each user individually on SQL Serve, given the tableau service account dbo to the data source, and we have granted impersonate rights.
Is there anything we have to do on the server to stop the caching?
In our tests we have created a view which enforces row-level security with a filter on SUSER_SNAME, not SUSER_NAME. I do not know if this is the root of the problems you are seeing.
Using the SQL Profiler are you able to see queries which establish the Impersonation context? You should see Tableau issue a query like the following:
EXECUTE AS USER = 'DOMAIN\username' WITH NO REVERT
I would like to investigate this further. Please file a Beta bug so we may collect the details of this problem, including log files from both Desktop and Server. If you have trouble getting traction on this issue quickly please let me know so I can engage directly.
William, I have noticed the issue you mention. We're still in testing so I didn't think of the ramifications of the caching issue, but we ended up having the test user click on the refresh data icon on the worksheet and they then only saw what they were supposed to, but if this is a systemic issue, then it's basically a useless feature. It seems that the server caches the "SELECT * FROM VIEW" syntax, but doesn't realize that the results are different based upon user....Is there an option NOT to cache per workbook? Ideally, the server would know that the workbook is using IMPERSONATE AS and NOT cache the results...or do it only per user (which would seem to hurt the scalability)
We do take many steps to ensure that the workbook connections are not shared between users when Impersonation is in effect. However there may be bugs (indeed making this feature rather useless!) which we will aggressively investigate once we get your Desktop and Server logs and any additional details you can provide. Please file a Beta bug report as soon as possible.
I am able to reproduce this problem internally, so I am confident it will be addressed before the product is released. In fact I aim to have it addressed by Beta 3. Fortunately the problem appears to be isolated to a very specific type of cache known as the 'visual model cache'.
In the meantime you can work around the problem by disabling the 'visual model cache'. You can do this one of two ways:
* tabadmin set vizqlserver.modelcachesize 0
* edit 'data\tabsvc\config\workgroup.yml' and edit 'vizqlserver.modelcachesize' to be 0.
After this, you will need to restart Server.
Please do take a moment to test Impersonation with model caching disabled. If you find any other problems, this will give us enough advanced notice to address these problems as well before shipping.
Thanks for the update. Presumably, setting modelcachesize is global, so the workaround would affect the efficiency of all Server reports while it was in place? Not a big deal, as the release will have impersonation all working!
Yes, the change will be global -- don't forget to restore your prior setting when you upgrade to the final release! You can reset it to the default with:
tabadmin set vizqlserver.modelcachesize -d