Re: SSPI connection on a remote server :

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@postgres.rocks>
Envoyé : mercredi 28 avril 2021 22:50
À : LE MENTEC, SANDRINE
Cc : pgsql-jdbc@lists.postgresql.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.le-mentec@capgemini.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.