SSPI connection on a remote server :

Dear community,

I am working on connecting a postgres 12.5 database on a windows server to a java application with sspi (pg-jdbc version 42.2.18).

I followed these steps for a jdbc client and postgres database 12.5 on the same windows 10 computer :

• I modified the pg_hba.conf to enable sspi connection.
• I followed the documentation (https://wiki.postgresql.org/wiki/Configuring_for_single_sign-on_using_SSPI_on_Windows).
• I tried to connect with sspi with psql on my database and it worked perfectly.
• I made the following Junit test (like this example : https://stackoverflow.com/questions/45815520/how-to-connect-with-java-to-postgresql-9-3-using-sspi and following the documentation : https://jdbc.postgresql.org/documentation/head/connect.html) and it worked perfectly too.

final String url = "jdbc:postgresql://<dbclientname>:5432/postgres?gsslib=sspi";

final Connection conn = DriverManager.getConnection(url);

System.out.println(conn.isValid(15));

conn.close();

Then, I have done the same approach for a remote database on a windows 2016 server. I also opened the 5432 port on the server and check that the client and the server computer are in the same Active Directory.

• When I tried to connect on my remote server from my client with a psql command line the sspi protocole worked perfectly.
• But, when I have tried this Junit test, I have an org.postgresql.util.PSQLException (full Stacktrace attached):

final String url = "jdbc:postgresql://<dbclientname>:5432/postgres?gsslib=sspi";

     final Connection conn = DriverManager.getConnection(url);

     System.out.println(conn.isValid(15));

     conn.close();

• In the stacktrace, it said «  Caused by: java.lang.RuntimeException: NTDSAPI DsMakeSpn call failed with 87 ». I have check the meaning of the 87 error code (here :  https://github.com/pgjdbc/pgjdbc/blob/82510ed6da8733659f5a2560f029e3cded7b1d66/pgjdbc/src/main/java/org/postgresql/sspi/NTDSAPI.java) it only said that int ERROR_INVALID_PARAMETER = 87.
• I also checked the postgres log and it indicate a winsock error 10054 (connection reset by peer) :

2021-04-28 09:58:12.432 CEST [3028] LOG:  n'a pas pu recevoir les données du client : unrecognized winsock error 10054

2021-04-28 09:58:12.434 CEST [3028] FATAL:  authentification SSPI échouée pour l'utilisateur « user »

2021-04-28 09:58:12.434 CEST [3028] DÉTAIL:  La connexion correspond à la ligne 85 du pg_hba.conf : « host    all             all             <clientname>/32        sspi include_realm=0 »

2021-04-28 09:58:12.435 CEST [3028] LOG:  could not send data to client: unrecognized winsock error 10054

I don’t understand what I am missing. I have read some setsdn, kerberos and postgres documentation, but I am not sure what I should do to make it work for the remote server. I am very perturbed by the fact that it works with a psql command line but not with the jdbc.

I aslo contact your email-list because in the stacktrace it is said « to make a report about this error»  (« Veuillez faire un rapport sur cette erreur » in French).

Do you think it is because I followed some 9.x documentation and my database is a 12.5 ? If so, do you know where I can find an example ? I have read some of the e-mail list archive, but I did not find something revelant.

Do I need to make some more settings in my java code or in windows or in my database ? I am not an expert in kerberos and in AD, so I can have missed something.

Thank you very much for your help.

Regards,

____________________________________________________________________

Sandrine Le Mentec

Software Engineer | CSD

Capgemini France | 147 Issy les Moulineaux

www.capgemini.com

____________________________________________________________________

Connect with Capgemini:

Please consider the environment and do not print this email unless absolutely necessary.

Capgemini encourages environmental awareness.