jie-d
commented
2 years ago Internal tracking id: 1343837
Closed kaklakariada closed 2 years ago
jie-d
commented
2 years ago Internal tracking id: 1343837
devanshsoni9
commented
2 years ago 
kaklakariada
commented
2 years ago Hi @devanshsoni9 Thank you very much for your test program, it was very helpful! Turned out that I had to create an SPN for the tableau user on the domain controller:
setspn -U -S TableauSvc/tableau tableauuserAfter this, the test program successfully connected to the database with the impersonated user.
But still Tableau Server (running on the same machine, with RunAs user EXAMPLE\tableauuser) connected to the DB as tableauuser. I compared the JDBC driver's debug log from your test program with Tablau Server's run. The difference was in the user property passed as driverProperties to DriverManager.getConnection("url", driverProperties):
user to an object containing principal tableauuser@EXAMPLE.COM and private credentials for the impersonated user.user to the value defined in connectionProperties.js. I used props["user"] = serverUser;as described in the documentation.I will try to follow the instructions at https://tableau.github.io/connector-plugin-sdk/docs/auth-modes.html and update the connector.
devanshsoni9
commented
2 years ago Hi @kaklakariada
In the test program, user property is assigned a valued equal to impersonatedUser, which is a String, so it should only contain the viewing user principal name. Not sure where you are seeing private credentials? (Maybe I got u wrong).
If you could send me debug logs (by redacting sensitive information) for both jprotosrv and log.txt, I might be able to help some.
kaklakariada
commented
2 years ago Good morning @devanshsoni9
These are the log from connecting to the database. Tableau Server uses RunAs user tableauuser and I am logged in to Tableau with user chris.
2021-11-11 05:30:38.852 +0000 (Default,chris,BB9AC00AC1CD48CE895882F81E50E5FF-1:0,YYyqfpy--Z62VFEJj6l_3gAAAgg,13,50) grpc-default-executor-2 : INFO com.tableausoftware.jdbc.JDBCDriverManager - Get driver from isolatedDrivers.
2021-11-11 05:30:38.852 +0000 (Default,chris,BB9AC00AC1CD48CE895882F81E50E5FF-1:0,YYyqfpy--Z62VFEJj6l_3gAAAgg,13,50) grpc-default-executor-2 : INFO com.tableausoftware.jdbc.JDBCProtocolImpl - Connecting to jdbc:exa:exasoldb.example.com:8563;validateservercertificate=1;fingerprint=ABD591342466880A16A4443DEEFF44A78A26E47514BE4D5E1C4CB712345F69CA;feedbackinterval=1;clientname=Tableau;kerberoshostname=exasoldb.example.com;kerberosservicename=exasol;debug=1;logdir=C:\tmp
2021-11-11 05:30:38.852 +0000 (Default,chris,BB9AC00AC1CD48CE895882F81E50E5FF-1:0,YYyqfpy--Z62VFEJj6l_3gAAAgg,13,50) grpc-default-executor-2 : INFO com.tableausoftware.jdbc.JDBCProtocolImpl - Connection properties {password=*******, gsslib=gssapi, jaasLogin=false, DelegationUID=*******, jdbc-driver-debug=*******, user=}
2021-11-11 05:30:38.852 +0000 (Default,chris,BB9AC00AC1CD48CE895882F81E50E5FF-1:0,YYyqfpy--Z62VFEJj6l_3gAAAgg,13,50) grpc-default-executor-2 : INFO com.tableausoftware.auth.KerberosContext - Retrieving TGT for tableauuser@EXAMPLE.COM using keytab C:\ProgramData\Tableau\tableau2.keytab
2021-11-11 05:30:38.903 +0000 (Default,chris,BB9AC00AC1CD48CE895882F81E50E5FF-1:0,YYyqfpy--Z62VFEJj6l_3gAAAgg,13,50) grpc-default-executor-2 : INFO com.tableausoftware.jdbc.JDBCDriverManager - Connected using driver {com.exasol.jdbc.EXADriver} from isolatedDriver.
{"ts":"2021-11-11T05:30:35.288","pid":14692,"tid":"3cbc","sev":"warn","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"msg","v":"SQLConnection::SQLConnection (attr constructor): m_dialect is null"}
{"ts":"2021-11-11T05:30:35.294","pid":14692,"tid":"3cbc","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"connect-using-keychain","v":{"xml":"\n \n","name":"federated.0x494si0sys0jo147h7mu0co3o8d"},"ctx":{"vw":"Sheet 1","wb":"New Workbook"}}
{"ts":"2021-11-11T05:30:35.288","pid":14692,"tid":"3cbc","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"begin-ds.connect-data-connection","l":{},"a":{"depth":4,"id":"C3lsqifeUV7IT3SWlM8mOv","name":"ds.connect-data-connection","root":"MM3c602Nk+KI8Zjhta3ZKR","sponsor":"JIvgnmTAkGpKqPEqq2gAt3","type":"begin"},"v":{"caption":"Untitled Data Source","class":"federated","ds":"Untitled Data Source","federated-connection":"\n \n","name":"federated.0x494si0sys0jo147h7mu0co3o8d"},"ctx":{"vw":"Sheet 1","wb":"New Workbook"}}
{"ts":"2021-11-11T05:30:35.289","pid":14692,"tid":"3cbc","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"end-ds.connect-data-connection","l":{},"a":{"depth":4,"elapsed":0,"exclusive":0,"id":"C3lsqifeUV7IT3SWlM8mOv","name":"ds.connect-data-connection","res":{"alloc":{"e":1.06e+04,"i":1.06e+04,"ne":97,"ni":97,"peak":6.78e+03},"free":{"e":4.64e+03,"i":4.64e+03,"ne":52,"ni":52},"kcpu":{"e":0,"i":0},"ntid":1,"ucpu":{"e":0,"i":0}},"rk":"ok","root":"MM3c602Nk+KI8Zjhta3ZKR","rv":{},"sponsor":"JIvgnmTAkGpKqPEqq2gAt3","type":"end"},"v":{"caption":"Untitled Data Source","class":"federated","ds":"Untitled Data Source","federated-connection":"\n \n","name":"federated.0x494si0sys0jo147h7mu0co3o8d"},"ctx":{"vw":"Sheet 1","wb":"New Workbook"}}
{"ts":"2021-11-11T05:30:35.289","pid":14692,"tid":"3cbc","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"begin-ds.load-metadata","l":{},"a":{"depth":4,"id":"HrgNLYPakNiLGaaGCHcAm6","name":"ds.load-metadata","root":"MM3c602Nk+KI8Zjhta3ZKR","sponsor":"JIvgnmTAkGpKqPEqq2gAt3","type":"begin"},"v":{"caption":"Untitled Data Source","class":"federated","ds":"Untitled Data Source","federated-connection":"\n \n","name":"federated.0x494si0sys0jo147h7mu0co3o8d"},"ctx":{"vw":"Sheet 1","wb":"New Workbook"}}
{"ts":"2021-11-11T05:30:35.289","pid":14692,"tid":"3cbc","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"end-ds.load-metadata","l":{},"a":{"depth":4,"elapsed":0,"exclusive":0,"id":"HrgNLYPakNiLGaaGCHcAm6","name":"ds.load-metadata","res":{"alloc":{"e":1.94e+03,"i":1.94e+03,"ne":19,"ni":19,"peak":7.36e+02},"free":{"e":1.68e+03,"i":1.68e+03,"ne":14,"ni":14},"kcpu":{"e":0,"i":0},"ntid":1,"ucpu":{"e":0,"i":0}},"rk":"ok","root":"MM3c602Nk+KI8Zjhta3ZKR","rv":{},"sponsor":"JIvgnmTAkGpKqPEqq2gAt3","type":"end"},"v":{"caption":"Untitled Data Source","class":"federated","ds":"Untitled Data Source","federated-connection":"\n \n","name":"federated.0x494si0sys0jo147h7mu0co3o8d"},"ctx":{"vw":"Sheet 1","wb":"New Workbook"}}
{"ts":"2021-11-11T05:30:35.289","pid":14692,"tid":"3cbc","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"end-ds.connect","l":{},"a":{"depth":3,"elapsed":0,"exclusive":0,"id":"JIvgnmTAkGpKqPEqq2gAt3","name":"ds.connect","res":{"alloc":{"e":1.79e+04,"i":3.05e+04,"ne":160,"ni":276,"peak":1.02e+04},"free":{"e":7.76e+03,"i":1.41e+04,"ne":95,"ni":161},"kcpu":{"e":0,"i":0},"ntid":1,"ucpu":{"e":0,"i":0}},"rk":"ok","root":"MM3c602Nk+KI8Zjhta3ZKR","rv":{},"sponsor":"JaL7SeBdk5YLOUus21SffS","type":"end"},"v":{"caption":"Untitled Data Source","class":"federated","ds":"Untitled Data Source","federated-connection":"\n \n","name":"federated.0x494si0sys0jo147h7mu0co3o8d"},"ctx":{"vw":"Sheet 1","wb":"New Workbook"}}
{"ts":"2021-11-11T05:30:35.294","pid":14692,"tid":"3cbc","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"begin-protocol.create-protocol","l":{},"a":{"depth":3,"id":"FcbykP0U0DWJQ+ViEWKO7P","name":"protocol.create-protocol","root":"MM3c602Nk+KI8Zjhta3ZKR","sponsor":"JaL7SeBdk5YLOUus21SffS","type":"begin"},"v":{"authentication":"auth-integrated","class":"exasol_jdbc","name":"exasol_jdbc.1xpv9ab12p8p6t1g1auci0rlf8dg","protocol-class":"exasol_jdbc","server":"exasoldb.example.com"},"ctx":{"vw":"Sheet 1","wb":"New Workbook"}}
{"ts":"2021-11-11T05:30:35.294","pid":14692,"tid":"3cbc","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"begin-protocol.create-protocol","l":{},"a":{"depth":4,"id":"JiKRQHJak8hIymhtO7yrwO","name":"protocol.create-protocol","root":"MM3c602Nk+KI8Zjhta3ZKR","sponsor":"FcbykP0U0DWJQ+ViEWKO7P","type":"begin"},"v":{"authentication":"auth-integrated","class":"jdbc","name":"exasol_jdbc.1xpv9ab12p8p6t1g1auci0rlf8dg","protocol-class":"jdbc","server":"exasoldb.example.com"},"ctx":{"vw":"Sheet 1","wb":"New Workbook"}}
{"ts":"2021-11-11T05:30:35.370","pid":14692,"tid":"2a08","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"msg","v":"JDBCProtocol: DBMS Server version: 7.1.2"}
{"ts":"2021-11-11T05:30:35.370","pid":14692,"tid":"2a08","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"msg","v":"JDBCProtocol: driver version: 7.1.2"}
{"ts":"2021-11-11T05:30:35.370","pid":14692,"tid":"2a08","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"construct-protocol","v":{"attributes":{":protocol-customizations":"",":subclass":"exasol_jdbc",":thread-session":"9","authentication":"auth-integrated","class":"jdbc","dbname":"","one-time-sql":"","password":"********","port":"8563","schema":"","server":"exasoldb.example.com","username":"","v-fingerprint":"ABD591342466880A16A4443DEEFF44A78A26E47514BE4D5E1C4CB712345F69CA","v-validateservercertificate":"1","workgroup-auth-mode":"as-is"},"created":"2021-11-11T05:30:35.294","created-elapsed":0.075,"disconnected":false,"id":9}}
{"ts":"2021-11-11T05:30:35.374","pid":14692,"tid":"3cbc","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"protocol-logsession","v":{"Class":"jdbc","Driver JDBC Version":"","Driver Name":"EXASolution JDBC Driver","Driver Version":"7.1.2","Protocol":"JDBCProtocol","Server Version":"7.1.2"}}
{"ts":"2021-11-11T05:30:35.374","pid":14692,"tid":"3cbc","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"protocol-added-to-group","v":{"group":{"attributes":{":protocol-customizations":"","authentication":"auth-integrated","class":"exasol_jdbc","dbname":"","one-time-sql":"","password":"********","port":"8563","schema":"","server":"exasoldb.example.com","site-luid":"d1103034-4811-496f-a275-d89aa6bf6766","username":"","v-fingerprint":"ABD591342466880A16A4443DEEFF44A78A26E47514BE4D5E1C4CB712345F69CA","v-validateservercertificate":"1","workgroup-auth-mode":""},"closed-protocols-count":"0","connection-limit":"16","group-id":"8","in-construction-count":"0","protocols-count":"1"},"protocol-id":"9"}}
{"ts":"2021-11-11T05:30:35.374","pid":14692,"tid":"3cbc","sev":"warn","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"connection-info-mismatch","v":{":connector-version":"(MISMATCH) 1.0.0 <=> ",":protocol-customizations":"",":thread-session":"(MISMATCH) 9 <=> ","authentication":"auth-integrated","class":"exasol_jdbc","dbname":"","one-time-sql":"","password":"********","port":"8563","schema":"","server":"exasoldb.example.com","site-luid":"(MISMATCH) <=> d1103034-4811-496f-a275-d89aa6bf","username":"","v-fingerprint":"ABD591342466880A16A4443DEEFF44A7","v-validateservercertificate":"1","workgroup-auth-mode":"(MISMATCH) as-is <=> "}}
{"ts":"2021-11-11T05:30:35.375","pid":14692,"tid":"3cb8","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"ec-load","v":{"cns":"MDTV7Z","elapsed-ms":"0","key-hash":"3281857889","key-size-b":"80","outcome":"miss"}}
{"ts":"2021-11-11T05:30:35.375","pid":14692,"tid":"3cbc","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"msg","v":"Metadata timestamp was not set, using Now() for m_metadataTimestamp)"}
{"ts":"2021-11-11T05:30:35.375","pid":14692,"tid":"66f0","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"ec-load","v":{"cns":"MDTV7Z","elapsed-ms":"0","key-hash":"3281857889","key-size-b":"80","outcome":"miss"}}
{"ts":"2021-11-11T05:30:35.376","pid":14692,"tid":"2204","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"ec-load","v":{"cns":"MDTV7Z","elapsed-ms":"0","key-hash":"3281857889","key-size-b":"80","outcome":"miss"}}
{"ts":"2021-11-11T05:30:35.376","pid":14692,"tid":"499c","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"ec-load","v":{"cns":"MDTV7Z","elapsed-ms":"0","key-hash":"3281857889","key-size-b":"80","outcome":"miss"}}
{"ts":"2021-11-11T05:30:35.376","pid":14692,"tid":"3cbc","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"msg","v":"Connected to exasol_jdbc version \"\", recognized as \"0\""}
{"ts":"2021-11-11T05:30:35.377","pid":14692,"tid":"3cbc","sev":"info","req":"YYyqe5y--Z62VFEJj6l_yQAAAgc","sess":"BB9AC00AC1CD48CE895882F81E50E5FF-1:0","site":"Default","user":"chris","k":"connect-using-keychain-selected-connection","v":{"xml":"
Regarding the user property: our JDBC logs all properties it recieves. When I successfully ran your test tool, the user was set to an object. I don't know which class, but the toString() output for this object was this:
user=Subject:
Principal: tableauuser@EXAMPLE.COM
Private Credential: Ticket (hex) = ****
Client Principal = tableauuser@EXAMPLE.COM
Server Principal = krbtgt/EXAMPLE.COM@EXAMPLE.COM
Session Key = EncryptionKey: keyType=18 keyBytes (hex dump)=****
Forwardable Ticket true
Forwarded Ticket false
Proxiable Ticket false
Proxy Ticket false
Postdated Ticket false
Renewable Ticket false
Initial Ticket true
Auth Time = Wed Nov 10 16:24:42 UTC 2021
Start Time = Wed Nov 10 16:24:42 UTC 2021
End Time = Thu Nov 11 02:24:42 UTC 2021
Renew Till = null
Client Addresses Null
Private Credential: [GSSCredential:
chris 1.2.840.113554.1.2.2 Initiate [class sun.security.jgss.krb5.Krb5ProxyCredential]
chris 1.3.6.1.5.5.2 Initiate [class sun.security.jgss.spnego.SpNegoCredElement]]This was only obvious difference between a successful impersonation and Tableau Server's behavior. I can also see that connectionProperties.js receives the following attributes:
attr[connectionHelper.attributeAuthentication] === 'auth-integrated'
attr[connectionHelper.attributeTableauServerAuthMode] === 'as-is'From what I understand, attr[connectionHelper.attributeTableauServerAuthMode] should be db-impersonate. That's why I assume that either our connector is not configured correctly so that Tableau does not activate authentication mode db-impersonate or I need to change some configuration in Tableau.
Thanks again for your very helpful constrained-delegation-jdbc test. It helped me identify issues with the Kerberos environment and I am sure it will also be helpful for our customers. Would it be OK for you if I add some tests to our project based on your code, naming you as the original author?
devanshsoni9
commented
2 years ago It seems to me that you probably don't need to add any of the properties which I see in the your connector. I see that the exasol jdbc driver does not need/support any of those properties, so I guess they are not serving any purpose. You can test this by removing properties from the constrained-delegation-jdbc .
So then the below code isn't needed. (You can keep the logging bit though)
if (authentication === 'auth-integrated') {
// if attributeTableauServerUser is non-empty, it means the connector plugin is currently being accessed in a Tableau Server environment
var serverUser = attr[connectionHelper.attributeTableauServerUser];
if (!isEmpty(serverUser)) {
log("Running on Server using integrated auth with user '" + serverUser + "'")
props["user"] = serverUser;
props["gsslib"] = "gssapi";
props["jaasLogin"] = "false";
} else {
log("Running on Desktop using integrated auth")
props["gsslib"] = "gssapi";
props["jaasLogin"] = "false";
props["jaasApplicationName"] = "com.sun.security.jgss.krb5.initiate";
}
} else {
log("Using non-integrated auth '" + authentication + "'")
}
The attr[connectionHelper.attributeTableauServerAuthMode] should be equal to kerberos-impersonate. Database impersonation on the other hand works a little differently than Kerberos Constrained Delegation and should not be confused with that.
I think if you can make sure, you are selecting "Viewer Credentials" option, while publishing the workbook, that will ensure that the correct authMode is being passed to the server backend. You will see this option only when you login on the Tableau Desktop using Kerberos("integrated-auth").
kaklakariada
commented
2 years ago You are right, I don't need these properties, I copied them from the example.
Do I understand you correctly that Tableau Server only uses impersonate when publishing a workbook from Tableau Desktop? I would have expected that this also works when creating a new workbook directly on the Server, because I am logged in via Kerberos/Active Directory and the Server already has my credentials.
devanshsoni9
commented
2 years ago Hi @kaklakariada
The feature around Supporting Impersonation mode for workbooks/datasources created directly on Tableau Server isn't supported at this time, but is being considered as a potential enhancement in the future.
Right now, a user logged into Tableau Desktop using Kerberos authentication, can publish workbook/datasource with the Viewer Credentials option to achieve the same purpose.
kaklakariada
commented
2 years ago Good morning @devanshsoni9 ,
Thank you very much for your help, I managed to get delegation working via Viewer Credentials. Is it OK for you if I add unit tests to our repo based on your constrained delegation test tool?
I also would like to add unit tests for the JavaScript files. To make the test more realistic I need to know which runtime Tableau Server/Desktop uses to execute the JS files, e.g. Node.js, Rhino, Nashorn or Graal.
Thank you very much for your reply!
devanshsoni9
commented
2 years ago Hi @kaklakariada It is fine if you add unit tests. The code of test tool is pretty generic delegation code and can be found on Oracle website as well so I think it should be fine. I will check about the JS engine and let you know.
kaklakariada
commented
2 years ago Hi @devanshsoni9 , here are our integration tests based on your tool: https://github.com/exasol/tableau-connector/tree/main/jdbc-kerberos-setup-test :) Do you have an update about the JS engine? I am using node.js for running the tests and it works fine: https://github.com/exasol/tableau-connector/pull/36
devanshsoni9
commented
2 years ago Hi @kaklakariada I cannot tell you what JS engine we use due to internal policy.
I am going to close this bug. Feel free to create another one if you have any further questions. Thanks
About You: Name: Christoph Pirkl Company: @exasol
Your question: We would like to enable Kerberos delegation with Tableau Server for the Exasol Tableau Connector.
I followed the instructions for Tableau Server under Windows, but Tableau keeps using the server's RunAs service account to login to the database. The instructions state that Kerberos delegation under Windows is only supported for Oracle and PostgreSQL.
My questions:
Thank you very much for your help!