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.

Tip: Find Root DNS for a Top Level Domain

Have you needed to find the root DNS servers for a particular top level domain?

Applies to: NetScanTools Pro, NetScanTools LE, NetScanTools Basic, NetScanTools Standard (obsolete).

It’s actually pretty easy, but how you enter the top level domain makes all the difference in the world. Examples of a top level domain are: .uk, .com, .nu, .se, .ca etc.

How to do it:

1. Switch to the DNS Tools – Core tool or on older software, the Name Server Lookup tool.
2. Enter the DNS you are going use under Advanced Query.
3. Select the NS record type, you may have to go into AQ Setup or Setup to do this.
4. Enter the top level extension in the IP/host/domain entry area. The correct method is to enter the extension followed by a period: ca. or uk. or com. –if you leave off the period or put the period before the extension, the query will fail.
5. Press NSLOOKUP.

Results will look like these two examples, the first for .ca (Canada) and the second for .se (Sweden):

[Start Query]
NSLOOKUP Starting Timestamp: 02/24/11 14:49:37
Command line equivalent: "nslookup -recurse -type=NS ca."
Looking up [ca.]

DNS Name: 4.2.2.2
IP Address: 4.2.2.2

Non-authoritative answer:
ca NS nameserver = f.ca-servers.ca
ca NS nameserver = e.ca-servers.ca
ca NS nameserver = j.ca-servers.ca
ca NS nameserver = a.ca-servers.ca
ca NS nameserver = c.ca-servers.ca
ca NS nameserver = m.ca-servers.ca
ca NS nameserver = l.ca-servers.ca
ca NS nameserver = z.ca-servers.ca
ca NS nameserver = k.ca-servers.ca
ca NS nameserver = sns-pb.isc.org
Server Response Time = 0.117 seconds
[End Query]

[Start Query]
NSLOOKUP Starting Timestamp: 02/24/11 14:54:34
Command line equivalent: "nslookup -recurse -type=NS se."
Looking up [se.]

DNS Name: 4.2.2.2
IP Address: 4.2.2.2

Non-authoritative answer:
se NS nameserver = d.ns.se
se NS nameserver = e.ns.se
se NS nameserver = c.ns.se
se NS nameserver = a.ns.se
se NS nameserver = b.ns.se
se NS nameserver = g.ns.se
se NS nameserver = h.ns.se
se NS nameserver = i.ns.se
se NS nameserver = f.ns.se
se NS nameserver = j.ns.se
Server Response Time = 0.430 seconds
[End Query]

What you see in the two examples above are the authoritative name servers for the root domains.

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!