SSPI connection on a remote server :

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

SSPI connection on a remote server :

LE MENTEC, SANDRINE

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 :

 

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();

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.

 

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.

stacktrace_sspi_connection_remote_windows_server.txt (4K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SSPI connection on a remote server :

Dave Cramer-7


On Wed, 28 Apr 2021 at 11:12, LE MENTEC, SANDRINE <[hidden email]> 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 :

 

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();

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
Reply | Threaded
Open this post in threaded view
|

RE: SSPI connection on a remote server :

LE MENTEC, SANDRINE

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 <[hidden email]>
Envoyé : mercredi 28 avril 2021 22:50
À : LE MENTEC, SANDRINE
Cc : [hidden email]; KOUMIRA, Adnane; POULLOT, Cedric
Objet : Re: SSPI connection on a remote server :

 

 

 

On Wed, 28 Apr 2021 at 11:12, LE MENTEC, SANDRINE <[hidden email]> 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 :

 

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();

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.
Reply | Threaded
Open this post in threaded view
|

Re: SSPI connection on a remote server :

Dave Cramer-7
 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 <[hidden email]> 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 <[hidden email]>
Envoyé : mercredi 28 avril 2021 22:50
À : LE MENTEC, SANDRINE
Cc : [hidden email]; KOUMIRA, Adnane; POULLOT, Cedric
Objet : Re: SSPI connection on a remote server :

 

 

 

On Wed, 28 Apr 2021 at 11:12, LE MENTEC, SANDRINE <[hidden email]> 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 :

 

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();

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.
Reply | Threaded
Open this post in threaded view
|

RE: SSPI connection on a remote server :

LE MENTEC, SANDRINE

Hello Dave,

 

Thank you again for your answer.

 

To clear up any misunderstanding, the user in the log line is a login windows.

I should have said : « 2021-04-28 09:58:12.434 CEST [3028] FATAL:  authentication SSPI failed for the user « <userloginwindows>».

This userloginwindows exists as a user in postgres, the AD and is the user Windows trying to connect to the database.

Sorry for the misunderstanding.

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.

 

De : Dave Cramer <[hidden email]>
Envoyé : jeudi 29 avril 2021 11:32
À : LE MENTEC, SANDRINE
Cc : [hidden email]; KOUMIRA, Adnane; POULLOT, Cedric
Objet : Re: SSPI connection on a remote server :

 

 FATAL:  authentication SSPI failed for the user « user »

is probably more relevant

 

Dave Cramer

 

 

On Thu, 29 Apr 2021 at 05:21, LE MENTEC, SANDRINE <[hidden email]> 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 <[hidden email]>
Envoyé : mercredi 28 avril 2021 22:50
À : LE MENTEC, SANDRINE
Cc : [hidden email]; KOUMIRA, Adnane; POULLOT, Cedric
Objet : Re: SSPI connection on a remote server :

 

 

 

On Wed, 28 Apr 2021 at 11:12, LE MENTEC, SANDRINE <[hidden email]> 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 :

 

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();

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.