Re: SSPI connection on a remote server : - Mailing list pgsql-jdbc
From | Dave Cramer |
---|---|
Subject | Re: SSPI connection on a remote server : |
Date | |
Msg-id | CADK3HHKziXD4GbEX=o8_amsPYdtqdEHNVCHBz4qEFV9+hBZqGA@mail.gmail.com Whole thread Raw |
In response to | SSPI connection on a remote server : ("LE MENTEC, SANDRINE" <sandrine.le-mentec@capgemini.com>) |
Responses |
RE: SSPI connection on a remote server :
|
List | pgsql-jdbc |
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,
pgsql-jdbc by date: