How to enable Always Encrypted feature of Microsoft SQL Server for Talend Administration Center - 7.3

author
Talend Documentation Team
EnrichVersion
7.3
EnrichProdName
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Real-Time Big Data Platform
task
Administration and Monitoring
Data Governance
Data Quality and Preparation
Deployment
Design and Development
Installation and Upgrade
EnrichPlatform
Talend Administration Center

How to enable Always Encrypted feature of Microsoft SQL Server for Talend Administration Center

Talend supports the Always Encrypted feature of Microsoft SQL Server. This document explains how to use this feature with Talend Administration Center.

For how to enable Always Encrypted on the SQL server side (using Microsoft SQL Management Studio), see https://channel9.msdn.com/events/DataDriven/SQLServer2016/AlwaysEncrypted.

Talend Administration Center supports Windows Certificate Store and Java Key Store keys providers. To enable decryption on Talend Administration Center side:

  1. If Talend Administration Center and the generated key (Windows Certificate Store or Java Key Store) are on the different machines, copy the key on the machine where Talend Administration Center will be running.
  2. Add the columnEncryptionSetting=Enabled property to connection URL. For JavaKeyStore, provide keyStoreAuthentication=JavaKeyStorePassword, also keyStoreLocation and keyStoreSecret. For example:
    jdbc:sqlserver://<ip>:<port>;databaseName=<name>;columnEncryptionSetting=Enabled; keyStoreAuthentication=JavaKeyStorePassword;
    keyStoreLocation=<path_to_the_keystore_file>;keyStoreSecret=<keystore_key_password>;
  3. The Always Encrypted feature requires the official Microsoft SQL Server driver (com.microsoft.sqlserver.jdbc.SQLServerDriver) version 6.0 and higher to work with the server.
    Warning: Official Microsoft driver don't work with database setup and for database migrations. Use JTDS variant (net.sourceforge.jtds.jdbc.Driver) for it.
  4. Download and install the Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files. Be sure to read the Readme included in the zip file for installation instructions and relevant details on possible export/import issues.
  5. For Windows Certificate Store, the driver has a dependency on the sqljdbc_auth.dll that is available in the Microsoft SQL Server driver package. To use this provider, copy the sqljdbc_auth.dll file to a directory on the Windows system path on the computer where the JDBC driver is installed. Alternatively you can set the java.library.path system property to specify the directory of the sqljdbc_auth.dll. If you are running a 32-bit Java Virtual Machine (JVM), use the sqljdbc_auth.dll file in the x86 folder, even if the operating system is the x64 version. If you are running a 64-bit JVM on a x64 processor, use the sqljdbc_auth.dll file in the x64 folder. For example, if you are using the 32-bit JVM and the JDBC driver is installed in the default directory, you can specify the location of the DLL by using the following virtual machine (VM) argument when the Java application is started (-Djava.library.path=C:\Microsoft JDBC Driver <version> for SQL Server\sqljdbc_<version>\enu\auth\x86) or add it to the C:/Windows/System32 directory.

Useful Links:

  1. What is Always Encrypted : https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2017
  2. Using Always Encrypted with the JDBC driver : https://docs.microsoft.com/en-us/sql/connect/jdbc/using-always-encrypted-with-the-jdbc-driver?view=sql-server-2017
  3. Always Encrypted API Reference for the JDBC Driver: https://docs.microsoft.com/en-us/sql/connect/jdbc/always-encrypted-api-reference-for-the-jdbc-driver?view=sql-server-2017