3

The Environment

I have a 30-year-old Windows desktop application written in Delphi 10.4 (upgraded numerous times throughout its life). It has a TDataModule with approximately 100 TADOQuery, TADODataSetProvider, TClientDataSet, TDataSource, and other related components. There is only one TADOConnection component for the entire application. These components are chained together at design time so that the only value that needs to be set at runtime is the TADOConnection's ConnectionString property. Also, the connection string has "Persist Security Info=True" in it. This has been working great for decades.

We recently added a custom class (TSecureString - to mimic C#'s SecureString class) to store sensitive string data in a format that was unreadable in memory. The connection string that we read from an INI file is stored in memory as TSecureString. It is only converted back to plain text when assigning it to TADOConnection.ConnectionString.

The Problem

Corporate IT did a security scan and said they could see our plain text connection string in memory. The security scan report said that this plain text connection string was in memory even when the program was just sitting on the main form right after logging in, not doing any SQL calls.

I've seen numerous posts (like hide connection user password in memory in delphi TadoConnection) that say to use TADOConnection's OnWillConnect property to change the user id and password. This seems ideal, but we only set the Connected property to True once during login. If Connected is not set to True, then every time any of those 100 components are used, they would need to have Connected set to true first. This seems unreasonable considering 1) how the components were designed, and 2) the sheer volume of code that would have to change. Additionally, none of these posts state how to remove the user ID and password after one is done with the execution.

Using the OnWillExecute event would solve the above issues, but changing the Connection parameter's ConnectionString property results in the error "Operation is not allowed when the object is open.", which is reasonable.

Also, changing the connection string to "Persist Security Info=False" won't work (AFAIK) because the user ID and password need to be set when Connected is set to true. There doesn't seem to be any way to change these value at the components' SQL execution time.

The Ask

What is the technically best, correct way of using TADOConnection globally while still hiding the connection string in memory, since it appears that the TADOConnection object doesn't do that by itself?

If you think that the TADOConnection object does hide/encrypt the connection string in memory, then please provide documentation to that effect. I could not find anything one way or another in this regard.

Caveats

Please do not provide answers with generic best security practices with connection strings, databases, or web-based solutions. It's a desktop application working with a database on a LAN. Also, this application is being sunset, so major overhauls of this nature won't be done. This question is about the right way to use the TADOConnection component securely.

Ken White
  • 123,280
  • 14
  • 225
  • 444
Bond
  • 79
  • 4
  • What database? Some provide a way to use Windows authentication instead of username/password. SQL Server does this well - One approach is to add a group in Active Directory that you add users to. Then that group is given permissions to the database needed by the application. No username and password is in the connection string just a reference to the database itself. – Brian Sep 07 '22 at 01:28
  • I don't know `ADOConnection` well enough but in Delphi 11 it provides a procedure called `Open` with two parameters called `UserID` and `Password` and it clears those values after it connected. Wouldn't that help (if it is available in 10.4 of course)? – Blurry Sterk Sep 07 '22 at 06:26
  • May I assume that the credentials saved in the `INI` are also encrypted? – Blurry Sterk Sep 07 '22 at 06:38
  • the problem is not [new](https://stackoverflow.com/q/47951499/800214). The only option is to not use a password, but it implies windows authentication and that is not always possible, [Microsoft says the same btw](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/protecting-connection-information). – whosrdaddy Sep 07 '22 at 08:45
  • Another [link](https://security.stackexchange.com/a/106869/173906), this gist is, do no use sql server authentication... – whosrdaddy Sep 07 '22 at 08:55
  • I'm ignoring the comments about using Windows Authentication, which my Caveats section said I would. @whosrdaddy I already saw the first link you posted - it was in my original post. It did not answer my question. As for your second link, this is a Delphi question, not a MS question. Ditto for the third link. – Bond Sep 07 '22 at 19:11
  • @BlurrySterk It does still have that Open method. [Documentation](https://docwiki.embarcadero.com/Libraries/Sydney/en/Data.Win.ADODB.TADOConnection.Open) is a little lacking for me. How would I use it - instead of the Connected property or in addition to it? When would I use it - before each related component's Execute or Open method or just once at the beginning? Example code (even pseudo-code) would be nice. That's a big reason for this question - I just don't see how all these methods and components are designed to fit together to work securely. Or maybe the answer is that they can't? – Bond Sep 07 '22 at 19:14
  • 1
    @MattBond - Remove the user credentials from the connection string then instead of setting `Connected` to true in your code you can try calling `Open` with the user credentials as paramaters. – Blurry Sterk Sep 08 '22 at 07:36
  • @MattBond: ADO is an MS technology, Delphi just calls the COM wrapper. So any security concern about will affect any application using it (written in any language) – whosrdaddy Sep 09 '22 at 14:58

0 Answers0