[QUESTION]How does client certificate authentication work in JDBC and ODBC?

[Gong-Allen]

Question

I come from this document Client Certificate Authentication for Loopback Scenarios, after reading this article I still don't know how to make client certificate authentication work in JDBC and ODBC, here are my questions:

• The tile is "for Loopback Scenarios", does this mean client and SQL Server should be on the same machine? The article also mentioned that "This feature is only officially supported for loopback authentication scenarios against Linux SQL Server 2019 and up.", does it mean client certificate authentication can only work on Linux SQL Server 2019 and up? And SQL Server on windows doesn't support this?
• Is there any special requirement when generate the client certificate? I mean should I specify the db user name in the client certificate, if I should, which certificate attribute should be the da user name? CN?
• If I use a PFX format file which contains client certificate and private key, then the clientKeyPassword can be specified as the password of my PFX file?
• How to make the SQL Server trust the client certificate? Is there some setup from SQL Server side?
• Is there any user mapping setup from SQL Server side between client certificate and db user?
• Does SQL Server support JWT authentication in JDBC/ODBC?

Thanks.

[tkyc]

The tile is "for Loopback Scenarios", does this mean client and SQL Server should be on the same machine?

Yes, this is for linux environments where the client and server are on the same machine.

"This feature is only officially supported for loopback authentication scenarios against Linux SQL Server 2019 and up.", does it mean client certificate authentication can only work on Linux SQL Server 2019 and up? And SQL Server on windows doesn't support this?

That's also correct. On windows, WIndows Integrated Auth is used instead for these loopback scenarios.

Does SQL Server support JWT authentication in JDBC/ODBC?

No, for JDBC. I can't speak for ODBC.

If I use a PFX format file which contains client certificate and private key, then the clientKeyPassword can be specified as the password of my PFX file?

Yes, see below and refer to ClientCertificateAuthenticationTest.java in the project for more details.

Is there any special requirement when generate the client certificate? I mean should I specify the db user name in the client certificate, if I should, which certificate attribute should be the da user name? CN?

How to make the SQL Server trust the client certificate? Is there some setup from SQL Server side?

I don't believe there is any special requirements. And to be frank, I'm not a complete expert on this either since this was before my time and I'm not able to find any additional info on this internally other than that doc you linked.

But going off of our testing in com/microsoft/sqlserver/jdbc/clientcertauth/ClientCertificateAuthenticationTest.java. We do the following setup in order to run the tests in that test class:

1. Create private keys

echo "generate client.pem"
openssl x509 -inform der -in client.cer -out client.pem

echo "generate client-pkcs1.key"
openssl rsa -inform PVK -outform PEM -in client.pvk -out client-pkcs1.key -passin pass:<your-password>

echo "generate client-encrypted-pkcs1.key"
openssl rsa -aes256 -in client-pkcs1.key -out client-encrypted-pkcs1.key -passout pass:<your-password>

echo "generate client-pkcs8.key"
openssl pkcs8 -topk8 -inform PEM -outform PEM -nocrypt -in client-pkcs1.key -out client-pkcs8.key

echo "generate client-encrypted-pkcs8.key"
openssl rsa -aes256 -in client-pkcs8.key -out client-encrypted-pkcs8.key -passout pass:<your-password>

echo "generate client.pfx"
openssl pkcs12 -export -out client.pfx -inkey client-pkcs1.key -in client.pem -passout pass:

echo "generate client-encrypted.pfx"
openssl pkcs12 -export -out client-encrypted.pfx -inkey client-pkcs1.key -in client.pem -passout pass:<your-password>

echo "generate client.pvk"
openssl rsa -inform PVK -in client.pvk -text -passin pass:<your-password> -out client.pvk

How to make the SQL Server trust the client certificate? Is there some setup from SQL Server side?
Is there any user mapping setup from SQL Server side between client certificate and db user?

2. On SQL Server you'll need to create the certificate and create a login associated with that certificate.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<your-password>';
USE MASTER;
CREATE CERTIFICATE clientcert
   ENCRYPTION BY PASSWORD =  '<your-password>' 
   WITH SUBJECT = 'clientCertAuth',
   EXPIRY_DATE = '20991231';
GO
CREATE LOGIN clientCertAuth from certificate clientcert;
BACKUP CERTIFICATE clientcert TO FILE = '/tmp/client.cer' 
   WITH PRIVATE KEY  (FILE = '/tmp/client.pvk' , 
   ENCRYPTION BY PASSWORD =  '<your-password>' ,
   DECRYPTION BY PASSWORD =  '<your-password>'
);
GO
GRANT VIEW SERVER STATE TO clientCertAuth

[Gong-Allen]

Hi @tkyc
Thanks for your answer, it's very clear for me.
By the way, when you said "On windows, Windows Integrated Auth is used instead for these loopback scenarios", do you mean these authentication such as Kerberos, NTLM, etc.. ? Is there any plan to support JWT authentication or extend the client certificate authentication without the limitation of "Linux and Loopback"?

[tkyc]

"On windows, Windows Integrated Auth is used instead for these loopback scenarios", do you mean these authentication such as Kerberos, NTLM, etc.. ?

That's right.

Is there any plan to support JWT authentication or extend the client certificate authentication without the limitation of "Linux and Loopback"

No, no plans for that.