Issues when making logfile folder read-only

[WillEb]

Hi Everyone,

For regulatory reasons, I have to make the logfiles folder of my systems read-only to prevent unauthorized modification of data. Yes, I know they’re protected by checksum but my QA department aren’t satisfied with that…

To get around the obvious issue of needing to write logfiles into that folder in the first place we give local admins write permissions to the folder and use a network level tool to apply an admin token to HxRun.exe and HxMethodCopy.exe.

For Venus 5 this has worked perfectly. However, with Venus 6.2.2 I’m having issues. When I try to run a method using HxRun.exe I get the error “Cannot open database connection. (Cannot open database ‘HamiltonVectorDB’ requested by the login. The login failed. Login failed for user ‘Hamilton’.)” Whilst experimenting I have also gotten errors which explicity state that it hasn’t been possible to create the .mdf and .ldf files in the logfiles folder.

I have tried also adding the admin token to Hamilton.HxVectorDbManager.exe with no change.

If I disable Sample Tracking and turn off Vector Database in System Configuration Editor the problem also goes away. Unfortunately, I need Sample Tracking for many of my methods.

Does anyone know what might be causing this issue? Are there any other executables that try to write files into the logfiles folder that I need to elevate?

Many thanks for any help offered,
Will

[NickHealy_Hamilton]

Hi @WillEb

From your description, I do not think that is related to the read/write state of the Logfiles folder. This is a credentials issue with the Hamilton SQL server account (created during VENUS installation) preventing access to the server at run onset when run control needs to login to SQL server and establish a run database used by sample tracking.

There can be several potential issues at play here so Ill provide a few suggestions. First, it looks like you are already using SQL authentication instead of Windows authentication for SQL server access. This is highly recommended, so just double check that your VectorDB connection settings have the following login configuration:

First thing I would check would be confirming that the default ‘Hamilton’ SQL account password hasn’t been modified in the connection configuration settings above. For 6.2.2, the installed password is:

P@ssWord:DB@^VENUS#6

Paste that in and select ‘Test Connection’. If it works, you should be good. If not, then you’ll need to check settings withing the Vector SQL server. If you haven’t already, you will need to download and install Microsoft SQL Server Management Studio.

Once installed (using an administrator account) when you run SSMS you will be navigated to the SQL Server login page. Ensure you are set to the HAMILTON server name and using Windows Authentication.

Prior to logging in, select ‘Connection Properties’ and ensure ‘Trust server certificate’ is toggled on if it was not already. In some cases, this alone will solve your issue outright, but if not there are some other things to check once logging in.

Navigate to the Hamilton account highlighted in the location below and open it’s properties. First I would ensure the server roles are set to the ones checked below.

While it is unlikely that the default Hamilton SQL account password does not mee the requirements of your companies password policy, you can also uncheck that setting if you believe it to be applicable. For future reference, this is where you can change the password used for the ‘Hamilton’ SQL account used by run control. If you change it here, then you will need to update the password in the VectorDB connection settings in system configuration editor.

Hopefully some combination of the above will get you functioning. If not, then the SQL server instance for VENUS will likely need to be manually installed.

Thanks.

-Nick

[WillEb]

Hi @NickHealy_Hamilton

Thank you for the comprehensive reply.

I’ve tried to work through your suggestions but it looks like something quite strange has happened to the two installations of Venus 6.2.2 that I have available.

I’ve confirmed that my Vector Database Connection Settings match yours. I can’t verify that the password isn’t changed as it’s hidden, but if I over-type it with the password you give and test the connection I get the following error.

I installed SSMS as you suggested, ran it as a local admin, made sure that “Trust server certificate” was checked and connected to the local server. Worryingly, there seem to be quite a few entries under “Logins” that are missing.

I imagine this wouldn’t be the best way to fix the issue anyway, but I tried to add the “Hamilton” user with details matching your screenshot. However, I am given the below error.

I tried going back to Vector Database Connection Settings and selected “Prepare Server” in an attempt to re-create the server. I entered the username “Hamilton” and the password you provided.

However, this produces the below error.

It seems like the database has become corrupted. Is there a way to fix this without having to re-install Venus? The installer doesn’t seem to have a “repair” option anymore. I’m slightly concerned that this has happened independently on two PCs shortly after pushing a Group Policy update to them. The only change in the GPO applied compared to the previously applied policy was to make the log file folder read only to all users but local admins.

Many thanks for any help you can offer!
Will

[NickHealy_Hamilton]

Hi Will,

I’m wondering if the underlying issue is that the Windows user account that installed VENUS either didn’t install as administrator, or didn’t have administrator rights to begin with.

Microsoft SQL Server 2019 is an independent software component outside of the core VENUS environment, and will not require VENUS to be uninstalled reinstalled. SQL Server can be reinstalled independently so long as afterwards, the VENUS installation is configured to use an authorized SQL user account created during manual SQL Server installation.

Before resorting to that, lets try one last thing. Seeing as the ‘sa’ account (system administrator) is present, try using ‘sa’ as the user. This account has elevated database credentials compared to the default ‘Hamilton’ account. The same password applies:

sa
DB@^VENUS#6

These credentials can also be used to reprepare the database.

Let me know if the above works. Thanks.

-Nick

[WillEb]

Hi @NickHealy_Hamilton

Thanks for the extra input. I’ve managed to make some further progress.

I swapped the PC’s Group Policy back to the old version which gave “Everyone” the “Full Control” permission over the whole Hamilton folder, as well as having some other entities listed. I completely uninstalled Venus including SQL Server and re-installed it.

After the re-install I confirmed that everything worked as expected and that I could connect to the database via System Configuration Editor. I then, one be one, removed folder permissions, getting closer and closer to how the new Group Policy if configured. By doing that, I found that once I reduced the permission for “Everyone” over the Hamilton folder from “Full Control” to “Modify” I stopped being able to connect to the database via System Configuration Editor. Restoring the “Full Control” permission restored the ability to connect to the database.

I got in touch with someone in IT and had the new group policy changed so that the “Everyone” entity has “Full Control” over C:\Program Files (x86)\Hamilton rather than “Modify” with the exception of C:\Program Files (x86)\Hamilton\Logfiles. For that folder the "BUILTIN\Users entity was only given “Read & Execute” permission. After updating the Group Policy on my test PC I can confirm that configured in this way I was able to connect to the database via System Configuration Editor.

However, I still can’t run methods. Trying to run a method containing only an initialize and user dialog step gives me the following error.

This error seems to indicate that it isn’t possible to create the .mdf and probably .ldf files in the log file folder. On this system HxRun.exe is elevated to local administrator whilst running and local administrators have “Full Control” permissions over the log file folder and so I don’t understand why this is happening. Indeed, HxRun.exe is able to create a .trc file in the log file folder for these errored runs. This is what leads me to wonder if there is another executable involved in creating the .mdf and .ldf files that also needs to be elevated to local admin. If I change the permission for “BUILTIN\Users” from “Read & Execute” to “Modify” the error goes away and the method runs without an issue. The .ldf and .mdf files are successfully created.

I’ve gone back over your original reply and all settings shown in SSMS match those in your screenshots.

Do you have any idea what could be causing this behaviour?

Many thanks again,
Will

[NickHealy_Hamilton]

Hi @WillEb

Thanks for the additional detail context - I think we are honing in on the issue.

HxRun.exe isn’t the software component that writes the SQL server database transaction logs and backup files to the Logfiles folder - it generates run traces, communication traces, run liquid databases (TADM data) and other supporting files.

The SQL server log files are generated by the Hamilton SQL server Windows service(s) that runs alongside run control when Vector database features are toggled to be active. I suspect you will need to elevate the security privileges of these services to the same level you promoted HxRun.exe in order for all required Logfiles logs to be generated as required by software.

I would navigate to the location highlighted below and modify the security permissions of the two indicates Hamilton SQL server service exe’s.

Hopefully this does the trick. Thanks.

-Nick

[Pascal]

I was wondering if there is an update to this matter.

The reason I am asking is that I am also in a regulated environment and the issue is that I need to provide users with the minimal amount of rights possible without impeding the Hamilton operation itself.

For now, Venus 6.3.2 is setup to have user groups, audit trail etc. enabled. The only issue I see here is that a user could be deleting Hamilton files from the hamilton folder. However, seeing that Venus needs to run under the logged in user, prevents me from simply revoking any rights from the user. I understand that checksum is my biggest line of defence here in which I can enable that and if a user manipulates a file, the method won’t run. The same for other files that are changed outside of Venus (Labware, liquidclasses etc). The only thing I need to find is a way for the user not to modify the logfiles and have read-only access to the logfile folder. Is there a way @WillEb or @NickHealy_Hamilton how to do this? It would satifsy our QA department

[WillEb]

Hi @Pascal

I did manage to solve this (mostly). The caveat is that I have only fixed the issue for the legacy executables (HxRun.exe, HxMethodCopy.exe). The project I was working on had very tight timelines and we were only deploying Venus 6.2.2 for a single instrument in the business at that time - and so I didn’t figure out how to apply this to the new GUI/version of Run Control.

These are the components that you need to put in place to achieve protection of logfiles but preservation of function for Venus.

Folder Access:
C:\Program Files (x86\HAMILTON\ - Full control for all users applied to that folder and all subfolders and files, disable allowing of inheritance from parent object to this object

C:\Program Files (x86\HAMILTON\LogFiles - Full control for SYSTEM and administrators, read and execute for BUILTIN\Users applied to that folder and all subfolders and files, disable allowing of inheritance from parent object to this object

Executable Elevation

Use a piece of security software such as PowerBroker, BeyondTrust Privilege Management or similar to add admin tokens to the following executables. This allows these programs to be run by users with their normal access levels (operator, method programmer etc.), but Windows sees a hidden flag on them and treats them as if they were run by a local administrator. That’s what allows them to write into the LogFiles folder. You can normally also a tick an option to prevent the permission propagating to child processes or Explorer windows - that stops users being able to use a loophole to gain local administrator access to a system via a File Open dialog.

C:\Program Files (x86)\Hamilton\bin\HxRun.exe
C:\Program Files (x86)\Hamilton\bin\HxMethodCopy.exe
C:\Program Files (x86)\Hamilton\bin\Hamilton.MethodCopy.exe
C:\Program Files (x86)\Hamilton\Bin\DesktopClient\Hamilton.VENUS.exe
C:\Program Files (x86)\Hamilton\bin\Hamilton.HxVectorDbManager.exe
C:\Program Files\Microsoft SQL Server\MSSQL15.HAMILTON\MSSQL\Binn\SQLAGENT.EXE
C:\Program Files\Microsoft SQL Server\MSSQL15.HAMILTON\MSSQL\Binn\sqlservr.exe

One limitation of this approach was that importing/exporting a method via Method Editor incurred at 60-70 second delay for an unknown reason - I couldn’t get around this. Therefore I added the admin token to HxMethodCopy.exe instead and made it our process to use that directly for import and export of methods.

Finally, I found that in my case, after login the services which run the Microsoft SQL Server actually started before my security software’s client. Therefore, SQL Server was already launched before the security software had a chance to apply the admin token to it - it gets applied when the software is launched only. Therefore I had to modify the MSSQL$HAMILTON and SQLTELEMETRY$HAMILTON services to have the “Automatic (Delayed Start)” startup type.

I think that was all that was required to get this sort of behaviour working. I think to get it working fully for the new GUI in Venus 6, it will just be a matter of finding all of the new executables involved and adding admin tokens to those also.

Good luck!
Will

[Pascal]

Thanks @WillEb highly appriciated