[Bug] Active Directory (Authentication=ActiveDirectoryServicePrincipalCertificate). Cannot parse the PVK, PVK file does not contain the correct header..does it not support encryped keys?

[muskaan62]

Question

ActiveDirectoryServicePrincipalCertificate Authentication with client certifiicate private key and private key associated with password is not working.

String jdbcUrl = "jdbc:sqlserver://******;" +
                "database=*******;" +
                "encrypt=true;"+
                "trustServerCertificate=true;" +
                "authentication=ActiveDirectoryServicePrincipalCertificate;"+
                "user=*****;"+
                "clientCertificate= ******;"+
                "clientKey=******;"+
                "clientKeyPassword=*****";

I am trying to connect with azuresql db with the above url using (clientcert,private key,password) for authentication
Note(clientcert with private key is working)
client cert with password also working.
only the above combination is giving the below error..I have verified the private key as well it contains valid header

-----BEGIN ENCRYPTED PRIVATE KEY-----

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: Failed to authenticate the user xxxxxxxx in Active Directory (Authentication=ActiveDirectoryServicePrincipalCertificate). Cannot parse the PVK, PVK file does not contain the correct header.
	at com.microsoft.sqlserver.jdbc.SQLServerMSAL4JUtils.getCorrectedException(SQLServerMSAL4JUtils.java:488)
	at com.microsoft.sqlserver.jdbc.SQLServerMSAL4JUtils.getSqlFedAuthTokenPrincipalCertificate(SQLServerMSAL4JUtils.java:319)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.getFedAuthToken(SQLServerConnection.java:6089)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.onFedAuthInfo(SQLServerConnection.java:6012)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.processFedAuthInfo(SQLServerConnection.java:5846)
	at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onFedAuthInfo(tdsparser.java:346)
	at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:130)
	at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:42)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:6905)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:5451)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:5383)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7775)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4408)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3845)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:3402)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:3211)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1979)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1267)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:251)

tested with latest driver also 12.8.0.jre11
Is it a bug in driver?

use below steps to create certificate with private key and privatekey password

Generate a Private Key (with password):
openssl genpkey -algorithm RSA -aes256 -out private.key -pass pass:<your-password>
Create a Certificate Signing Request (CSR):
openssl req -new -key private.key -out request.csr -passin pass:<your-password>
Generate a Self-Signed Certificate:
openssl x509 -req -days 365 -in request.csr -signkey private.key -out certificate.crt -passin pass:<your-password>

Relevant Issues and Pull Requests

[lilgreenbird]

hi @muskaan62

I don't see the code you used to connect but the url you included above doesn't look correct. Please see this doc for an example on how to connect using ActiveDirectoryServicePrincipalCertificate authentication mode.

[muskaan62]

hi @muskaan62

I don't see the code you used to connect but the url you included above doesn't look correct. Please see this doc for an example on how to connect using ActiveDirectoryServicePrincipalCertificate authentication mode.

@lilgreenbird The code

try (Connection connection = DriverManager.getConnection(jdbcUrl);
             Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT* from TestTable")) {

            if (rs.next()) {
                System.out.println("Id: " + rs.getString(1));
                System.out.println("Name: " + rs.getString(2));
            }
        }

can u please mention whats the wrong in the url if it is trustServerCertificate and encrypt one then keeping both true also give same error...also the attach doc is not showing anything its pointing to github repo only.I am kinda block on this.

[muskaan62]

@lilgreenbird I think the bug is in this method https://github.com/microsoft/mssql-jdbc/blob/main/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerCertificateUtils.java#L397 which use to decrypt the encrypted private key .
I clone the driver code and replace the above method with the below logic
https://stackoverflow.com/questions/69027323/reading-pem-encoded-encrypted-private-key-from-file-in-java . its working as expected.
can u please fix this issue ASAP. as it is blocking our enhancement feature.

[lilgreenbird]

I'm sorry it looks like there was a c&p issue the link in my prev post was wrong, I've now fixed it. The example uses SQLServerDataSource but it's the same idea in URL form, you can see in the sample what properties is needed for ActiveDirectoryServicePrincipalCertificate auth.

We also have junit test which tests ActiveDirectoryServicePrincipalCertificate fyi.

[muskaan62]

@lilgreenbird I am using the correct properties in URL form. And the test you provide is only for testing certificate auth without any password.
let me tell you in details
As per this doc https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication?view=sql-server-ver16#connect-using-activedirectoryserviceprincipalcertificate-authentication-mode we can authenticate via ActiveDirectoryServicePrincipal certificate in this four ways.(jdbc url prop allowed https://learn.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-ver16 )

1. client certificate without password (the test you provide for this scenario) [working]
2. client certificate with password [working]
3. client certificate with private key [working]
4. client certificate with private key and key password (i.e means encrypted key) [bug]
   The above 3 ways are working fine but fourth is not ..so for that i clone the jdbc driver code in my machine and debug and observe the logic use for decrypting the private key for the fourth option is not correct so i replaced with another logic(gave details in prev commet) and try to authenticate and its working ..please check my previous comment. its a bug in the code..if URL has any issues it wouldn't have work for any option and when i replace the logic also it wouldnt have work for fourth option..but its working that means no issue with the URL.

[muskaan62]

@lilgreenbird I try to raise the PR for this issue but looks like i dont have perms to create PR.
though i have created a patch for the fix below.. I tried to upload patch file here for the fix somehow its not uploading so put that in the text file you rename it to .patch if you are considering the fix.
fixissue2530.txt

[lilgreenbird]

hi @muskaan62

Thanks, what errors are you getting whehn you tried to create a PR? this is open source so anyone should be able to create a PR and we've had contributions from various users in the past. You should be able to create a PR here

[muskaan62]

@lilgreenbird facing this error

I try to fork the repo and raise PR #2532