Creating an Information Services JDBC Data source with Kerberos delegated authentication to a Microsoft SQL Server database.

Creating an Information Services JDBC Data source with Kerberos delegated authentication to a Microsoft SQL Server database.

Product: TIBCO Spotfire Server

Version: 5.5 and later

OS: All Supported Operating Systems

--------------------

Description:

How to create an Information Services Data source with Kerberos delegated authentication to Microsoft SQL Server Database.

Resolution:

Prerequisites

1). TIBCO Spotfire Server configured for Kerberos authentication. Refer to the TIBCO Spotfire Server Installation manual (section "Kerberos Authentication").

2). TIBCO Spotfire Web Player (optional), configured for Single Sign-On (SSO) using Delegation with Kerberos authentication. Refer to the TIBCO Spotfire Web Player installation manual.

3). Constrained or Unconstrained delegation enabled on the domain controller for the Service Account used for Kerberos authentication on TIBCO Spotfire Server. Refer to the TIBCO Spotfire Server installation manual (section "Grant the Spotfire Server Service Account the Right to Delegate Client Credentials").

4). Microsoft SQL Server database to be used in setting up an information link data source that is configured for Kerberos authentication.

Instructions

1). Download the JDBC driver for Microsoft SQL Server from the following location and copy the sqljdbc_auth.dll file into the TIBCO Spotfire Server instance's tomcat\lib folder.

- http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774 .

The sqljdbc_auth.dll files are extracted into the following location:

\sqljdbc_\\auth\x64\

2). Create a new Database Template for Kerberos delegated Authentication to Microsoft SQL Server Database using the TIBCO Spotfire Server Configuration Tool. Define the JDBC data source template using the XML shown below. For detailed instructions, refer to knowledge base article KB 45013 on the TIBCO Support site.

-----------------------------------------------------------------------

( sqlserver2005_delegated com.microsoft.sqlserver.jdbc.SQLServerDriver jdbc:sqlserver://<host>:<port>;DatabaseName=<database>;integratedSecurity=true; authenticationScheme=JavaKerberos SELECT 1 spotfire.connection.pool.factory.data.source kerberos.data.source selectMethod cursor com.spotfire.ws.im.ds.sql.sqlserver.SQLServerMetadataProvider 10000 100 TABLE, VIEW true true true 1000 10000 false [$$catalog$$.][$$schema$$.]$$table$$ $$value$$ convert(varchar(8), $$value$$, 108) $$value$$ String VARCHAR($$value$$) String VARCHAR(8000) Integer DECIMAL(10, 0) Long DECIMAL(38, 0) Float REAL Double DOUBLE PRECISION Date DATETIME Time DATETIME DateTime DATETIME #$$name$$ CREATE TABLE $$name$$ $$column_list$$ DROP TABLE $$name$$ true )

-------------------------------------------------

3). Enable the template and Save the configuration into the database.

4). On the Spotfire Server, create a keytab file for the MS SQL Server database secured with Kerberos:

ktpass /princ {dbspnuser}@DOMAIN /ptype krb5_nt_principal /crypto rc4-hmac-nt /out spotfire-database.keytab /kvno 0 /pass ***

Replace dbspnuser - User used to register SPNs for the database

DOMAIN - your custom domain

5). Copy spotfire-database.keytab to \jdk\jre\lib\security folder.

6). Create spotfire-database.login file in \jdk\jre\lib\security\ containing the following text:

DatabaseKerberos { com.sun.security.auth.module.Krb5LoginModule required debug=true storeKey=true useKeyTab=true keyTab="${java.home}/jre/lib/security/spotfire-database.keytab" principal="{dbspnuser}@DOMAIN" }

Replace dbspnuser - User used to register SPNs for the SQL Server database

DOMAIN - your custom domain

7). Add an entry for the spotfire-database.login in the \jdk\jre\lib\security\java.security file:

login.config.url.1=file:${java.home}/jre/lib/security/spotfire-database.login

8). Run the Spotfire Server service with the service account used to register SPNs for the server machine.

9). Restart the TIBCO Spotfire Server service (as a Windows or Linux service), to make these changes take effect.

10). Login to Spotfire Server from the Spotfire desktop client, then select "Tools > Information Designer" from the main menu and create a new Data Source using the newly created template(sqlserver2005_delegated). Keep the username/password field blank.

11). If the data source connection fails, purge the kerberos tickets on the Spotfire client machine using this command:

klist purge

References:

- https://msdn.microsoft.com/en-us/library/ms378428(v=sql.110).aspx

- https://msdn.microsoft.com/en-us/library/gg558122(v=sql.110).aspx