Connecting Tableau to SQL Server

A Tableau Data Connection to a SQL Server relational database can be made by using either Windows Authentication or SQL Server Authentication. After creating the Data Connection, the Data Source can be published to Tableau Server. While publishing, the Data Source can be configured to use a specific Authentication. Here is a list ofchoices.

http://kb.tableausoftware.com/articles/knowledgebase/tableau-server-database-logins

The choices made while creating the Data Connection and publishing the Data Source determine how logging into and reading data from a SQL Server database behaves when a workbook deployed to Tableau Server is opened.

The choice made during creating the Data Connection determines which SQL Server login is used to make connection and log into the database engine. Whereas, the Authentication choice made during publishing the Data Source to Tableau Server determines the security context under which the query is run.

Account used to connect to SQL Server
If a Data Source published to Tableau is created from a Data Connection that uses Windows Authentication, then the service account under which Tableau’s service (tabsvc) is running on the server will be used to make a connection to the database. This is done irrespective of the Authentication option chosen when publishing the Data Source to Tableau Server.

If a Data Source published to Tableau is created from a Data Connection that uses SQL Server authentication (“Use a specific username and password” option in Tableau), then the credentials used when creating the connection is used to connect to the database. Except in one case. If the option “Prompt User” is selected under Authentication options when publishing the Data Source to Tableau Server, then the user is prompted to provide a sql server login details and the provided account is used to connect to the database.

Security Context under which the query is run

The Authentication choice made in Tableau when publishing the Data Source to the server determines the security context under which the query runs.

The above link provides a summary of Authentication options available under each Data Connection choice. If the Impersonate option is chosen, then the user logging into Tableau is impersonated by the SQL Server login logging into the database. Otherwise, the query is run under the security context of the SQLServer login logging into the database.

Summary

The following table summarizes the behavior.

Database Connection Options
Database logon account uses… Authentication mode SQL Account Logging into Database Security Context
Windows NT Integrated security Server Run As account Account used to run Tableau Service (tabsvc) Account used to run Tableau Service (tabsvc)
Impersonate via server Run As account Account used to run Tableau Service (tabsvc) The user logged into Tableau
Specific username and password Prompt users: Users are prompted for their database credentials. Credentials can be saved. The Non-WindowsCredentials that user provides at run time The Non-WindowsCredentials that user provides at run time
Embedded password: The database username and password are embedded. The sql login used to create the Data Connection during design time The sql login used to create the Data Connection during design time
Impersonate via embedded password: The database username and password with impersonate permission are embedded. The sql login used to create the Data Connection during design time The user logged into Tableau
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s