Communication closed during authentication, Connection reset by peer, SQL state 28000

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Communication closed during authentication, Connection reset by peer, SQL state 28000

marco ardito
Hi, I'm new here!

I'm developing a (php) web application on a linux server which uses unixodbc, working very well since months, but since a few days it happens, very rarely, an error which is reported by the framework which, apart file/row backtrace info, looks like this:

----------------------------
odbc_pconnect(): SQL error: [unixODBC]Communication closed during authentication; Connection reset by peer., SQL state 28000 in SQLConnect
----------------------------

Then, a simple page reload (with post parameters submitted by a form) gets a normal result, no errors whatsoever. And maybe any other application page that loads data from the back-end db works fine... until... for some reason, it could happen again.

For you to understand better the setup, the back-end db is jboss teiid, a data integration middleware based on java which, apart usual jdbc, offer also an odbc interface through postgres server emulation, so unixodbc is using postgres driver connecting to teiid.

The complete flow is:
application => php framework => php => unixodbc => network (LAN) => teiid => network (LAN) => back-end data sources (a virtual database composed by several mysql, sqlserver, xls files)

I don't know what is the cause of this error, I don't know which log can help me to understand why it happens, in order to prevent it, and  its intermittent and rare behaviour, makes this issue impossible (up to now) to reproduce, unfortunately. I probably could force php to "try again", when the error happens, as it seems to work from the browser, but I would prefer to understand what's happening...

I have complete access to the whole platform so I can check everything and perform actions if needed but... I need some help...

I've also  found this page about the sql state 28000  http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#28000

But still can't understand what it could be, what to check, how to trace/debug... any hint welcome...

--
Marco

------------------- [Ai sensi e per gli effetti della Legge sulla tutela della privacy (L. 196/2003), questa mail è destinata unicamente alle persone sopra indicate e le informazioni in essa contenute sono da considerarsi strettamente riservate. E' proibito leggere, copiare, usare o diffondere il contenuto della presente mail senza autorizzazione. Se avete ricevuto questo messaggio per errore, siete pregati di rispedire la stessa al mittente. Grazie]

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: Communication closed during authentication, Connection reset by peer, SQL state 28000

Nick Gorham-2
On 24/01/17 17:19, marco ardito wrote:
Hi, I'm new here!

I'm developing a (php) web application on a linux server which uses unixodbc, working very well since months, but since a few days it happens, very rarely, an error which is reported by the framework which, apart file/row backtrace info, looks like this:

----------------------------
odbc_pconnect(): SQL error: [unixODBC]Communication closed during authentication; Connection reset by peer., SQL state 28000 in SQLConnect
----------------------------

Then, a simple page reload (with post parameters submitted by a form) gets a normal result, no errors whatsoever. And maybe any other application page that loads data from the back-end db works fine... until... for some reason, it could happen again.

For you to understand better the setup, the back-end db is jboss teiid, a data integration middleware based on java which, apart usual jdbc, offer also an odbc interface through postgres server emulation, so unixodbc is using postgres driver connecting to teiid.

The complete flow is:
application => php framework => php => unixodbc => network (LAN) => teiid => network (LAN) => back-end data sources (a virtual database composed by several mysql, sqlserver, xls files)

I don't know what is the cause of this error, I don't know which log can help me to understand why it happens, in order to prevent it, and  its intermittent and rare behaviour, makes this issue impossible (up to now) to reproduce, unfortunately. I probably could force php to "try again", when the error happens, as it seems to work from the browser, but I would prefer to understand what's happening...

I have complete access to the whole platform so I can check everything and perform actions if needed but... I need some help...

I've also  found this page about the sql state 28000  http://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#28000

But still can't understand what it could be, what to check, how to trace/debug... any hint welcome...

--
Marco

Hi,

That error will have come from a particular driver you are using, I would look at what logging that driver offers.

--
Nick

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: Communication closed during authentication, Connection reset by peer, SQL state 28000

Edouard Gaulué
In reply to this post by marco ardito
Hi,

In my case, I've observed those kind of unpredictable error after moving
from SQL2008 to SQL2012.

Any change on the SQL Server side?

Regards, Edouard
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: Communication closed during authentication, Connection reset by peer, SQL state 28000

Daniel Kasak
I'm not a heavy user, but I've done a little testing, here and there,
against most of the freebie versions of SQL Server, and I've never had
any of these types of issues that weren't a result of incorrect SQL
Server configuration ... eg check in SSMS that you have all the client
connectivity things working ... that it's listening on an IP on the
LAN, etc. I've done some testing against Microsoft's SQL Server in
Docker thing, and I can connect fine to that too. You could try
turning on ODBC tracing, examining SQL Server logs, etc. Good luck :)

Dan

On Thu, Jan 26, 2017 at 4:54 AM, Edouard Gaulué <[hidden email]> wrote:

> Hi,
>
> In my case, I've observed those kind of unpredictable error after moving
> from SQL2008 to SQL2012.
>
> Any change on the SQL Server side?
>
> Regards, Edouard
> _______________________________________________
> unixODBC-support mailing list
> [hidden email]
> http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: Communication closed during authentication, Connection reset by peer, SQL state 28000

marco ardito
Thanks all for replying, and infos.
From what you all wrote, it seems that the cause of the error can only be a connection to a Microsoft SQL server, isn't it?

If it is so, I could restrict the field of investigation... my virtual database on teiid server has two different MSSQL servers behind, with a slightly different network path, too. I could try disabling one of them, temporarily. And maybe the really short lifespan of the issue (a simple retry then works) should point towards a transient network issue... instead of a dirver or configuration issue.

I was in doubt about what subsystem was reporting the error (I thought to ask here because it was labelled by "[unixodbc]") because, as I said before, on my web server unixodbc uses only postgresql driver towards teiid (it is the only "db" unixodbc sees, and connects to), which in turn has several jdbc connections towards different databases on different web servers (two MSSQL, one Mysql server) and also other resources.

If it is so, if it is really due to a connection towards a MSSQL server, it should be a problem better "visible" from Teiid, and its logs, since it's doing the real connection to those remote MSSQL servers... and unixodbc is only reporting the remote error transparently.

I will soon contact teiid support team about this, but any further consideration you could add about the above will help me to better understand this matter, quite new to me...

Thanks a lot.

Il 26/01/2017 02:27, Daniel Kasak ha scritto:
I'm not a heavy user, but I've done a little testing, here and there,
against most of the freebie versions of SQL Server, ... You could try
turning on ODBC tracing, examining SQL Server logs, etc. Good luck :)

Dan

On Thu, Jan 26, 2017 at 4:54 AM, Edouard Gaulué [hidden email] wrote:
Hi,

In my case, I've observed those kind of unpredictable error after moving
from SQL2008 to SQL2012.

Any change on the SQL Server side?

--
Marco

------------------- [Ai sensi e per gli effetti della Legge sulla tutela della privacy (L. 196/2003), questa mail � destinata unicamente alle persone sopra indicate e le informazioni in essa contenute sono da considerarsi strettamente riservate. E' proibito leggere, copiare, usare o diffondere il contenuto della presente mail senza autorizzazione. Se avete ricevuto questo messaggio per errore, siete pregati di rispedire la stessa al mittente. Grazie]

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: Communication closed during authentication, Connection reset by peer, SQL state 28000

marco ardito
update: it doesn't seems to be related to MSSQL, apparently...

- I created a copy of the teiid virtual database, excluding any MSSQL server from its models
- changed the web server config to load, through unixodbc, the new virtual database
- it happens again... same message: " [unixODBC]Communication closed during authentication; Connection reset by peer., SQL state 28000 in SQLConnect"

I looked for unixodbc logs, and found that this error is reported in many psqlodbc*.log files (I guess this name comes from the postgres driver usage)

And this is how it appears there (there are many files like this): there's a first connection try that fails with the error, then another one that succedes... (no timings, though...?)

can you spot any interesting info here?

=================
DSN info: DSN='dsnname',server='x.y.z.w',port='xxxxx',dbase='vdbname',user='user',passwd='xxxxx'
          onlyread='No',protocol='7.4',showoid='No',fakeoidindex='No',showsystable='No'
          conn_settings='', conn_encoding='(null)'
          translation_dll='',translation_option=''
conn = 0x8058d3c8, PGAPI_Connect(DSN='dsnname', UID='user', PWD='xxxxx')
Driver Version='09.02.0100,201306020000'
Global Options: fetch=10000, socket=4096, unknown_sizes=0, max_varchar_size=255, max_longvarchar_size=8190
                disable_optimizer=0, ksqo=0, unique_index=1, use_declarefetch=0
                text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64
                extra_systable_prefixes='dd_;', conn_settings='' conn_encoding=''
CONN ERROR: func=original_CC_connect, desc='', errnum=210, errmsg='Communication closed during authentication'
            ------------------------------------------------------------
            henv=0x8058c2b8, conn=0x8058d3c8, status=0, num_stmts=16
            sock=0x8058b160, stmts=0x80589280, lobj_type=-999
            ---------------- Socket Info -------------------------------
            socket=38, reverse=0, errornumber=10, errormsg='Connection reset by peer.'
            buffer_in=2153300704, buffer_out=2153316120
            buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0
CONN ERROR: func=PGAPI_Connect, desc='Error on CC_connect', errnum=210, errmsg='Communication closed during authentication'
            ------------------------------------------------------------
            henv=0x8058c2b8, conn=0x8058d3c8, status=0, num_stmts=16
            sock=0x8058b160, stmts=0x80589280, lobj_type=-999
            ---------------- Socket Info -------------------------------
            socket=38, reverse=0, errornumber=10, errormsg='Connection reset by peer.'
            buffer_in=2153300704, buffer_out=2153316120
            buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0
DSN info: DSN='dsnname',server='x.y.z.w',port='xxxxx',dbase='vdbname',user='user',passwd='xxxxx'
          onlyread='No',protocol='7.4',showoid='No',fakeoidindex='No',showsystable='No'
          conn_settings='', conn_encoding='(null)'
          translation_dll='',translation_option=''
conn = 0x8058d9c8, PGAPI_Connect(DSN='dsnname', UID='user', PWD='xxxxx')
Driver Version='09.02.0100,201306020000'
Global Options: fetch=10000, socket=4096, unknown_sizes=0, max_varchar_size=255, max_longvarchar_size=8190
                disable_optimizer=0, ksqo=0, unique_index=1, use_declarefetch=0
                text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64
                extra_systable_prefixes='dd_;', conn_settings='' conn_encoding=''
    [ PostgreSQL version string = '8.1.4' ]
    [ PostgreSQL version number = '8.1' ]
conn=0x8058d9c8, query='select oid, typbasetype from pg_type where typname = 'lo''
    [ fetched 1 rows ]
    [ Large Object oid = 14939 ]
    [ Client encoding = 'UTF8' (code = 6) ]
conn=0x8058d9c8, PGAPI_Disconnect
=================

Il 27/01/2017 09:24, marco ardito ha scritto:
Thanks all for replying, and infos.
From what you all wrote, it seems that the cause of the error can only be a connection to a Microsoft SQL server, isn't it?

If it is so, I could restrict the field of investigation... my virtual database on teiid server has two different MSSQL servers behind, with a slightly different network path, too. I could try disabling one of them, temporarily. And maybe the really short lifespan of the issue (a simple retry then works) should point towards a transient network issue... instead of a dirver or configuration issue.

I was in doubt about what subsystem was reporting the error (I thought to ask here because it was labelled by "[unixodbc]") because, as I said before, on my web server unixodbc uses only postgresql driver towards teiid (it is the only "db" unixodbc sees, and connects to), which in turn has several jdbc connections towards different databases on different web servers (two MSSQL, one Mysql server) and also other resources.

If it is so, if it is really due to a connection towards a MSSQL server, it should be a problem better "visible" from Teiid, and its logs, since it's doing the real connection to those remote MSSQL servers... and unixodbc is only reporting the remote error transparently.

I will soon contact teiid support team about this, but any further consideration you could add about the above will help me to better understand this matter, quite new to me...

Thanks a lot.

Il 26/01/2017 02:27, Daniel Kasak ha scritto:
I'm not a heavy user, but I've done a little testing, here and there,
against most of the freebie versions of SQL Server, ... You could try
turning on ODBC tracing, examining SQL Server logs, etc. Good luck :)

Dan

On Thu, Jan 26, 2017 at 4:54 AM, Edouard Gaulué [hidden email] wrote:
Hi,

In my case, I've observed those kind of unpredictable error after moving
from SQL2008 to SQL2012.

Any change on the SQL Server side?

--
Marco


--
Marco Ardito
Responsabile Servizi Informatici

Api Formazione s.c.r.l.
Via Pianezza 123, 10151 - TORINO
Tel: +39-0114513123
Fax: +39-0114551150 - +39-0114515075

------------------- [Ai sensi e per gli effetti della Legge sulla tutela della privacy (L. 196/2003), questa mail � destinata unicamente alle persone sopra indicate e le informazioni in essa contenute sono da considerarsi strettamente riservate. E' proibito leggere, copiare, usare o diffondere il contenuto della presente mail senza autorizzazione. Se avete ricevuto questo messaggio per errore, siete pregati di rispedire la stessa al mittente. Grazie]

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: Communication closed during authentication, Connection reset by peer, SQL state 28000

Daniel Kasak
If the Postgres driver is logging these errors, then you're attempting
to connect to SQL Server with the wrong driver. Post details of your
ODBC configuration.

Dan

On Fri, Jan 27, 2017 at 11:56 PM, marco ardito <[hidden email]> wrote:

> update: it doesn't seems to be related to MSSQL, apparently...
>
> - I created a copy of the teiid virtual database, excluding any MSSQL server
> from its models
> - changed the web server config to load, through unixodbc, the new virtual
> database
> - it happens again... same message: " [unixODBC]Communication closed during
> authentication; Connection reset by peer., SQL state 28000 in SQLConnect"
>
> I looked for unixodbc logs, and found that this error is reported in many
> psqlodbc*.log files (I guess this name comes from the postgres driver usage)
>
> And this is how it appears there (there are many files like this): there's a
> first connection try that fails with the error, then another one that
> succedes... (no timings, though...?)
>
> can you spot any interesting info here?
>
> =================
> DSN info:
> DSN='dsnname',server='x.y.z.w',port='xxxxx',dbase='vdbname',user='user',passwd='xxxxx'
>
> onlyread='No',protocol='7.4',showoid='No',fakeoidindex='No',showsystable='No'
>           conn_settings='', conn_encoding='(null)'
>           translation_dll='',translation_option=''
> conn = 0x8058d3c8, PGAPI_Connect(DSN='dsnname', UID='user', PWD='xxxxx')
> Driver Version='09.02.0100,201306020000'
> Global Options: fetch=10000, socket=4096, unknown_sizes=0,
> max_varchar_size=255, max_longvarchar_size=8190
>                 disable_optimizer=0, ksqo=0, unique_index=1,
> use_declarefetch=0
>                 text_as_longvarchar=1, unknowns_as_longvarchar=0,
> bools_as_char=1 NAMEDATALEN=64
>                 extra_systable_prefixes='dd_;', conn_settings=''
> conn_encoding=''
> CONN ERROR: func=original_CC_connect, desc='', errnum=210,
> errmsg='Communication closed during authentication'
>             ------------------------------------------------------------
>             henv=0x8058c2b8, conn=0x8058d3c8, status=0, num_stmts=16
>             sock=0x8058b160, stmts=0x80589280, lobj_type=-999
>             ---------------- Socket Info -------------------------------
>             socket=38, reverse=0, errornumber=10, errormsg='Connection reset
> by peer.'
>             buffer_in=2153300704, buffer_out=2153316120
>             buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0
> CONN ERROR: func=PGAPI_Connect, desc='Error on CC_connect', errnum=210,
> errmsg='Communication closed during authentication'
>             ------------------------------------------------------------
>             henv=0x8058c2b8, conn=0x8058d3c8, status=0, num_stmts=16
>             sock=0x8058b160, stmts=0x80589280, lobj_type=-999
>             ---------------- Socket Info -------------------------------
>             socket=38, reverse=0, errornumber=10, errormsg='Connection reset
> by peer.'
>             buffer_in=2153300704, buffer_out=2153316120
>             buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0
> DSN info:
> DSN='dsnname',server='x.y.z.w',port='xxxxx',dbase='vdbname',user='user',passwd='xxxxx'
>
> onlyread='No',protocol='7.4',showoid='No',fakeoidindex='No',showsystable='No'
>           conn_settings='', conn_encoding='(null)'
>           translation_dll='',translation_option=''
> conn = 0x8058d9c8, PGAPI_Connect(DSN='dsnname', UID='user', PWD='xxxxx')
> Driver Version='09.02.0100,201306020000'
> Global Options: fetch=10000, socket=4096, unknown_sizes=0,
> max_varchar_size=255, max_longvarchar_size=8190
>                 disable_optimizer=0, ksqo=0, unique_index=1,
> use_declarefetch=0
>                 text_as_longvarchar=1, unknowns_as_longvarchar=0,
> bools_as_char=1 NAMEDATALEN=64
>                 extra_systable_prefixes='dd_;', conn_settings=''
> conn_encoding=''
>     [ PostgreSQL version string = '8.1.4' ]
>     [ PostgreSQL version number = '8.1' ]
> conn=0x8058d9c8, query='select oid, typbasetype from pg_type where typname =
> 'lo''
>     [ fetched 1 rows ]
>     [ Large Object oid = 14939 ]
>     [ Client encoding = 'UTF8' (code = 6) ]
> conn=0x8058d9c8, PGAPI_Disconnect
> =================
>
> Il 27/01/2017 09:24, marco ardito ha scritto:
>
> Thanks all for replying, and infos.
> From what you all wrote, it seems that the cause of the error can only be a
> connection to a Microsoft SQL server, isn't it?
>
> If it is so, I could restrict the field of investigation... my virtual
> database on teiid server has two different MSSQL servers behind, with a
> slightly different network path, too. I could try disabling one of them,
> temporarily. And maybe the really short lifespan of the issue (a simple
> retry then works) should point towards a transient network issue... instead
> of a dirver or configuration issue.
>
> I was in doubt about what subsystem was reporting the error (I thought to
> ask here because it was labelled by "[unixodbc]") because, as I said before,
> on my web server unixodbc uses only postgresql driver towards teiid (it is
> the only "db" unixodbc sees, and connects to), which in turn has several
> jdbc connections towards different databases on different web servers (two
> MSSQL, one Mysql server) and also other resources.
>
> If it is so, if it is really due to a connection towards a MSSQL server, it
> should be a problem better "visible" from Teiid, and its logs, since it's
> doing the real connection to those remote MSSQL servers... and unixodbc is
> only reporting the remote error transparently.
>
> I will soon contact teiid support team about this, but any further
> consideration you could add about the above will help me to better
> understand this matter, quite new to me...
>
> Thanks a lot.
>
> Il 26/01/2017 02:27, Daniel Kasak ha scritto:
>
> I'm not a heavy user, but I've done a little testing, here and there,
> against most of the freebie versions of SQL Server, ... You could try
> turning on ODBC tracing, examining SQL Server logs, etc. Good luck :)
>
> Dan
>
> On Thu, Jan 26, 2017 at 4:54 AM, Edouard Gaulué <[hidden email]> wrote:
>
> Hi,
>
> In my case, I've observed those kind of unpredictable error after moving
> from SQL2008 to SQL2012.
>
> Any change on the SQL Server side?
>
>
> --
> Marco
>
>
>
> --
> Marco Ardito
> Responsabile Servizi Informatici
>
> Api Formazione s.c.r.l.
> Via Pianezza 123, 10151 - TORINO
> Tel: +39-0114513123
> Fax: +39-0114551150 - +39-0114515075
> Mail : [hidden email]
> Web : http://www.apiform.to.it
>
> ------------------- [Ai sensi e per gli effetti della Legge sulla tutela
> della privacy (L. 196/2003), questa mail è destinata unicamente alle persone
> sopra indicate e le informazioni in essa contenute sono da considerarsi
> strettamente riservate. E' proibito leggere, copiare, usare o diffondere il
> contenuto della presente mail senza autorizzazione. Se avete ricevuto questo
> messaggio per errore, siete pregati di rispedire la stessa al mittente.
> Grazie]
>
>
> _______________________________________________
> unixODBC-support mailing list
> [hidden email]
> http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
>
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|

Re: Communication closed during authentication, Connection reset by peer, SQL state 28000

marco ardito
Il 30/01/2017 01:05, Daniel Kasak ha scritto:
If the Postgres driver is logging these errors, then you're attempting
to connect to SQL Server with the wrong driver. Post details of your
ODBC configuration.

Dan

No, Dan :) it isn't so. Maybe I just didn't stress it enough before but:

the unixodbc back-end db is jboss teiid, a data integration middleware based on java which offers an **odbc interface through postgres server emulation**, so unixodbc is using postgresql driver connecting to teiid.

Teiid offers, as a postgresql database over odbc, what is a "virtual" aggregation of many different data sources, in a very flexible way, and also with high performances. There's a clear and informative image on the project home page: at http://teiid.jboss.org/

The complete flow is:
my application <=> php framework <=> php< => unixodbc <=> network (LAN) <=> teiid => network (LAN) <=> (several data sources)

So, unixodbc uses the postgresql driver to connect to the "postgresql server" emulated by teiid, which is seen as a "big" postgresql database.

But this "big" database is really virtual and can have a number of "real" databases (and nearly any other source of data), as Mysql and MSSQL servers.

I was in doubt about which "virtual" or "real" database was reporting the error in unixodbc. A few comments seemed to point out that the SQL 28000 error was a MSSQL error. But even when I removed all MSSQL "real" servers from the "virtual" server, the error came out the same. SO, it's not that.

In the meanwhile there seems to be a trace on the teiid side, something related to some java network component, still to be confirmed, may be the cause. so I'm trying to get info on that side.

I'll report any meaningful progress here...
Thanks,

--
Marco

------------------- [Ai sensi e per gli effetti della Legge sulla tutela della privacy (L. 196/2003), questa mail � destinata unicamente alle persone sopra indicate e le informazioni in essa contenute sono da considerarsi strettamente riservate. E' proibito leggere, copiare, usare o diffondere il contenuto della presente mail senza autorizzazione. Se avete ricevuto questo messaggio per errore, siete pregati di rispedire la stessa al mittente. Grazie]

_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support