Skip to content

Latest commit

 

History

History
99 lines (82 loc) · 4.64 KB

1-CreateKey.md

File metadata and controls

99 lines (82 loc) · 4.64 KB

Certificate Key Generation for SQL Always Encrypted generic provider

This certificate will be used by the .NET Driver and JDBC Driver using different Key Store.

Points of the script:

  • FriendlyName is mandatory for the JavaKeyStore provider.
  • Exportable is mandatory: you must distribute this certificate to all clients requiring access to encrypted columns.
  • Set the password for your exported certificate.

Certificate compatibility issue

warning This setup used Windows Server 2016 to generate the certificate. Nevertheless it seems there is an issue in Java to read the certificate generated by this operating system version (at least in the PKCS#12 format (.pfx file). I used the Oracle JDK 1.8.0_181 and the JDBC driver was unable to read properly the certificate in the JavaKeyStore. This is documented in following bug reports : These bugs are resolved in the JDK 11 which is not yet publicly available (early access build JDK 11. So until general availability you should generate the certificate with:
  • A Windows Server 2012 R2 : nevertheless the New-SelfSignedCertificate cmdlet doesn't present all options (especially the -FriendlyName) so it is not suitable.
  • A real PKI infrastructure (Windows Server 2012 R2 or below), to create a certificate request with appropriate settings (especially the FriendlyName).
  • KeyTool.exe available in the Java JDK. This is finally this option that worked for both the .NET Driver and the JDBC Driver.

KeyTool

Using keytool.exe (included with JDK) to generate the certificate :

c:\Program Files\Java\jdk1.8.0_181\bin>keytool -genkeypair -keyalg RSA -alias CLINIC_CMK_GENERIC -keystore C:\Temp\CLINIC_CMK_GENERIC.pfx -storepass P@ssw0rd -validity 7200 -keysize 4096 -storetype pkcs12 -keypass P@ssw0rd

Output with answers to generate the certificate subject :

What is your first and last name?
  [Unknown]:  CLINIC_CMK_GENERIC
What is the name of your organizational unit?
  [Unknown]:  CLINIC
What is the name of your organization?
  [Unknown]:  CLINIC
What is the name of your City or Locality?
  [Unknown]:  BOSTON
What is the name of your State or Province?
  [Unknown]:  IL
What is the two-letter country code for this unit?
  [Unknown]:  US
Is CN=CLINIC_CMK_GENERIC, OU=CLINIC, O=CLINIC, L=BOSTON, ST=IL, C=US correct?
  [no]:  yes

Import the certificate in the Windows Certificate store for both the .NET Client and Security administrator system. Provide the certificate to the JDBC client as file. Do not provide the certificate to DBA administrator. All this roles are described in details on next steps :

$pwd = ConvertTo-SecureString -String "P@ssw0rd" -AsPlainText -Force
Import-PfxCertificate -FilePath C:\Temp\CLINIC_CMK_GENERIC.pfx -Exportable -CertStoreLocation Cert:\CurrentUser\My -Password $pwd

PowerShell

With the issue described above, Java clients using a certificate generated with a Windows Server 2016 operating system will not be able to decrypt properly encrypted columns. So this script is just for information purpose until JDK 11 availability.

Windows Server 2006 Certificate generation :

# Create a new self signed certificate
# FriendlyName is for the JavaKeyStore
$cert = New-SelfSignedCertificate -CertStoreLocation Cert:\CurrentUser\My `
            -KeyAlgorithm RSA  `
            -KeyDescription 'SQL Server Always Encrypted CLINIC CMK' `
            -KeyExportPolicy Exportable `
            -KeyLength 4096 `
            -KeySpec KeyExchange `
            -KeyUsage DataEncipherment `
            -KeyUsageProperty All  `
            -NotAfter ([DateTime]::now.AddYears(20)) `
            -NotBefore $([DateTime]::Now.AddDays(-1)) `
            -Subject 'CLINIC_CMK_GENERIC' `
            -Type DocumentEncryptionCert `
            -Provider 'Microsoft Strong Cryptographic Provider' `
            -FriendlyName 'CLINIC_CMK_GENERIC'


#export the certificate in a file
$pwd = ConvertTo-SecureString  "P@ssw0rd" -AsPlainText -Force
Export-PfxCertificate -Cert Cert:\CurrentUser\my\$($cert.Thumbprint) -FilePath "C:\Temp\CLINIC_CMK_GENERIC.pfx" -Password $pwd