Re: SSPI connection on a remote server :

On Wed, 28 Apr 2021 at 11:12, LE MENTEC, SANDRINE <
sandrine(dot)le-mentec(at)capgemini(dot)com> wrote:

> 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,
>
>

I would say if it works locally and doesn't work remotely there is some
sort of difference between the servers.

As for the error "unrecognized winsock error 10054" seems to be more
relevant.

Dave

>