Re: SSPI connection on a remote server :

FATAL: authentication SSPI failed for the user « user »
is probably more relevant

Dave Cramer
www.postgres.rocks

On Thu, 29 Apr 2021 at 05:21, LE MENTEC, SANDRINE <
sandrine(dot)le-mentec(at)capgemini(dot)com> wrote:

> Thank you Dave for your answer.
>
>
>
> I have done some research about this error. It is known that it is caused
> by « connection reset by peer ». I have found some documentations (
> https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/troubleshoot-postgresql-an-existing-connection-was-forcibly/ba-p/925164)
> I am going to investigate about a network problem.
>
>
>
> But I still do not fully understand why the remote sspi connection works
> perfectly with the psql command line but does not work with the java + jdbc
> code. And I do not understand why it causes a java.lang.RuntimeException:
> NTDSAPI DsMakeSpn call failed with 87 (wrong parameter).
>
>
>
> Here I translated the Postgres Log in English (I forgot to do it in my
> last e-mail, sorry) :
>
> 2021-04-28 09:58:12.432 CEST [3028] LOG: Could not receive data from the
> client : unrecognized winsock error 10054
>
> 2021-04-28 09:58:12.434 CEST [3028] FATAL: authentication SSPI failed for
> the user « user »
>
> 2021-04-28 09:58:12.434 CEST [3028] DETAIL : Connection matched
> pg_hba.conf line 85 : « 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
>
>
>
> Thank you very much again,
>
>
>
> Regards,
>
>
>
> ____________________________________________________________________
>
> *Sandrine Le Mentec*
>
> Software Engineer
>
>
>
> *De :* Dave Cramer <davecramer(at)postgres(dot)rocks>
> *Envoyé :* mercredi 28 avril 2021 22:50
> *À :* LE MENTEC, SANDRINE
> *Cc :* pgsql-jdbc(at)lists(dot)postgresql(dot)org; KOUMIRA, Adnane; POULLOT, Cedric
> *Objet :* 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
> This message contains information that may be privileged or confidential
> and is the property of the Capgemini Group. It is intended only for the
> person to whom it is addressed. If you are not the intended recipient, you
> are not authorized to read, print, retain, copy, disseminate, distribute,
> or use this message or any part thereof. If you receive this message in
> error, please notify the sender immediately and delete all copies of this
> message.
>