Debugging PostgreSQL SSL connections
Debugging PostgreSQL SSL connections
Hi,
When I set "Connected" to True in the Delphi (2010) IDE for a PostgreSQL connection using SSL, it just hangs and generates Page Faults (counter keeps going up in the Windows monitor). The cursor first changes to the hourglass with SQL for some seconds, then gets stuck at the normal hourglass.
When I run the application and use a button to set Connected to True, everything works just fine (and I can see that the SSL DLL's get loaded in the debugger).
I have the feeling that the IDE is not loading SSL dll libraries (I'm using the open source ODBC driver for PostgreSQL that includes the EasySSL libraries).
Please advise.
kind regards,
Jan
When I set "Connected" to True in the Delphi (2010) IDE for a PostgreSQL connection using SSL, it just hangs and generates Page Faults (counter keeps going up in the Windows monitor). The cursor first changes to the hourglass with SQL for some seconds, then gets stuck at the normal hourglass.
When I run the application and use a button to set Connected to True, everything works just fine (and I can see that the SSL DLL's get loaded in the debugger).
I have the feeling that the IDE is not loading SSL dll libraries (I'm using the open source ODBC driver for PostgreSQL that includes the EasySSL libraries).
Please advise.
kind regards,
Jan
Hello
The problem is that the current folder for application in the design-time and in the run-time is different. In the design-time the current folder is the folder where Delphi EXE is located. In the run-time the current folder is the folder where the application EXE file is located. That's why your application cannot load DLL in the design-time.
The problem is that the current folder for application in the design-time and in the run-time is different. In the design-time the current folder is the folder where Delphi EXE is located. In the run-time the current folder is the folder where the application EXE file is located. That's why your application cannot load DLL in the design-time.
Hi,
The DLLs are neither in the Delphi EXE directory nor in the application's directory but in the Windows System directory and - as it turned out - not just in one place (multiple versions are present).
I had a closer look and found that the PostgreSQL ODBC driver installer didn't install the DLLs in the Windows directory and didn't prepend the path where it did install them to the Windows path either.
The Windows directory contained (much) older versions of these DLLs; once i replaced them with the more current ones that come with the PostgreSQL ODBC driver, all works as expected.
Problem solved, thanks for your help,
Jan
The DLLs are neither in the Delphi EXE directory nor in the application's directory but in the Windows System directory and - as it turned out - not just in one place (multiple versions are present).
I had a closer look and found that the PostgreSQL ODBC driver installer didn't install the DLLs in the Windows directory and didn't prepend the path where it did install them to the Windows path either.
The Windows directory contained (much) older versions of these DLLs; once i replaced them with the more current ones that come with the PostgreSQL ODBC driver, all works as expected.
Problem solved, thanks for your help,
Jan
You can use SecureBridge components to create the secure connection to PostgreSQL via SSL without any dll libraries and without third-party components. You can find more detailed information about SecureBridge by the following link: http://devart.com/sbridge/
Problems SecureBridge with PgDAC
Hi Dimon,
I'm in the process of replacing the PostgreSQL ODBC SSL DLLs with SecureBridge and am running into a few difficulties. I hope you can help.
I am using datamodules, with TUniConnection, TCRSSLIOHandler and TScFileStorage components. I'm not using certificates, the properties of TCRSSLIOHandler and TScFileStorage are the defaults.
I can successfully set up a SSL connection with the PostgreSQL server by setting TUniConnection.Active to True as indicated by the fact that it doesn't work if I limit the PostgreSQL server to NoSSL connections only. I have removed the libeay32.dll and ssleay32.dll from the path and Windows directory so I'm positive TUniConnection is using SecureBridge.
I have two otherwise identical queries on my datamodule; one is using SpecificOptions FetchAll = True, the other one is using FetchAll = False. When FetchAll is False and I attempt to set Active to True, an error pops up:
"OpenSSL client library couldn't be loaded. Please place libeay32.dll and sseay32.dll (or libssl32.dll) to system folder (included to PATH) or to the folder with executable unit of main program"
When I set FetchAll = True, it opens successfully. Setting FetchAll back to False generates the error again.
To verify this behaviour once more I added the following code in my datamodule before opening the datasets:
with DataSet to SpecificOptions.Values['PostgreSQL.FetchAll'] := 'True'
With this code my application works as expected. Without it, it doesn't.
The datasets I'm working with are potentially quite large so needless to say that I can't live with "FetchALl" = True...
kind regards,
Jan
I'm in the process of replacing the PostgreSQL ODBC SSL DLLs with SecureBridge and am running into a few difficulties. I hope you can help.
I am using datamodules, with TUniConnection, TCRSSLIOHandler and TScFileStorage components. I'm not using certificates, the properties of TCRSSLIOHandler and TScFileStorage are the defaults.
I can successfully set up a SSL connection with the PostgreSQL server by setting TUniConnection.Active to True as indicated by the fact that it doesn't work if I limit the PostgreSQL server to NoSSL connections only. I have removed the libeay32.dll and ssleay32.dll from the path and Windows directory so I'm positive TUniConnection is using SecureBridge.
I have two otherwise identical queries on my datamodule; one is using SpecificOptions FetchAll = True, the other one is using FetchAll = False. When FetchAll is False and I attempt to set Active to True, an error pops up:
"OpenSSL client library couldn't be loaded. Please place libeay32.dll and sseay32.dll (or libssl32.dll) to system folder (included to PATH) or to the folder with executable unit of main program"
When I set FetchAll = True, it opens successfully. Setting FetchAll back to False generates the error again.
To verify this behaviour once more I added the following code in my datamodule before opening the datasets:
with DataSet to SpecificOptions.Values['PostgreSQL.FetchAll'] := 'True'
With this code my application works as expected. Without it, it doesn't.
The datasets I'm working with are potentially quite large so needless to say that I can't live with "FetchALl" = True...
kind regards,
Jan
Hi Dimon,
I agree that the behaviour observed suggests that SecureBridge is not being used. My point is that I am 100% sure I've done everything to the best of my knowledge to make sure the UniDAC components use SecureBridge. Further proof that suggests that I have done that correctly below. I have a feeling that somehow the UniDAC components decide not to use SecureBridge when FetchAll = False...?
The only property value I change is FetchAll... when set to True I can open the dataset (please note the connection using SSL has been established at this time; the TUniConnection component Active property remains True throughout my tests with the Delphi IDE).
When I set FetchAll to False (while keeping TUniConnection connected) in the Delphi IDE and then try to open the dataset by setting the dataset's Active property to True, I get the error message. Just setting FetchAll to True (not changing anything else) will allow me to open the dataset, which must use SecureBridge since a) the server doesn't accept none ssl connections, b) libeay32.dll and ssleay32.dll are not available.
To proof that IOHandler indeed is assigned, I inserted the following code just before opening the dataset:
if Assigned (dmMyModule.IOHandler) then ShowMessage ('IOHandler is assigned')
Surely this pops up a message window confirming IOHandler is assigned.
The Call Stack shows the following when the exception is raised:
KERNELBASE.RaiseException + 0x54
LoadSSLib + $E8
InitSSLib + $29
TCRVioTcpSSL.Create + $5E
TPgSQLProtocol.CreateVio + $136
InternalConnect + $15F
TPgSQLConnection.Connect + $53
TPgSQLConnection.PrepareFetchConnection + $41
TCRRecordSet.InternalPrepare +$8
TPgSQLRecordSet.ExecCommand +$49
TPgSQLRecordSet.ExecFetch + $23
TCRRecordSet.InternalOpen +$3B
TPgSQLRecordSet.InternalOpen + $A
TMemData.Open + $5
TDataSet.DoInternalOpen +$1F
TCustomUniDataSet.OpenCursor +$2C
TDataSet.SetActive +$5B
TCustomDADataSet.SetActive +$42
kind regards,
Jan
I agree that the behaviour observed suggests that SecureBridge is not being used. My point is that I am 100% sure I've done everything to the best of my knowledge to make sure the UniDAC components use SecureBridge. Further proof that suggests that I have done that correctly below. I have a feeling that somehow the UniDAC components decide not to use SecureBridge when FetchAll = False...?
The only property value I change is FetchAll... when set to True I can open the dataset (please note the connection using SSL has been established at this time; the TUniConnection component Active property remains True throughout my tests with the Delphi IDE).
When I set FetchAll to False (while keeping TUniConnection connected) in the Delphi IDE and then try to open the dataset by setting the dataset's Active property to True, I get the error message. Just setting FetchAll to True (not changing anything else) will allow me to open the dataset, which must use SecureBridge since a) the server doesn't accept none ssl connections, b) libeay32.dll and ssleay32.dll are not available.
To proof that IOHandler indeed is assigned, I inserted the following code just before opening the dataset:
if Assigned (dmMyModule.IOHandler) then ShowMessage ('IOHandler is assigned')
Surely this pops up a message window confirming IOHandler is assigned.
The Call Stack shows the following when the exception is raised:
KERNELBASE.RaiseException + 0x54
LoadSSLib + $E8
InitSSLib + $29
TCRVioTcpSSL.Create + $5E
TPgSQLProtocol.CreateVio + $136
InternalConnect + $15F
TPgSQLConnection.Connect + $53
TPgSQLConnection.PrepareFetchConnection + $41
TCRRecordSet.InternalPrepare +$8
TPgSQLRecordSet.ExecCommand +$49
TPgSQLRecordSet.ExecFetch + $23
TCRRecordSet.InternalOpen +$3B
TPgSQLRecordSet.InternalOpen + $A
TMemData.Open + $5
TDataSet.DoInternalOpen +$1F
TCustomUniDataSet.OpenCursor +$2C
TDataSet.SetActive +$5B
TCustomDADataSet.SetActive +$42
kind regards,
Jan