SQLite, AUTO_VACUUM and Windows ACLs

This article applies to Windows 7 32/64 and Windows Vista 32/64 with UAC active.

In January an enduser pointed out to me that every time he tried to use the Real Time BlackList tool in NetScanTools Pro, he got an SQLite error message about the database being 'read-only' - it could not be opened. The software was installed on Windows 7-64 bit and NetScanTools Pro is operating at 'asInvoker' privilege which is normally USER privileges.

After doing the usual tech support routines by checking file properties, I was stumped - until yesterday when I was able to duplicate it on two Windows 7 machines.

The SQLite database is copied into our own directory created at install time under c:\ProgramData which is the common user data area. The thought was that any account using the program would be able to access the database. That was the idea. It's not the only database we put in there and the others were opening fine so I set out to find out why.

The only thing different about this database is that it has the pragma "AUTO_VACUUM" set. It appears that with AUTO_VACUUM SQLite moves freed pages around within the tables. This requires write privileges. SQLite error messages should do more than simply state that the database is read-only by checking the file ACLs given the calling process account privileges then stating the incompatibility with the current AUTO_VACUUM state.

To see the file access privs on an account level, you have to go into our C:\ProgramData\NWPS\NetScanToolsPro common user directory and do an "icacls *.*" on the command line. You will see that indeed user level privileges (BUILTIN\Users) only have (I)(RX) - inherit, read, execute privileges while the other higher level accounts have (F) full privileges. Since AUTO_VACUUM requires write access to the database to make changes, it will not have the proper privs for a user level account. So, yes, opening the database fails (I just don't think the message is good enough).

So now, how to fix it. Recreating the database with AUTO_VACUUM off fixes it. But what if you need to write (as a USER) to the Real Time Blacklist database using the tool we provide to edit the database? You can't because the administrators group are the owner.

The solution is to change the directory and file ACLs. I did this by modifying the installer to call a function of my own design which applied FULL access privs (grfAccessPermissions=GENERIC_ALL) to grfInheritance=SUB_CONTAINERS_AND_OBJECTS_INHERIT at our NWPS\NetScanToolsPro directory level. If that is done and you do the icacls command, all files in that directory show the "Everyone:(I)(F)" which means that every account can fully access the files and that includes our SQLite database that we couldn't open. You have to use AllocateAndInitializeSid, SetEntriesInAcl, and SetNamedSecurityInfo so accomplish this. You have to do this in the installer because it is running at admin privileges.

To summarize, if you have a program running Windows 7 or Vista at USER level that needs to access an SQLite database with write privileges contained in the C:\ProgramData common user directory that was not created by your program - you've got a problem. And that problem is even worse if it has AUTO_VACUUM enabled. You have to modify the file access privileges to FULL control in order to allow SQLite to operate on the database correctly.

NetScanTools Pro 10.98.2 Released

On February 23, 2011 we released NetScanTools Pro 10.98.2 both installed and USB versions. Many of the changes have to do with privileges and will primarily affect Windows 7 and Vista users.

Here are the release notes.
-Improved messages that show if writing to a registry location fails. They now suggest escalating the privileges by starting the program with 'Run as administrator'.
-All temporary snmp files are now removed on program exit.
-Improved handling of WinPcap interfaces where both IPv4 and IPv6 addresses are bound to the interface. Affects several programs.
-Internal changes to DNS Tools resolver.
-Port Scanner and NetScanner (Ping Scan) now show warning messages if privileges are not sufficient to run UDP scan and Subnet Mask test respectively.
-Updated SQLite to version 3.7.4.
-Updated database files.

Enjoy!

Addressing Confusion

In my December 2010 newletter I talked about NetScanTools Pro 10.98.1 on Windows 7-64 bit. I talked about how the change to the manifest from 'require administrator' (which did not allow unescalated use on a User privileges account) to 'asInvoker' allowed User privileges accounts to run NetScanTools Pro without logging in as an administrator -- some business installations only allow user level privs for their employees. But the biproduct of that change was to disallow writing to HKEY_LOCAL_MACHINE (HKLM) on Windows 7-64 and possibly Vista as well. For security reasons, UAC only allows read-only privileges in HKLM when you are not an administrator process. The process (ie. NetScanTools Pro) must have elevate privs to administrator for UAC to allow writing to that part of the registry.

NetScanTools Pro 10 only writes to one specific portion of HKLM and it only does that when you complete your registration (Validate and Save) or if you change your Maintenance Plan expiration date or Email Address using About/Edit Maintenance Plan. If you try that on Windows 7-64 with normal administrator or user privs, you get an error that it cannot write to the registry. It does not write to HKLM in the normal course of program operation, registry writes are done to HKEY_CURRENT_USER (HKCU).

The next release 10.98.2 will address this by giving a much more descriptive message explaining that in order to complete the task, you have to exit and restart with 'run as administrator'. It will also have some other changes to address some things we recently learned about the SNMP toolset.

We are working on 10.98.2 and should have it done shortly.