Our company has recently started doing business with a partner that is trying to give us a Web-based RDP connection into their systems. As a part of their setup documentation, they are requiring us to change the Local Network Security policy for our workstations: specifically, changing the LAN Manager authentication level setting from the previous Send LM & NTLM responses to Send NTLMv2 response only. Once I make this change and gpupdate /force the changes to the workstation (which then shows the policy "correctly" set), I can RDP into our new partner's server just fine, but all of my applications that connect to the PostgreSQL database file with the following error:
FATAL: XX000: could not accept SSPI security context
I've built a little temporary application to test the connection in Visual Studio with the following code (network details redacted):
Imports Npgsql
Imports System.DirectoryServices
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim PGUserName As String
Dim PGDB As NpgsqlConnection = Nothing
Dim PGConnection As NpgsqlConnectionStringBuilder
Dim PGCommand As NpgsqlCommand = Nothing
Dim PGAdapter As NpgsqlDataAdapter = Nothing
Dim TestSQL As String = String.Empty
Dim TestData As New DataTable
PGUserName = GetActiveDirectoryUsername()
TestSQL = "SELECT * FROM testdb"
PGConnection = New NpgsqlConnectionStringBuilder
With PGConnection
NpgsqlEventLog.Level = LogLevel.Debug
NpgsqlEventLog.LogName = "C:\TEST\NPGSQLEVENTLOG.TXT"
.Host = "<PGSQLSERVER>"
.Port = <PORT>
.UserName = PGUserName
.IntegratedSecurity = True
.Database = "testing"
End With
PGDB = New NpgsqlConnection(PGConnection.ConnectionString)
Try
PGDB.Open()
PGCommand = New NpgsqlCommand(TestSQL, PGDB)
PGAdapter = New NpgsqlDataAdapter(PGCommand)
PGAdapter.Fill(TestData)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
If Not PGAdapter Is Nothing Then
PGAdapter.Dispose()
End If
If Not PGCommand Is Nothing Then
PGCommand.Dispose()
End If
If PGDB.State <> ConnectionState.Closed Then
PGDB.Close()
End If
If Not PGDB Is Nothing Then
PGDB.Dispose()
End If
If Not TestData Is Nothing Then
TestData.Dispose()
End If
End Try
End Sub
Friend Function GetDirectoryEntry() As DirectoryEntry
Dim dirEntry As DirectoryEntry = New DirectoryEntry()
dirEntry.Path = "LDAP://<ADSERVERIP>/DC=<DOMAIN>"
Return dirEntry
End Function
Friend Function GetActiveDirectoryUsername() As String
Try
Dim MyDirectory As DirectoryEntry = GetDirectoryEntry()
Dim search As New DirectorySearcher(MyDirectory)
search.Filter = String.Format("(&(SAMAccountName={0}))", Environment.UserName)
'Use the .FindOne() Method to stop as soon as a match is found
Dim result As SearchResult = search.FindOne()
If result Is Nothing Then
Return ""
Else
Return result.Properties("samaccountname").Item(0).ToString
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "Active Directory Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Return ""
End Try
End Function
End Class
All of this code works perfectly if I leave the LAN Manager authentication level set on Send LM & NTLM responses, but if I change it to Send NTLMv2 response only as is required by our new partner, it throws an exception on the PGDB.Open() statement. From the debug log:
2/22/16 12:32:58 PM 11452 Debug Entering NpgsqlConnection.NpgsqlConnection(NpgsqlConnection())
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: HOST = <PGSQLSERVER>
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: PORT = <PORT>
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: PROTOCOL = 3
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: DATABASE = testing
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: USER ID = <PGSQLUSERNAME>
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: SSL = False
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: SSLMODE = Disable
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: TIMEOUT = 15
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: POOLING = True
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: CONNECTIONLIFETIME = 15
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: MINPOOLSIZE = 1
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: MAXPOOLSIZE = 20
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: SYNCNOTIFICATION = False
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: COMMANDTIMEOUT = 20
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: ENLIST = False
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: PRELOADREADER = False
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: USEEXTENDEDTYPES = False
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: INTEGRATED SECURITY = True
2/22/16 12:32:58 PM 11452 Debug ConnectionString Option: COMPATIBLE = 2.0.11.92
2/22/16 12:32:58 PM 11452 Debug Entering NpgsqlConnection.Open()
2/22/16 12:32:58 PM 11452 Debug Get NpgsqlClosedState.Instance
2/22/16 12:32:58 PM 11452 Debug Get NpgsqlClosedState.Instance
2/22/16 12:32:58 PM 11452 Debug Entering NpgsqlClosedState.Open()
2/22/16 12:32:58 PM 11452 Debug Attempt to connect to '<PGSQLSERVERIP>'.
2/22/16 12:32:58 PM 11452 Normal Connected to: <PGSQLSERVER>:<PORT>.
2/22/16 12:32:58 PM 11452 Debug Entering NpgsqlStartupPacket.NpgsqlStartupPacket()
2/22/16 12:32:58 PM 11452 Debug Entering NpgsqlStartupPacket.WriteToStream()
2/22/16 12:32:58 PM 11452 Debug Entering NpgsqlStartupPacket.WriteToStream_Ver_3()
2/22/16 12:32:58 PM 11452 Debug Entering PGUtil.WriteString()
2/22/16 12:32:58 PM 11452 Debug String written: user.
2/22/16 12:32:58 PM 11452 Debug Entering PGUtil.WriteString()
2/22/16 12:32:58 PM 11452 Debug String written: <PGSQLUSERNAME>.
2/22/16 12:32:58 PM 11452 Debug Entering PGUtil.WriteString()
2/22/16 12:32:58 PM 11452 Debug String written: database.
2/22/16 12:32:58 PM 11452 Debug Entering PGUtil.WriteString()
2/22/16 12:32:58 PM 11452 Debug String written: testing.
2/22/16 12:32:58 PM 11452 Debug Entering PGUtil.WriteString()
2/22/16 12:32:58 PM 11452 Debug String written: DateStyle.
2/22/16 12:32:58 PM 11452 Debug Entering PGUtil.WriteString()
2/22/16 12:32:58 PM 11452 Debug String written: ISO.
2/22/16 12:32:58 PM 11452 Debug Entering NpgsqlState.ProcessBackendResponses()
2/22/16 12:32:58 PM 11452 Debug AuthenticationRequest message received from server.
2/22/16 12:32:58 PM 11452 Debug Entering NpgsqlStartupState.Authenticate()
2/22/16 12:32:58 PM 11452 Debug Entering NpgsqlPasswordPacket.NpgsqlPasswordPacket()
2/22/16 12:32:58 PM 11452 Debug Entering NpgsqlPasswordPacket.WriteToStream()
2/22/16 12:32:58 PM 11452 Debug Entering PGUtil.WriteBytes()
2/22/16 12:32:58 PM 11452 Debug Unable to find resource string Log_BytesWritten for class PGUtil
2/22/16 12:32:58 PM 11452 Debug AuthenticationRequest message received from server.
2/22/16 12:32:58 PM 11452 Debug Entering NpgsqlStartupState.Authenticate()
2/22/16 12:32:58 PM 11452 Debug Entering NpgsqlPasswordPacket.NpgsqlPasswordPacket()
2/22/16 12:32:58 PM 11452 Debug Entering NpgsqlPasswordPacket.WriteToStream()
2/22/16 12:32:58 PM 11452 Debug Entering PGUtil.WriteBytes()
2/22/16 12:32:58 PM 11452 Debug Unable to find resource string Log_BytesWritten for class PGUtil
2/22/16 12:32:58 PM 11452 Debug Entering PGUtil.ReadString()
2/22/16 12:32:58 PM 11452 Debug Get NpgsqlEventLog.LogLevel
2/22/16 12:32:58 PM 11452 Debug String read: FATAL.
2/22/16 12:32:58 PM 11452 Debug Entering PGUtil.ReadString()
2/22/16 12:32:58 PM 11452 Debug Get NpgsqlEventLog.LogLevel
2/22/16 12:32:58 PM 11452 Debug String read: XX000.
2/22/16 12:32:58 PM 11452 Debug Entering PGUtil.ReadString()
2/22/16 12:32:58 PM 11452 Debug Get NpgsqlEventLog.LogLevel
2/22/16 12:32:58 PM 11452 Debug String read: could not accept SSPI security context.
2/22/16 12:32:58 PM 11452 Debug Entering PGUtil.ReadString()
2/22/16 12:32:58 PM 11452 Debug Get NpgsqlEventLog.LogLevel
2/22/16 12:32:58 PM 11452 Debug String read: The token supplied to the function is invalid
(80090308).
2/22/16 12:32:58 PM 11452 Debug Entering PGUtil.ReadString()
2/22/16 12:32:58 PM 11452 Debug Get NpgsqlEventLog.LogLevel
2/22/16 12:32:58 PM 11452 Debug String read: src\backend\libpq\auth.c.
2/22/16 12:32:58 PM 11452 Debug Entering PGUtil.ReadString()
2/22/16 12:32:58 PM 11452 Debug Get NpgsqlEventLog.LogLevel
2/22/16 12:32:58 PM 11452 Debug String read: 1024.
2/22/16 12:32:58 PM 11452 Debug Entering PGUtil.ReadString()
2/22/16 12:32:58 PM 11452 Debug Get NpgsqlEventLog.LogLevel
2/22/16 12:32:58 PM 11452 Debug String read: pg_SSPI_error.
2/22/16 12:32:58 PM 11452 Debug ErrorResponse message from Server: could not accept SSPI security context.
2/22/16 12:32:58 PM 11452 Normal An NpgsqlException occured: FATAL: XX000: could not accept SSPI security context.
2/22/16 12:33:02 PM 11452 Debug Entering NpgsqlConnection.Dispose()
2/22/16 12:33:03 PM 11452 Debug Entering NpgsqlConnection.Close()
Oddly enough, if I try to connect using PGAdmin III using SSPI, there's no error. I restarted the PostgreSQL service on the server (just in case), and still got the same error. I even went as far as rebooting the whole server, but the error persists.
Now, if I change the Local Network Security policy setting back to Send LM & NTLM responses, the application works normally, but we can't establish the RDP connection to our partner's system. I even tried changing the policy setting to Send LM & NTLM - use NTLMv2 session security if negotiated. Again, my application will connect to the database without an error, but the RDP connection fails.
I guess the question boils down to this: Does anyone know how to get Npgsql to connect to a PostgreSQL database while the Local Network Security policy's LAN Manager authentication level setting is configured to Send NTLMv2 response only? Since everything else seems to work normally (as far as I can tell), this seems to be the point of failure.
EDIT: Since I'm working in Visual Studio 2015, I tried updating the version of Npgsql I was using through the NuGet console. This updated the library to version 3.0.5.0, and I tested the connection again under the Send NTLMv2 response only setting. This time the application's connection appears to have worked normally. Just to be sure, I removed the 3.0.5.0 reference and added my older version back (apparently I'm on 2.0.11.92 - yeah, yeah... I know) and tested again, which gave me the same error as before.
It looks like whatever is causing the issue has been resolved with later updates to the Npgsql library. Of course, that means that, unless there is a workaround for the version I'm using (2.0.11.92), I'm going to have to update ALL of my internal applications to make use of the newer library. I really don't have time for all of that right now, so if anyone has a way to make this work, I'd love to hear it.
@G_Hosa_Phat. I remember we had a big rework of the ntlm support, as you could check by testing recent 3.0.x versions and get it working...
There is no configuration options to change the behavior of ntlm authentication you could use. I think you really would need to update your library.
But I would suggest you to update to the most recent 2.x version, instead of a 3.0.x as the changes of 3.0.x are beyond the ntlm support and it could break your existing systems.
Sorry to give you this bad news. :(
I hope it helps.