Ошибка аутентификации SSPI соединения PostgreSQL ODBC

#postgresql #vb6 #odbc #sspi

Вопрос:

Недавно я внес некоторые изменения в существующее приложение, которое я поддерживаю для нашей компании, чтобы попытаться улучшить некоторые функциональные возможности. Однако я столкнулся с проблемой, и я не могу найти ничего полезного во всех своих поисковых запросах Google. Вот платформа:

Приложение VB6, работающее в Windows 7 (64-разрядная версия), использует соединение ADODB через ODBC с сервером PostgreSQL (v. 9.1.1).

Проблема в том, что по какой-то причине кажется, что метод аутентификации SSPI, который я использую, не может получить значение из последовательности в базе данных PostgreSQL. Другое (VB.NET ) приложения без проблем подключаются к одному и тому же серверу базы данных с использованием аутентификации SSPI (с использованием Npgsql).

Вот основная строка подключения, используемая для подключения ODBC в приложении VB6:

 DRIVER={PostgreSQL ANSI};Server=<SERVERNAMEHERE>;Port=<####>;Database=<DATABASENAMEHERE>;Uid=<USERNAMEHERE>

Когда я проверяю журналы сервера PostgreSQL после попытки установить соединение с базой данных, я нахожу следующую ошибку:

 CDT FATAL:  SSPI authentication failed for user "<USERNAMEHERE>"
CDT LOG:  could not send data to client: No connection could be made because the target machine actively refused it.

Я попытался подключиться к базе данных как этот пользователь с другого компьютера (вошедший в систему со своими учетными данными) через pgAdmin III, и я могу подключиться просто отлично. Я на самом деле попробовал то же приложение с этого второго компьютера (также 64-разрядная версия Windows 7), и, похоже, оно не выдает мне никаких ошибок в соединении.

Конечно, когда я запускаю код через среду IDE VB6 на своей виртуальной машине WinXP (под моими собственными учетными данными), кажется, что все работает точно так, как ожидалось. Я проверил, что учетная запись пользователя Active Directory активна и разблокирована, а в базе данных PostgreSQL создан правильный пользователь с правильным членством в групповой роли и т. Д. Итак, очевидно, я думаю, что должно быть что-то не так с драйвером ODBC, установленным для PostgreSQL. Я установил 64-разрядный драйвер PostgreSQL ODBC, и (конечно) это не помогло. Я удалил и переустановил 32-разрядный драйвер (поскольку он работает через 32-разрядное приложение), но никаких изменений.

Вот код VB6, который я использую, чтобы попытаться подключиться к базе данных:

 Private Function GetPGCertNumber() As Long
    On Error GoTo PGSQLError

    Dim PGDB As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim CertNum As Long
    Dim SQLStr As String

    Set PGDB = New ADODB.Connection
    PGDB.ConnectionString = PGSQL_CONNECTION_STR
    PGDB.CursorLocation = adUseServer
    PGDB.Open

    SQLStr = "SELECT nextval('certificatenumber_seq')"
    Set RS = New ADODB.Recordset
    RS.CursorLocation = adUseServer
    RS.Open SQLStr, PGDB

    If Not RS.EOF And Not RS.BOF Then
        CertNum = RS!nextval
    Else
        CertNum = -1
    End If

    GetPGCertNumber = CertNum
    RS.Close
    Set RS = Nothing
    PGDB.Close
    Set PGDB = Nothing
    Exit Function
PGSQLError:
    Dim ErrorMsg As String

    GetPGCertNumber = -1
    ErrorMsg = "An error occurred retrieving the next certificate number to use." amp; vbCrLf amp; _
               "Connection String: " amp; PGSQL_CONNECTION_STR amp; vbCrLf amp; _
               "Command String: " amp; SQLStr amp; vbCrLf amp; _
               "Database State: " amp; PGDB.State amp; vbCrLf amp; vbCrLf
    MsgBox ErrorMsg, vbCritical   vbOKOnly, "POSTGRESQL DATABASE ERROR"

    On Error Resume Next

    If Not PGDB Is Nothing Then
        If PGDB.State <> 0 Then
            PGDB.Close
        End If

        Set PGDB = Nothing
    End If
End Function

I’ve checked the sequence in the PostgreSQL database, and the sequence is not getting incremented. The error message that appears indicates that the SQLStr variable hasn’t even been populated yet, which tells me that it’s occurring when the Open method is being called.

I’ve come up with a «Band-Aid» workaround for the time being: I’ve created a new, limited access user on the PostgreSQL server that uses an MD5 login (specified in the pg_hba.conf) instead of SSPI, and hard-coded this in my connection string ( PGSQL_CONNECTION_STR ). From what I can tell so far, this appears to be working correctly, which leads me further to believe that the issue is specifically related to using SSPI with the ODBC connector for PostgreSQL in this environment.

I’m basically wondering at this point if someone might know of a specific issue with using the PostgreSQL ODBC driver to authenticate a connection to a PostgreSQL database via SSPI. I would much prefer to not have to hard-code credentials into the application, even if the credentials are for a limited-access user. I’ve tried everything I know to look at, although I’ve probably failed to mention it above, so please let me know if you need any clarification. Thanks in advance for your help.

EDIT: The only thing I have not tried yet is installing the 64-bit ODBC driver and changing my connection string to use that instead. The reason I haven’t tried this is that we still have some users that haven’t yet been upgraded to a 64-bit OS. If, perhaps, I can find a way to determine whether the user is on a 32-bit or 64-bit OS through VB6, this might be a viable alternative (assuming the 64-bit ODBC driver actually works correctly).

EDIT — debugging log details (redacted):

[mylog_3380.log]

 [3336-0.000]calling getDSNdefaults
[3336-0.015]CC_connect: entering...
[3336-0.015]sslmode=disable
[3336-0.015]original_CC_connect: entering...
[3336-0.015]Driver Version='09.03.0300,201405140001' linking 1600 dynamic Multithread library
[3336-0.031]original_CC_connect: DSN = '', server = '<SERVERNAMEHERE>', port = '####', database = '<DATABASENAMEHERE>', username = '<USERNAMEHERE>', password=''
[3336-0.031]connecting to the server socket...
[3336-0.047](0)(null) ERRNO=0
[3336-0.047]connection to the server socket succeeded.
[3336-0.047]!!! usrname=<USERNAMEHERE> server=<SERVERNAMEHERE>
[3336-0.047]sizeof startup packet = 80
[3336-0.047]sent the authentication block successfully.
[3336-0.047]gonna do authentication
[3336-0.047]read -1, global_socket_buffersize=4096
[3336-0.047]Lasterror=10035
[3336-0.062]read 9, global_socket_buffersize=4096
[3336-0.062]auth got 'R'
[3336-0.062]areq = 9 salt=0000000000
[3336-0.062]in AUTH_REQ_SSPI
[3336-47.767][[SQLAllocHandle]][3336-47.767]PGAPI_AllocConnect: entering...
[3336-47.767]**** PGAPI_AllocConnect: henv = 02B413B8, conn = 02B481C0
[3336-47.767]EN_add_connection: self = 02B413B8, conn = 02B481C0
[3336-47.767]       added at i=1, conn->henv = 02B413B8, conns[i]->henv = 02B413B8
[3336-47.767][SQLGetInfo(30)][3336-47.767]PGAPI_GetInfo: entering...fInfoType=77
[3336-47.767]PGAPI_GetInfo: p='03.50', len=0, value=0, cbMax=12
[3336-47.767][[SQLSetConnectAttr]] 103
[3336-47.767]PGAPI_SetConnectAttr for 02B481C0: 103 0000000F
[3336-47.767]PGAPI_SetConnectOption: entering fOption = 103 vParam = 15
[3336-47.767][SQLDriverConnect][3336-47.767]PGAPI_DriverConnect: entering...
[3336-47.767]**** PGAPI_DriverConnect: fDriverCompletion=0, connStrIn='DRIVER={PostgreSQL ANSI};UID=<USERNAMEHERE>;Server=<SERVERNAMEHERE>;Port=####;Database=<DATABASENAMEHERE>;debug=1;commlog=1;'
[3336-47.767]CC_conninfo_init opt=2
[3336-47.767]copy_globals driver=PostgreSQL ANSI socket_buffersize=4096
[3336-47.767]our_connect_string = 'DRIVER={PostgreSQL ANSI};UID=<USERNAMEHERE>;Server=<SERVERNAMEHERE>;Port=####;Database=<DATABASENAMEHERE>;debug=1;commlog=1;'
[3336-47.767]attribute = 'DRIVER', value = '{PostgreSQL ANSI}'
[3336-47.783]copyAttributes: DSN='',server='',dbase='',user='',passwd='',port='',onlyread='',protocol='',conn_settings='(null)',disallow_premature=-1)
[3336-47.783]attribute = 'UID', value = '<USERNAMEHERE>'
[3336-47.783]copyAttributes: DSN='',server='',dbase='',user='<USERNAMEHERE>',passwd='',port='',onlyread='',protocol='',conn_settings='(null)',disallow_premature=-1)
[3336-47.783]attribute = 'Server', value = '<SERVERNAMEHERE>'
[3336-47.783]copyAttributes: DSN='',server='<SERVERNAMEHERE>',dbase='',user='<USERNAMEHERE>',passwd='',port='',onlyread='',protocol='',conn_settings='(null)',disallow_premature=-1)
[3336-47.783]attribute = 'Port', value = '####'
[3336-47.783]copyAttributes: DSN='',server='<SERVERNAMEHERE>',dbase='',user='<USERNAMEHERE>',passwd='',port='####',onlyread='',protocol='',conn_settings='(null)',disallow_premature=-1)
[3336-47.783]attribute = 'Database', value = '<DATABASENAMEHERE>'
[3336-47.783]copyAttributes: DSN='',server='<SERVERNAMEHERE>',dbase='<DATABASENAMEHERE>',user='<USERNAMEHERE>',passwd='',port='####',onlyread='',protocol='',conn_settings='(null)',disallow_premature=-1)
[3336-47.783]attribute = 'debug', value = '1'
[3336-47.783]copyAttributes: DSN='',server='<SERVERNAMEHERE>',dbase='<DATABASENAMEHERE>',user='<USERNAMEHERE>',passwd='',port='####',onlyread='',protocol='',conn_settings='(null)',disallow_premature=-1)
[3336-47.783]attribute = 'commlog', value = '1'
[3336-47.783]copyAttributes: DSN='',server='<SERVERNAMEHERE>',dbase='<DATABASENAMEHERE>',user='<USERNAMEHERE>',passwd='',port='####',onlyread='',protocol='',conn_settings='(null)',disallow_premature=-1)
[3336-47.783]getDSNinfo: DSN= overwrite=0
[3336-47.799]our_connect_string = 'DRIVER={PostgreSQL ANSI};UID=<USERNAMEHERE>;Server=<SERVERNAMEHERE>;Port=####;Database=<DATABASENAMEHERE>;debug=1;commlog=1;'
[3336-47.799]attribute = 'DRIVER', value = '{PostgreSQL ANSI}'
[3336-47.799]copyCommonAttributes: A7=100;A8=4096;A9=0;B0=255;B1=8190;B2=0;B3=0;B4=0;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[3336-47.799]attribute = 'UID', value = '<USERNAMEHERE>'
[3336-47.799]copyCommonAttributes: A7=100;A8=4096;A9=0;B0=255;B1=8190;B2=0;B3=0;B4=0;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[3336-47.799]attribute = 'Server', value = '<SERVERNAMEHERE>'
[3336-47.799]copyCommonAttributes: A7=100;A8=4096;A9=0;B0=255;B1=8190;B2=0;B3=0;B4=0;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[3336-47.799]attribute = 'Port', value = '####'
[3336-47.799]copyCommonAttributes: A7=100;A8=4096;A9=0;B0=255;B1=8190;B2=0;B3=0;B4=0;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[3336-47.799]attribute = 'Database', value = '<DATABASENAMEHERE>'
[3336-47.799]copyCommonAttributes: A7=100;A8=4096;A9=0;B0=255;B1=8190;B2=0;B3=0;B4=0;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[3336-47.799]attribute = 'debug', value = '1'
[3336-47.799]copyCommonAttributes: A7=100;A8=4096;A9=0;B0=255;B1=8190;B2=1;B3=0;B4=0;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[3336-47.799]attribute = 'commlog', value = '1'
[3336-47.799]copyCommonAttributes: A7=100;A8=4096;A9=0;B0=255;B1=8190;B2=1;B3=1;B4=0;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[3336-47.799]calling getDSNdefaults
[3336-47.799]CC_connect: entering...
[3336-47.799]sslmode=disable
[3336-47.799]original_CC_connect: entering...
[3336-47.799]Driver Version='09.03.0300,201405140001' linking 1600 dynamic Multithread library
[3336-47.799]original_CC_connect: DSN = '', server = '<SERVERNAMEHERE>', port = '####', database = '<DATABASENAMEHERE>', username = '<USERNAMEHERE>', password=''
[3336-47.799]connecting to the server socket...
[3336-47.814](0)(null) ERRNO=0
[3336-47.814]connection to the server socket succeeded.
[3336-47.814]!!! usrname=<USERNAMEHERE> server=<SERVERNAMEHERE>
[3336-47.814]sizeof startup packet = 80
[3336-47.814]sent the authentication block successfully.
[3336-47.814]gonna do authentication
[3336-47.814]read -1, global_socket_buffersize=4096
[3336-47.814]Lasterror=10035
[3336-47.830]read 9, global_socket_buffersize=4096
[3336-47.830]auth got 'R'
[3336-47.830]areq = 9 salt=0000000000
[3336-47.830]in AUTH_REQ_SSPI

[psqlodbc_3380.log]

 [0.015]Driver Version='09.03.0300,201405140001' linking 1600 dynamic Multithread library
[0.015]Global Options: fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=255, max_longvarchar_size=8190
[0.015]                disable_optimizer=0, ksqo=1, unique_index=1, use_declarefetch=0
[0.015]                text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64
[0.031]                extra_systable_prefixes='dd_;', conn_settings='(null)' conn_encoding=''
[47.767]conn=02B481C0, PGAPI_DriverConnect( in)='DRIVER={PostgreSQL ANSI};UID=<USERNAMEHERE>;Server=<SERVERNAMEHERE>;Port=####;Database=<DATABASENAMEHERE>;debug=1;commlog=1;', fDriverCompletion=0
[47.799]Driver Version='09.03.0300,201405140001' linking 1600 dynamic Multithread library
[47.799]Global Options: fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=255, max_longvarchar_size=8190
[47.799]                disable_optimizer=0, ksqo=1, unique_index=1, use_declarefetch=0
[47.799]                text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64
[47.799]                extra_systable_prefixes='dd_;', conn_settings='(null)' conn_encoding=''

EDIT — server logs:
Here are the log entries from right around the time of my testing. It’s pretty much the same thing I posted before, but I went forward a few minutes to allow for connection timeouts and such. It’s from a different function in my VB6 application that’s calling a stored procedure, but it’s resulting in the same error/issue with the SSPI authentication.

 2014-06-30 16:57:17 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 16:57:17 CDT LOG:  unexpected EOF on client connection
2014-06-30 16:57:30 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 16:57:30 CDT LOG:  unexpected EOF on client connection
2014-06-30 16:57:31 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 16:57:31 CDT LOG:  unexpected EOF on client connection
2014-06-30 16:58:38 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 16:58:38 CDT LOG:  unexpected EOF on client connection
2014-06-30 16:59:02 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 16:59:02 CDT LOG:  unexpected EOF on client connection
2014-06-30 16:59:22 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 16:59:22 CDT LOG:  unexpected EOF on client connection
2014-06-30 17:00:23 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 17:00:23 CDT LOG:  unexpected EOF on client connection
2014-06-30 17:00:44 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 17:00:44 CDT LOG:  unexpected EOF on client connection
2014-06-30 17:00:46 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 17:00:46 CDT LOG:  unexpected EOF on client connection
2014-06-30 17:00:52 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 17:00:52 CDT LOG:  unexpected EOF on client connection
2014-06-30 17:01:17 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 17:01:17 CDT LOG:  unexpected EOF on client connection
2014-06-30 17:01:43 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 17:01:43 CDT FATAL:  SSPI authentication failed for user "<USERNAMEHERE>"
2014-06-30 17:01:43 CDT LOG:  could not send data to client: No connection could be made because the target machine actively refused it.    
2014-06-30 17:01:43 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 17:01:43 CDT FATAL:  SSPI authentication failed for user "<USERNAMEHERE>"
2014-06-30 17:01:43 CDT LOG:  could not send data to client: No connection could be made because the target machine actively refused it.    
2014-06-30 17:03:56 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 17:03:56 CDT LOG:  unexpected EOF on client connection
2014-06-30 17:04:02 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 17:04:02 CDT LOG:  unexpected EOF on client connection
2014-06-30 17:04:10 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.
2014-06-30 17:04:10 CDT LOG:  unexpected EOF on client connection
2014-06-30 17:05:30 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 17:05:30 CDT LOG:  unexpected EOF on client connection
2014-06-30 17:05:39 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 17:05:39 CDT LOG:  unexpected EOF on client connection
2014-06-30 17:06:33 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 17:06:33 CDT LOG:  unexpected EOF on client connection
2014-06-30 17:11:05 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 17:11:05 CDT LOG:  unexpected EOF on client connection
2014-06-30 17:11:50 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.   
2014-06-30 17:11:50 CDT LOG:  unexpected EOF on client connection

Я могу попытаться включить дополнительную отладку, но мне может потребоваться немного времени, чтобы получить из нее какие-либо данные. Конечно, возможно (как вы предположили ранее), что проблема связана с версией PostgreSQL, за исключением того, что эти же функции будут корректно работать в 32-разрядной тестовой среде WinXP с тем же установленным ODBC connector.

РЕДАКТИРОВАТЬ — log_min_messages = debug1:

 2014-07-02 10:33:24 CDT DEBUG:  autovacuum: processing database "<DATABASENAMEHERE>"
2014-07-02 10:33:25 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.


2014-07-02 10:33:25 CDT FATAL:  SSPI authentication failed for user "<USERNAMEHERE>"
2014-07-02 10:33:25 CDT LOG:  could not send data to client: No connection could be made because the target machine actively refused it.


2014-07-02 10:33:29 CDT DEBUG:  autovacuum: processing database "<DATABASENAMEHERE>"

log_min_messages = debug5

 2014-07-02 10:36:49 CDT DEBUG:  StartTransaction
2014-07-02 10:36:49 CDT DEBUG:  name: unnamed; blockState:       DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
2014-07-02 10:36:49 CDT DEBUG:  CommitTransaction
2014-07-02 10:36:49 CDT DEBUG:  name: unnamed; blockState:       STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
2014-07-02 10:36:49 CDT DEBUG:  InitPostgres
2014-07-02 10:36:49 CDT DEBUG:  my backend ID is 5
2014-07-02 10:36:49 CDT DEBUG:  StartTransaction
2014-07-02 10:36:49 CDT DEBUG:  name: unnamed; blockState:       DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
2014-07-02 10:36:49 CDT DEBUG:  CommitTransaction
2014-07-02 10:36:49 CDT DEBUG:  name: unnamed; blockState:       STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
2014-07-02 10:36:49 CDT DEBUG:  autovacuum: processing database "<DATABASENAMEHERE>"
2014-07-02 10:36:49 CDT DEBUG:  StartTransaction
2014-07-02 10:36:49 CDT DEBUG:  name: unnamed; blockState:       DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
2014-07-02 10:36:49 CDT DEBUG:  pg_statistic: vac: 80 (threshold 124), anl: 560 (threshold 87)
2014-07-02 10:36:49 CDT DEBUG:  pg_type: vac: 0 (threshold 112), anl: 0 (threshold 81)
2014-07-02 10:36:49 CDT DEBUG:  pg_attribute: vac: 0 (threshold 651), anl: 0 (threshold 351)
2014-07-02 10:36:49 CDT DEBUG:  pg_authid: vac: 19 (threshold 50), anl: 30 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  pg_proc: vac: 0 (threshold 518), anl: 0 (threshold 284)
2014-07-02 10:36:49 CDT DEBUG:  pg_class: vac: 0 (threshold 106), anl: 0 (threshold 78)
2014-07-02 10:36:49 CDT DEBUG:  pg_database: vac: 3 (threshold 50), anl: 7 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  pg_inherits: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  pg_index: vac: 0 (threshold 72), anl: 0 (threshold 61)
2014-07-02 10:36:49 CDT DEBUG:  pg_operator: vac: 0 (threshold 191), anl: 0 (threshold 121)
2014-07-02 10:36:49 CDT DEBUG:  pg_opfamily: vac: 0 (threshold 64), anl: 0 (threshold 57)
2014-07-02 10:36:49 CDT DEBUG:  pg_opclass: vac: 0 (threshold 73), anl: 0 (threshold 61)
2014-07-02 10:36:49 CDT DEBUG:  pg_amop: vac: 0 (threshold 122), anl: 0 (threshold 86)
2014-07-02 10:36:49 CDT DEBUG:  pg_amproc: vac: 0 (threshold 100), anl: 0 (threshold 75)
2014-07-02 10:36:49 CDT DEBUG:  pg_language: vac: 0 (threshold 51), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  pg_largeobject_metadata: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  pg_rewrite: vac: 0 (threshold 70), anl: 0 (threshold 60)
2014-07-02 10:36:49 CDT DEBUG:  pg_description: vac: 0 (threshold 705), anl: 0 (threshold 378)
2014-07-02 10:36:49 CDT DEBUG:  pg_cast: vac: 0 (threshold 89), anl: 0 (threshold 70)
2014-07-02 10:36:49 CDT DEBUG:  pg_namespace: vac: 0 (threshold 51), anl: 0 (threshold 51)
2014-07-02 10:36:49 CDT DEBUG:  pg_conversion: vac: 0 (threshold 76), anl: 0 (threshold 63)
2014-07-02 10:36:49 CDT DEBUG:  pg_depend: vac: 0 (threshold 1219), anl: 0 (threshold 635)
2014-07-02 10:36:49 CDT DEBUG:  pg_tablespace: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  pg_pltemplate: vac: 0 (threshold 52), anl: 0 (threshold 51)
2014-07-02 10:36:49 CDT DEBUG:  pg_auth_members: vac: 9 (threshold 75), anl: 37 (threshold 62)
2014-07-02 10:36:49 CDT DEBUG:  pg_shdepend: vac: 100 (threshold 943), anl: 394 (threshold 496)
2014-07-02 10:36:49 CDT DEBUG:  pg_shdescription: vac: 36 (threshold 50), anl: 42 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  pg_ts_config: vac: 0 (threshold 53), anl: 0 (threshold 52)
2014-07-02 10:36:49 CDT DEBUG:  pg_ts_dict: vac: 0 (threshold 53), anl: 0 (threshold 52)
2014-07-02 10:36:49 CDT DEBUG:  pg_ts_parser: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  pg_ts_template: vac: 0 (threshold 51), anl: 0 (threshold 51)
2014-07-02 10:36:49 CDT DEBUG:  pg_extension: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  pg_foreign_data_wrapper: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  pg_foreign_server: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  pg_default_acl: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  pg_seclabel: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  pg_collation: vac: 0 (threshold 51), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  pg_attrdef: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  pg_am: vac: 0 (threshold 51), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  pg_db_role_setting: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 104), anl: 0 (threshold 77)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 96), anl: 0 (threshold 73)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 83), anl: 0 (threshold 67)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 104), anl: 0 (threshold 77)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 134), anl: 0 (threshold 92)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 82), anl: 0 (threshold 66)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 71), anl: 0 (threshold 61)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 80), anl: 0 (threshold 65)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 50), anl: 0 (threshold 50)
2014-07-02 10:36:49 CDT DEBUG:  <TABLENAMEHERE>: vac: 0 (threshold 826), anl: 0 (threshold 438)
2014-07-02 10:36:49 CDT DEBUG:  CommitTransaction
2014-07-02 10:36:49 CDT DEBUG:  name: unnamed; blockState:       STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
2014-07-02 10:36:49 CDT DEBUG:  shmem_exit(0): 8 callbacks to make
2014-07-02 10:36:49 CDT DEBUG:  proc_exit(0): 2 callbacks to make
2014-07-02 10:36:49 CDT DEBUG:  exit(0)
2014-07-02 10:36:49 CDT DEBUG:  shmem_exit(-1): 0 callbacks to make
2014-07-02 10:36:49 CDT DEBUG:  proc_exit(-1): 0 callbacks to make
2014-07-02 10:36:49 CDT DEBUG:  reaping dead processes
2014-07-02 10:36:49 CDT DEBUG:  server process (PID 5692) exited with exit code 0
2014-07-02 10:36:51 CDT LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.


2014-07-02 10:36:51 CDT FATAL:  SSPI authentication failed for user "<USERNAMEHERE>"
2014-07-02 10:36:51 CDT LOG:  could not send data to client: No connection could be made because the target machine actively refused it.


2014-07-02 10:36:51 CDT DEBUG:  shmem_exit(1): 7 callbacks to make
2014-07-02 10:36:51 CDT DEBUG:  proc_exit(1): 3 callbacks to make
2014-07-02 10:36:51 CDT DEBUG:  exit(1)
2014-07-02 10:36:51 CDT DEBUG:  shmem_exit(-1): 0 callbacks to make
2014-07-02 10:36:51 CDT DEBUG:  proc_exit(-1): 0 callbacks to make
2014-07-02 10:36:51 CDT DEBUG:  reaping dead processes
2014-07-02 10:36:51 CDT DEBUG:  server process (PID 4300) exited with exit code 1

1. Недавно я отлаживал некоторые проблемы, связанные с SysWOW64, с помощью psqlODBC. Я бы не удивился, если бы это было еще одно. Что касается 64-разрядного драйвера — вы не можете использовать 64-разрядный драйвер ODBC с 32-разрядным исполняемым файлом, и aFAIK нет 64-разрядной среды выполнения VB6, она работает только на SysWOW64 как 32-разрядное приложение под 64-разрядной Windows. На вашем месте я бы (а) обновился до менее доисторической версии PostgreSQL, по крайней мере, до текущей версии исправления 9.1; (б) включил трассировку драйверов psqlODBC («mylog») и просматривал журналы; (в) использовал wireshark для просмотра обмена данными по проводам.

2. Кроме того, вы не упомянули свою версию psqlODBC. Какую версию вы используете? Установите последнюю версию MSI драйвера 09.03.0300 (32- и 64-разрядные версии) и повторите попытку.

3. @Craig — Спасибо за ваши отзывы и извините за задержку с ответом. Сначала мы использовали более старую версию соединителя psqlODBC, но я установил последнюю версию без изменений в поведении SSPI. Что касается упомянутых вами 64-разрядных проблем, я боялся, что могу столкнуться с такой проблемой, но это была мысль, которая возникла у меня, когда я пытался найти решение. Я рассмотрю возможность обновления версии PostgreSQL, которую мы используем.

4. Что ж, первое, что нужно сделать, это записать журналы соединения с включенным как системным протоколированием odbc, так и собственным протоколированием psqlODBC («mylog»)…

5. О, я также только что заметил это в самой последней истории git драйвера, после выпуска 09030300. git.postgresql.org/gitweb /… . Это может иметь отношение к делу.

Ответ №1:

Вы проверили регистр для вашего логина «USERNAMEHERE» ?

Логин должен быть идентичным в Windows и pgsql.

У нас даже есть проблема, когда логин Windows — «usera», а логин pgsql — «usera», но логин, отправленный через odbc, — «Usera».

Похоже, что Windows использует то, что вы использовали для входа в систему в качестве имени пользователя, вместо того, что определено в AD.

Мы должны вручную исправить строку подключения для этого.

В противном случае вам не нужно иметь жестко запрограммированный логин, вы можете использовать сопоставление в pg_hba.conf. (см http://www.postgresql.org/docs/9.4/static/auth-username-maps.html )

1. Спасибо за ваше предложение. Для решения этой проблемы я, наконец, решил использовать жестко запрограммированный набор учетных данных для базы данных. Я проверил регистр имени пользователя и даже создал процедуру для получения точного имени входа в систему в том виде, в каком оно отображается в Active Directory, чтобы убедиться, что я передаю его правильно. Я действительно не хотел проходить процесс настройки сопоставления, поскольку у меня уже есть SSPI, работающий во всех моих .NET-приложениях, поэтому я просто вернусь и остановлюсь на этом. Надеюсь, я скоро смогу перестроить это приложение в .NET, и весь этот вопрос будет спорным.