Connecting Tableau to SQL Server – Impersonation options

When publishing a Data Source to Tableau Server, you can choose to impersonate another user. Impersonation helps to apply the security context that is specific to the user being impersonated; in case of Tableau, it will be the user logging into Tableau; thus allowing the tableau user to only see the data that he/she is allowed to see.

As a reminder, when Impersonation is involved, the login connecting to the database is decided as follows:

1. If the Data Connection is created using Windows Authentication, then the service account under which Tableau Service (tabsvc) is running on its server .
2. If the Data Connection is created using SQL Server Authentication, then the SQL Server login used to create the Data Connection during design time.

For example, let us say Tableau service (tabsvc) is running under a domain account called MyDomain\tabadmin (this can  be found by opening services.msc) and a domain user called “Dave Tableau” with an AD account MyDomain\dtableau is logged into Tableau. Here, if the Data Connection is created using Windows Authentication, then MyDomain\tabadmin is used to connect to the database and once inside the database, MyDomain\tabadmin will be impersonating MyDomain\dtableau.The following screenshot of the profiler shows the above in action:

Also, the behavior is the same if using a Data Connection that is created with SQL Authentication. Except in this case, the login that is used to create the Connection is used to connect to the database and impersonate the user. Here is the profiler screenshot when accessing a report that uses Impersonation under SQL Authentication. The sql login ‘skolli’ connects to the database and ‘skolli’ then impersonates MyDomain\dtableau.

Notes on Impersonation

Impersonation is achieved by running the command EXECUTE AS USER = ‘Database_User_Name’ WITH NO REVERT; Let us look at some relevant issues with this statement. Also, the explanation below uses a Data Connection that uses Windows Authentication; but the principles hold the same for a Data Connection that uses SQL Server Authentication.

Permission to “EXECUTE AS”

The first prerequisite is that the Service account running the Tableau Service (tabsvc) should be able to connect to the database. In the above example, MyDomain\tabadmin should be created as a login in SQL Server and granted the permission to connect to the Database Engine.

The user being impersonated (MyDomain\dtableau) may nor may not have the permission to connect to the database engine; but that accound should have a active Database User in the required database.

The second prerequisite is that the Service account running the Tableau Service (tabsvc) should be able to impersonate the user that needs to be impersonated. So, “GRANT IMPERSONATE” permission on MyDomain\dtableau should be granted to MyDomain\tabadmin.

There is a catch in granting the Impersonate permission. Creating a login for a Windows user in SQL Server can be done directly for the user in question or implicitly through the Active Directory group that the user is part of. In the first case, a row for the user will be found in sys.server_principals with type_desc = ‘WINDOWS_LOGIN’ and in the second case a row for the user’s Active Directory will be found in sys.server_principals with type_desc = ‘WINDOWS_GROUP’. Either way, the windows user can have required access in SQL Server.In most cases, if there are a large of number of users to be granted access to SQL Server, then it is more manageable to grant access to their Active Directory groups rather than granting access directly to the user in question. In such a case, the user will not have a explicit row in sys.server_principals but the user’s Active Directory will be present with a row of type_desc = ‘WINDOWS_GROUP’. In this scenario though, the windows user cannot be impersonated by another login unless the login doing the impersonation has sysadmin rights. In our example, if MyDomain\dtableau has access to the database implicitly because his Active Directory has access to database, then for MyDomain\dtableau to be successfully impersonated by MyDomain\tabadmin, MyDomain\tabadmin should have sysadmin rights. If MyDomain\tabadmin does not have sysadmin privileges, then it cannot impersonate MyDomain\dtableau in the above scenario. However, if the Windows user being impersonated is granted access directly to the database instead of implicitly through a Active Directory, then a non-sysadmin account can be granted the privileges to impersonate that Windows user. So, in our example scenario, if MyDomain\dtableau has access to the database directly instead of implicitly through the Active Directory, then MyDomain\tabadmin can be given permission to impersonate MyDomain\dtableau even if MyDomain\tabadmin has no sysadmin privileges.

USER vs LOGIN

Do note that the EXECUTE AS statement refers to the USER and not the LOGIN. This means, the standard scope restrictions of USER vs LOGIN apply. That is, if the query has cross database references, then the EXECUTE AS USER statement would fail; even if the impersonated user has rights in the other database.

For example, of the two statements below, the first one would run, where as the second one would fail.

<code>

execute as login = ‘MyDomain\dtableau’
select * from AnotherDatabase.dbo.CrossDatabaseView
revert;

execute as user = ‘MyDomain\dtableau’
select * from AnotherDatabase.dbo.CrossDatabaseView
revert;

</code>

This means that queries referring to cross database objects (or any other kind of database context switching) would not work if you decide to use the Impersonation option in Tableau. The work around would be to either extend impersonation across databases (look here) or eliminate cross database references by duplicating those objects.

REVERT Option

Finally, the EXECUTE AS statement includes the NO REVERT option. That is, subsequent queries are still run under the impersonated account context.

Summary

To summarize:

1. Make sure that account used to run Tableau Service can connect to the SQL Server database (if using Windows Authentication for creating the Data Connection)
2. Make sure that the account used to run Tableau Service (in case of Windows Authentication) or the SQL account (in case of SQL Authentication) can impersonate the user logged into Tableau.
3. If the user logged into Tableau can access the SQL Server database implicitly through an Active Directory, then the sql account from step 2 above should have sysadmin privileges to impersonate the user. If the user has direct access to SQL Server (not through an AD account), then the sql account from step 2 above need not have sysadmin privileges to be granted impersonate permissions.
4. Since the Impersonation is done at database user level (EXECUTE AS USER), then any cross database queries will fail to run