Eugenio Tampieri's blog

Keycloak with Microsoft SQL Server 2016

It should work out of the box, but it didn’t (for me).

It complained about the server certificate (java.security.cert.CertPathValidatorException: Algorithm constraints check failed on signature algorithm: SHA1withRSA).

This is how I fixed it:

1. I created a new self-signed certificate for the host (https://www.sqlserver-dba.com/2022/06/how-to-generate-a-self-signed-certificate-for-sql-server-with-new-selfsignedcertificate.html):

   New-SelfSignedCertificate `
     -Type SSLServerAuthentication `
     -Subject "CN=$env:COMPUTERNAME" `
     -FriendlyName "SQL Server Test self-signed" `
     -DnsName "$env:COMPUTERNAME",'localhost.'  `
     -KeyAlgorithm RSA -KeyLength 2048 -Hash 'SHA256' `
     -TextExtension '2.5.29.37={text}1.3.6.1.5.5.7.3.1' `
     -NotAfter (Get-Date).AddMonths(240) `
     -KeySpec KeyExchange `
     -Provider "Microsoft RSA SChannel Cryptographic Provider" `
     -CertStoreLocation "Cert:\LocalMachine\My"
   

2. Opened mmc.exe, added certificates (local computer) snap-in and copied it to the trusted root authorities
3. Right clicked the certificate > All tasks > Open > Details
4. Copied the thumbprint (no spaces)
5. Opened regedit
6. Pasted into Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MicrosoftSQLServer\MSSQL13.GE\MSSQLServer\SuperSocketNetLib (https://learn.microsoft.com/en-US/sql/database-engine/configure-windows/configure-sql-server-encryption?view=sql-server-ver16)
7. Restarted Microsoft SQL Server
8. Modified keycloak.conf: db-url=jdbc:sqlserver://sqlsrv;databaseName=<keycloakDB>;user=<keycloakUser>;password=s3cr3t;authentication=SqlPassword;trustServerCertificate=true;