NTLM Authentication to connect to SQL Server about vertx-sql-client

[petarov]

That's also the reason I'm currently not using the vertx-mssql-client: 99% of my customers use Windows Server and they mostly prefer named instances with Windows authentication as opposed to classic user/pwd SQL Server authentication.

I'm using the mssql-jdbc driver with blocking calls and it's doing quite alright so far. One idea that I have, is to possibly switch to a dedicated worker verticle with a ThreadingModel.VIRTUAL_THREAD threading model. I wonder if that'd yield any benefits.

In my case, I'm using the mssql-jdbc_auth-<version>-<arch>.dll that provides the required Kerberos auth routines, however and as described by Microsoft [1], it should be possible to use the Krb5LoginModule [2] as well, and I believe this could be in fact the best possible variant to add Windows authentication support to the vertx-mssql-client.

I hope I can get some time to look for a way to help adding this in the future. It'd be an interesting project.

1 - https://learn.microsoft.com/en-us/sql/connect/jdbc/using-kerberos-integrated-authentication-to-connect-to-sql-server
2 - https://docs.oracle.com/javase/8/docs/jre/api/security/jaas/spec/com/sun/security/auth/module/Krb5LoginModule.html

[mswatosh]

verify how the contribution can be tested

As someone who has dealt with Kerberos and SQLServer, last I tried, SQLServer requires an Active Directory environment when using Kerberos. This makes it difficult to test with TestContainers, since even if the SQLServer instance is on Linux, the AD domain controller needs to run on windows. It may be possible to get it working with Samba's AD domain controller, but I couldn't justify the time investment to get it working.

[git4rputuval]

Can you let us know a date that we can communicate to our clients?

[tsegismont]

I'm afraid there is no planned date, even though we'd like to have this feature. We have neither the knowledge of the protocol nor the resources required to test its implementation.

If you're willing to contribute it, I would be glad to help.

Otherwise, I can suggest some alternatives: switching to vertx-jdbc-client, or given you're a Hibernate user, switching to a blocking persistence layer.

[git4rputuval]

I'm afraid there is no planned date, even though we'd like to have this feature. We have neither the knowledge of the protocol nor the resources required to test its implementation.

If you're willing to contribute it, I would be glad to help.

Otherwise, I can suggest some alternatives: switching to vertx-jdbc-client, or given you're a Hibernate user, switching to a blocking persistence layer.

Hi,

We are creating a project that contains a sample representative of our application. Once that is done, it will be shared shortly. Please let us know if we are making any mistake in creating a blocking persistence layer.

In the meantime, do let us know how to contribute. We will discuss with my development team and work out our timelines to see if we are able to understand, contribute and deliver to our clients at the earliest.

Kind regards,

[git4rputuval]

I'm using the mssql-jdbc driver with blocking calls and it's doing quite alright so far.
...
In my case, I'm using the mssql-jdbc_auth--.dll that provides the required Kerberos auth routines

Can you provide the code and/steps to implement the Kerberos auth routines?

[git4rputuval]

We are attaching a demo project which demonstrates our application architecture to a small extent to reproduce the errors we're getting. This project attachment is giving a runtime error whereas in our actual application its a compilation error, we are attaching both errors as snapshots, we expect that if either of the error is fixed the other would also be solved.

Below is the demo application
persistenceUnit_Error.zip

the compilation error in our actual application is

the runtime error wrt this demo application is

this is runtime error when we disable reactive datasource using quarkus.datasource.reactive=false in our application.properties file

The solutions we used by far involved using @PersistenceUnit to attach our entity/model to a specific data source, made two different entities one to map if persistence is done using jdbc and depending on the flag value set in application.properties persistence through our data service layer would either happen from jdbc persistence side (jdbcEntity,jdbcService,jdbcRepo classes) else the usual/preffered reactive side.

[git4rputuval]

I'm afraid there is no planned date, even though we'd like to have this feature. We have neither the knowledge of the protocol nor the resources required to test its implementation.

If you're willing to contribute it, I would be glad to help.

Otherwise, I can suggest some alternatives: switching to vertx-jdbc-client, or given you're a Hibernate user, switching to a blocking persistence layer.

Hi @tsegismont ,

Please provide the blocking persistence layer option. Let me evaluate this option.

Thanks & Regards,

[git4rputuval]

@tsegismont

Please provide the details on how to contribute. We would like to evaluate it with our existing timelines.

Kind Regards,

[tsegismont]

@git4rputuval the firsts steps would be to:

• clarify which auth scheme(s) you plan to contribute (I'm a bit lost between NTLM, Kerberos ...)
• verify how the contribution can be tested (we need to be able to test it to support it)

[git4rputuval]

@git4rputuval the firsts steps would be to:

• clarify which auth scheme(s) you plan to contribute (I'm a bit lost between NTLM, Kerberos ...)
• verify how the contribution can be tested (we need to be able to test it to support it)

Hi,

I wish to contribute to NTLM in reactive extension (To be specific in GitHub, refer quarkus-2.7\extensions\hibernate-reactive).

To test, you need a domain-based Windows login system (Laptop or Desktop or VM). This is not possible in my development environment because we do not have domain-based windows login in our company.

What we do/plan to do is

• Deploy the application in the client UAT environment which employs domain-based windows login.
• Add a domain user in the SQL Server with "windows-authentication" activated.
• Configure the "reactive-url" in the application properties to include "domain name" and "integratedSecurity=true"
• Login into the client UAT environment using the domain user created in step 2
• Start the application

The application should start without any issues. The application should cater to data requests without any issues.