How to use Windows Authentication with a generic database extractor (Java) against Microsoft SQL Server? |
Caution: It is possible that this integration fails with the update to TrueSight Capacity Optimization 20.02 when Kerberos multiple realms are used because of a change in OpenJDK 11.06 in the 20.02 version. TrueSight Capacity Optimization 11.5.1 was using OpenJDK 11.0.5. Introduction It is not possible to use Windows Authentication with the Microsoft SQL Server JDBC Driver version 4.2 is used which is included in the product until TrueSight Capacity Optimization 20.02. This driver has to removed from BCO_HOME$/etl/lib , the file name is sqljdbc-4.2.jar. Windows Authentication with newer features are supported from version 6.2,. There was no problem observed with Microsoft JDBC Driver version 7 - 9, recommended is to use a JDBC Driver matching the latest Microsoft SQL Server version used in-house. Please refer to this Microsoft Page to get an understanding of the versions and supported SQL Server Version. https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix It is important to note that in recent Microsoft SQL Server JDBC driver versions, a separate driver is distributed for different OpenJDK version, there is a separate driver for OpenJDK8, OpenJDK11 and OpenJDK12. The Java version is different on TrueSight Capacity Optimization. For example, TSCO version 11.5.00 is using OpenJDK8, where as 11.5.01 and 20.02 are using OpenJDK11. When the OpenJDK version is changing the relevant driver must be used by TSCO, for the JDBC Driver 7.4.1 version the following files are related.
For on TrueSight Capacity Optimization 11.5.01 and 20.02 use mssql-jdbc-7.4.1.jre11.jar from that list. Similiar Jar files are available for the other JDBC Driver version. This approach is using Kerberos Protocol but logins with Windows Authentication to the SQL Server. Per default an MS SQL Server does allow the login over Kerberos, but Windows policies may prevent that. It is also required that the MS SQL Server instance is registered as a ServicePrincipalName in Active Directory. Testing It is recommended to test this approach in non-production first. If different MS SQL Server versions are used on several ETLs, it is possible that the SQL Server version is not supported by the JDBC Driver. Relation version support can be found on the Microsoft Webpage listed above. This document refers to the latest version which support all MS SQL Server version higher as MS SQL Server 2008R2 Product Updates When doing product updates, it is possible that the sqljdbc-4.2.jar file is restored on the BCO_HOME$/etl/lib directory it is important that this file is deleted when using a different version. If OpenJDK is updated with a new TrueSight version that related JDBC driver must be used for example with OpenJDK12 use mssql-jdbc-7.4.1.jre12.jar instead of mssql-jdbc-7.4.1.jre11.jar It is very important to know that only 1 JDBC Driver is using, using multiple versions may cause problems when loading the driver. How to Test On mft.bmc.com Patches>SQL Server JDBC test program there is testconnectionparams.jar Jar file which could be used to test the connection to the database is working outside the ETL, it has Microsoft SQL Server JDBC Driver 9. included, and should be run on the cpit OS user on the EE Server. Run the tool from command line in this way: /opt/bmc/BCO/jre/bin/java -jar testconnectionparams.jar "jdbc:sqlserver://hostname.domain.com:1433;integratedSecurity=true;authenticationScheme=JavaKerberos;encrypt=true;trustServerCertificate=true" "user@DOMAIN.COM" "userpassword" Replace: - hostname.domain.com:1433 with the Server FQDN and Port of the SQL Server - user@DOMAIN.COM with the PricincipalName of the account, it is that format and case must match - userpassword with the password of the database user. If the account is able to connect, the tool shows this messages, the line in bold is to highlight. May 18, 2021 12:04:14 PM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL WARNING: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum. Authentication Scheme: KERBEROS Driver name: Microsoft JDBC Driver 9.2 for SQL Server Driver version: 9.2.1.0 Product name: Microsoft SQL Server Product version: 11.00.5058 If this tool is generatiung an error the messages more below help to explain what are the potential reason. If login happen with an unexpected user, check if the Linux OS and OS user starting the ETL is integrated into Kerberos. Check with ls /etc/krb5.ini for the OS level and ls ~/*krb5* for signs for Kerberos integration. If the OS user is integrated into Kerberos for example for mount point authentication, the user defined in the ETL or on command line is ignored, the OS user is used for the authentication. How to implement
UserPrincipalName this is a login in email format, it is very important to write the domain part in HIGH capital, if defining in low capital the login will not work, error message and reasons does cover that. Define user@DOMAIN.COM instead of user@domain.com as the log user. JDBC driver: set it to : com.microsoft.sqlserver.jdbc.SQLServerDriver JDBC URL: Change host.domain.com to the FDN of the MS SQL Server , change :1433 to the Port of the Instance, change TEST to the name of the Database. Do NOT change the last part of the URL: integratedSecurity=true;authenticationScheme=JavaKerberos The ; semicolons are separators. How to get? and errors How to get the UserPrincipalName? Ask a Windows Domain Admin, if a box with RSAT tools is available the following command can be run, replace username with the name of the account. dsquery user -name <username> | dsget user -upn How to verify if the ServicePrincipalName for the MS SQL Server does exist? setspn -L <ServerServerHostName> A MS SQL Server ServicePrinciplicalName is registered in Active Directory in the following format. MSSQLSvc/hostname.domain.com That SPN is registered per default, but in MS SQL Server in a Cluster it is possible that manual registration on all nodes is required. How to verify the OpenJDK version used by the product? From the $BCO_HOME installation directory run: jre/bin/java -version [cpit@hostname BCO]$ jre/bin/java -version openjdk version "11.0.3" 2019-04-16 OpenJDK Runtime Environment AdoptOpenJDK (build 11.0.3+7) OpenJDK 64-Bit Server VM AdoptOpenJDK (build 11.0.3+7, mixed mode) The User name in the log is not the same as defined in the ETL and login fails. If the name in the log is like the Unix user which starts the services, the Unix user account is configured for Kerberos. The ETL should authenticate with the user in the ETL via Kerberos and not with the Unix user, to avoid that append useTicketCache=false to the JDBC driver URL. integratedSecurity=true;authenticationScheme=JavaKerberos;useTicketCache=false How to enable TLS connections Append encrypt=true;trustServerCertificate=true to the JDBC URL integratedSecurity=true;authenticationScheme=JavaKerberos;useTicketCache=falseM encrypt=true;trustServerCertificate=true UserName is defined with low capital domain part? It must be HIGH. But this error can also happen with wrong user and password combination. Please ensure that the proper UserPrincipalName is used, with high capital domain part. BCO_ETL_FAIL104: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot login with Kerberos principal user@domain.com, check your credentials. Kerberos Login failed: Integrated authentication failed. ClientConnectionId:51157449-0ec7-46e3-a54a-41040493f4ae due to javax.security.auth.login.LoginException (Message stream modified (41)) If the error happens when using TrueSight Capacity Optimization 20.02 it possible that a kerberos child account is used like: user@CHILD.DOMAIN.COM This is because of a change in Open JDK 11.0.6 and higher, to fix it. - Navigate to TSCO installation folder i.e. cd /opt/bmc/BCO - create or edit a file named customenv.sh - run chmod +x customenv.sh - Add ETL_JAVA_OPTS="$ETL_JAVA_OPTS -Dsun.security.krb5.disableReferrals=true" A MS SQL Server JDBC Driver is used which does not support the authentication type BCO_ETL_ERR011: Detected an abnormal ETL termination. Reason: com.microsoft.sqlserver.jdbc.SQLServerException: Integrated authentication failed. ClientConnectionId:301ec159-317b-4859-9c98-86dee908d12a Caused by: javax.security.auth.login.LoginException: Unable to obtain Principal Name for authentication This error does happen with sqljdbc-4.2.jar in in BCO_HOME$/etl/lib, a more recent driver is required. Wrong defined JDBC Driver is must be: com.microsoft.sqlserver.jdbc.SQLServerDriver BCO_ETL_FAIL108: Process cannot be started (cannot fork external java/os process) or process fails due to severe errors (no modules in classpath, ClassNotFoundException, not enough memory available on EE machine, etc.). Reason: java.lang.ClassNotFoundException: com.microsoft.sqlserver The JDBC Driver used does not match the OpenJDK version BCO_ETL_ERR011: Detected an abnormal ETL termination. Reason: java.lang.UnsupportedClassVersionError: com/microsoft/sqlserver/jdbc/SQLServerDriver has been compiled by a more recent version of the Java Runtime (class file version 56.0), this version of the Java Runtime only recognizes class file versions up to 55.0 The Server FDQN used has not registered as a SPN BCO_ETL_FAIL104: com.microsoft.sqlserver.jdbc.SQLServerException: No valid credentials provided (Mechanism level: Server not found in Kerberos database (7) Please use nslookup and setspn -L command to debug the issue, probably an DNS Alias Hostname is used for which no registered ServicePrincipalName is found in Active Directory. Use lookup to run against the use hostname used in the ETL and a nslookup against the IP address. >nslookup sqlserver.domain.com Server: realhostname.domain.com Address: 10.133.185.33 Aliases: sqlserver.domain.com From the output above use realhostname.domain.com instead of sqlserver.domain.com If this is a Cluster or Alias a Service Principal Name is required in Active Directory. The setspn -L command shows registration for realhostname but not for sqlserver. The Active Directory Admins should register this system as a Host Service Principal Name. The setpn -L command should show at least a Host record >setspn -L realhostname HOST/ realhostname.domain.com TERMSRV/ realhostname.domain.com MSSQLSvc/realhostname.domain.com:1433 MSSQLSvc/realhostname.domain.com The Service Principal Name and User Principal Name must be in synch BCO_ETL_ERR011: Detected an abnormal ETL termination. Reason: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot login with Kerberos principal user@SUB.DOMAIN.COM check your credentials. Kerberos javax.security.auth.login.LoginException (null (68)) The name of the User and the FQDN must be in the same domain, if the SQL Server SP is server.domain.com. The UserPrinciplarName must be from the same domain, a user from a child domain is not supported. Change the User to user@DOMAIN.COM instead of user@SUB.DOMAIN.COM. Even if the User Principal Name is returned as : SUB.DOMAIN.COM it is not possible to use account with a different UPN. Typically, there is a default UPN which could match the domain part for the FQDN/SPN. Old Kerberos configuration with weak hash used com.microsoft.sqlserver.jdbc.SQLServerException: Integrated authentication failed. Caused by: GSSException: Defective token detected (Mechanism level: AP_REP token id does not match!) This was observed in an environment which was running RHEL6, the OS User was integrated fully into Kerberos. In that case the user defined on the database connection is ignored. The connection to the database was to a SQL Server 2017, which has disabled old hash used by authentication. Hash rc4-hmac is not supported by SQL Server 2017 and newer. When connecting to SQL Server 2017 and higher it is recommended to use RHEL7 as the OS which is supporting more mordern has and security authentication algorithm. OS user is running a different Ream as database user FAILED BCO_ETL_FAIL104: com.microsoft.sqlserver.jdbc.SQLServerException: Integrated authentication failed Caused by: GSSException: No valid credentials provided (Mechanism level: Fail to create credential. (63) - No Service creds) Caused by: KrbException: Fail to create credential. (63) - No service creds This error was observed when the OS user which runs the ETL is integrated with Kerberos and different REALMS are configured in /etc/krb5.conf, To solve this error on the OS user which starts the ETL generate a Kerberos ticket with kinit User@DOMAIN.COM This user should be the database user which is used on the connection string, the password is requested for the user, and when the password is changing this commands needs to rerun. Additional Documentation which can help Microsoft JDBC Driver for SQL Server support matrix This gives a good overview of what MS SQL Server version is supported by which JDBC Driver version, it provides also an URL to the Downloads. Using Kerberos integrated authentication to connect to SQL Server Explains the Kerberos integration in Detail, for this solution ins test lab it was only required to use the steps described above, it was not required to create Kerberos login and configuration files. This approach is required when running native OS commands or mount point over Kerberos. MS SQL Server JDBC - Connecting with encryption More detailed information about enable TLS encryption in JDBC. https://cr.openjdk.java.net/~jjg/8186684/api.02/com/sun/security/auth/module/Krb5LoginModule.html Kerberos Java - Krb5Logion Module, the Micosoft JDBC Driver can work with more options which are listed here. ========================================================================================================================================================================= An RFE 'DRCOZ-23694 : Update MS SQL Server JDBC Driver to never version' has been logged for the issue and the fix will be available in Helix optimize 20.08 . Current driver is supported until August 2020. |