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 and User Privileges

Yesterday an astute user pointed out that if you start NetScanTools Pro 10.98 on Windows 7 in a USER privileges account, the operating system asks that you login with an administrator account password to allow the program to run.

NOT GOOD! You should at least be able to run the program from that type of account without jumping through hoops. So I quickly traced the issue to the MANIFEST. You programmers out there know about this. In it were commands asking for higher privileges than we really need, so I think we are going to have a new minor, minor revision on Thursday. Beside fixing that little problem, the databases will all be updated with current information.

I assume this would affect anyone still using Windows Vista as well as Windows 7. The rest of you have little to concern yourselves with regarding this problem.

Kirk

Windows 7 RTM on MSDN

They posted it yesterday and I just confirmed it's there. Now the decision for my OS testing system: do I erase the Vista 64 that's on there now and replace it with Win7 x64? Should I try upgrading it? if I erase it all the Virtual machine OS's will be destroyed and I'll have to rebuild them -- or maybe not -- I can just save the virtual OS files onto a backup and reinstall Virtual PC and reload them. And does Microsoft's Virtual PC 2007 run on Windows 7? I know it can host it. Questions...

Windows 7

The announcement of the public release of the Windows 7 beta was a couple days ago and already news articles are calling Windows Vista "much-maligned" with users experiencing "many problems" with it. The push to get the next release of Windows out really comes as no surprise.

The end user acceptance of Vista seems low after two years. I say this based on visitor logs of our websites and also our own user polls during product registrations. XP is still the dominant operating system seen in our website logs at a rate of 75%-78% of all Windows OS visitors. Vista is running about 15%-20% with the other old Windows operating systems down in the noise. This data comes from two sites looking at the last couple months. This represents a small increase in the Vista numbers from what I reported in October and it does show a couple percentage points drop in the XP numbers. These kinds of numbers are probably behind the push to get Windows 7 out -- reminds me of Windows ME.

While I haven't actually got my hands on the Windows 7 beta yet, I do want to try it out soon. I hope it will load into MS Virtual PC 2007 for testing. Hopefully none of our software breaks this time and our code changes will be minimal -- there were so many code changes required for Vista.

Windows Operating System traffic on our sites

As we approach the 2nd anniversary of the release of Windows Vista, I couldn't help but notice that the traffic on our sites still show a huge margin of Windows XP users. Here is the raw data on two of our sites.

NetScanTools.com (10,700 hits)
On this site 93.4% of our hits are Windows users, 3.8% are Linux users and 2.3% are Mac users. Here is a breakdown of the Windows traffic:
77.8% Windows XP
17.2% Windows Vista
2.8% Windows Server 2003
2.2% Windows 2000
0.1% Windows 98
0.02% Window ME
and 1 person using NT4 (must be Boris)

nwpsw.com (600 hits)
On this site 92% of our hits are Windows users, 6.5% are Linux users and 1.2% are Mac users. Here is a breakdown of the Windows traffic:
81.1% Windows XP
14.6% Windows Vista
2.5% Windows Server 2003
1.8% Windows 2000

This data shows that people are not only still using XP, they are favoring XP. To be fair, there has been a couple of percentage points increase in Vista traffic since the first of 2008. I wonder what the market penetration was for XP at the two year mark? I just thought this was interesting.