SecureBridge IOHandler for ODAC

[applespring]

Hello

For MyDAC, it is very easy to use SSH/SSL due to the exposure of IOHandler and use MySSHIOHandler/MySSLIOHandler

Is it possible also for ODAC to expose the IOHandler, and the same as MyDAC, to have similar SSH/SSL IOHandler?

The background is, due to security reason, customer requires SSH/SSL protection for all the connection to their Oracle server if public internet route are used, and they have installed the SSH server at their gateway.

Is it possible for ODAC?

[applespring]

more info:

Since we need to connect to different remote Oracle server at the same time, one TOraSession for "master", others for "slave" , using local port forwarding will be very complicated as we need to in advance config local different port to different remote server.

[AlexP]

Hello,

To use ODAC with SecureBridge you should perform the following steps:
1. Install SSH server on the computer which runs Oracle server, setup it to listen a port allowed for remote connections (default 1521), and run the SSH server. You can find an SSH server implementation within SecureBridge demos.
2. Add TScSSHChannel and TScSSHClient components to your client application to make an SSH client, and setup port forwarding from a local port to your Oracle server through the port listened by the SSH server. For example, see the SSHClient demo of SecureBridge.
3. Setup your TOraSession to connect to the localhost on the port that you used for port forwarding.

[applespring]

Hello Alex

For this standard solution, as I have explained in the "additional info", is very complicated. As for each time customer inform us the IP address of the Oracle server to be investigated, the end user must first configure a local port for server mapping.

Comparing to the MyDAC application, which is transparent for end-user, they are complaining and expecting us to provide an Oracle application with the same easy-usage quality.

[AlexP]

Hello,

If the server port or ip-address is changed, end-user will have to modify OraSession settings in any way, independently on whether SecureBridge is used or not. Please describe the problem in more details in order for us to be able to give you a more detailed answer.

[applespring]

Hello

For the current MyDAC application for customer, we catch the event "BeforeConnect", and then simply copy the dest IP of MySQL server from TMyConnection to the SSH client's destination and start the SSH connection first.

So for end-user, the SSH is transparent to them.

Naturally, they expect that ODAC application has the same transparency and easy usage as MyDAC application.

[AlexP]

Hello,

ODAC allows working with the database in two modes - OCI and Direct. When using the OCI mode server data is saved to the tnsnames.ora file, and you won't be able to change the ip-address "on-the-fly". In the Direct mode all the connection data is set directly in the TOraSession component like the following:

Code: Select all

OraSession1.Options.Direct := True;
OraSession1.Server := 'hostname:1521:SID';

Therefore you can retrieve the ip-address (or the host name) from this property and set it in the SecureBridge components, as well as us when using MyDAC, for example:

Code: Select all

var
  ConnectParams: TStringList;
  i: integer;
begin
  OraSession1.Options.Direct := True;
  OraSession1.Server := 'dboracle:1521:orcl1020';
  OraSession1.Username := 'scott';
  OraSession1.Password := 'tiger';
  ConnectParams := TStringList.Create;
  try
    ConnectParams.Delimiter := ':';
    ConnectParams.DelimitedText  := OraSession1.Server;
    ScSSHChannel1.DestHost := ConnectParams[0];
    ScSSHChannel1.SourcePort := 1520;
    ScSSHChannel1.DestPort := StrToInt(ConnectParams[1]);
    OraSession1.Server := 'localhost:1520:' + ConnectParams[2];
  finally
    ConnectParams.Free;
  end;
  ScSSHChannel1.Connect;
  OraSession1.Connect;

[applespring]

Hello Alex

thanks for your kind reply.

We do use ODAC in direct mode.

The customer has about 200 Oracle servers be exposed to Internet connection through SSH protection.

And the use case, is that we often need to connect multiple Oracle at the same time, for data synchronization etc.

So now it is for the developer's challenge to have a smart way of dynamically allocate/de-allocate the local free ports.

[AlexP]

Hello,

In order for the ports to be freed more quickly, you can edit the Windows registry in the following way:

• 1. Start Registry Editor (Regedt32.exe).
  
  2. Locate the following key in the registry:
  
  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
  
  3. On the Edit menu, click Add Value, and then add the following registry value:
  
  Value Name: MaxUserPort
  Data Type: REG_DWORD
  Value: 65534
  This sets the number of ephemeral ports available to any user. The valid range is between 5000 and 65534 (decimal). The default value is 0x1388 (5000 decimal).
  
  4. On the Edit menu, click Add Value, and then add the following registry value:
  
  Value Name: TcpTimedWaitDelay
  Data Type: REG_DWORD
  Value: 1
  This sets the number of seconds to hold a TCP port connection in TIME_WAIT state before closing. The valid range is between 0 (zero) and 300 (decimal). The default value is 0x78 (120 decimal).
  
  5. Quit Registry Editor.
  
  6. Reboot the machine.