Microsoft Access workgroup security appears to be robust because it uses a strong encryption algorithm. However, encryption remains secure only while the decryption key remains secure, and this is where Access/Jet is vulnerable.

How Access security works
The way Jet security works is that users, group, and password information is stored in a workgroup file (MDW), and permissions are saved in the database file (MDB) and mapped to the security IDs (SIDs) of the users or groups in the MDW file. For users to work with an Access database, they need file permissions on both the MDW and MDB file. Attackers can gain physical possession by simply copying the files and carting them off to hack away on at their leisure. No matter how superior the key is, if it's physically accessible, and the attackers have the skills and the motivation, they'll crack it.

Relying on Access security is like hiding your house key in your yard. If a thief gets over the garden wall and has unlimited time and a fervent desire to get into your house, he'll find the key, even if you've cleverly hidden it under a lawn dwarf behind dense bushes inside a maze. The key might be safe if you embedded it in concrete and buried it under the fountain, but then you wouldn't be able to use it yourself. There are already companies and products out there that can hand you an administrative user name and password from any MDW.

Foil Access attackers
Fortunately, there's a way to foil attackers by exploiting the dual MDW/MDB structure of Jet security. Secure your database normally using a development workgroup file, assigning permissions to your custom groups. Then create a different workgroup file for distribution. Duplicate the group names and Personal IDs (PIDs) to generate the same SIDs in the distribution MDW. An unauthorized user who tries to hack in by obtaining an administrative login and password won't get very far, because the Admins group in the distribution workgroup file has a different SID from the Admins group in the original workgroup. Only members of the Admins group in the original workgroup file can modify permissions and access protected data, and you keep the original workgroup file safely tucked away and out of reach of would-be hackers. The Access Security FAQ includes the steps to implement this strategy. It's available at http://support. microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp . See item number 33, "I want to create a remote site administrator able to administer the database and add user accounts but not alter permissions on database objects." However, because all users must have physical access to the MDW and MDB files, a hacker could still take a copy of your data and eventually decrypt it.

SQL Server security
Even when users don't have direct access to the files, as with SQL Server, you're still at risk if you use mixed-mode SQL Server authentication. The problem is that the undocumented internal function used to encrypt passwords is vulnerable. And an attacker who breaches SQL Server security and logs on as a system administrator can do more than just steal your data -- the attacker gains access to the operating system, the network, and anything else connected to the SQL Server box. There's no equivalent to the dual workgroup technique in SQL Server -- a system administrator has irrevocable permissions on all databases on that server instance and can execute extended stored procedures that reach out to the operating system. In this respect, Access is more secure than SQL Server -- at least it only exposes your data, not the entire network. The only way to protect SQL Server is to use Windows authentication only, and to run the SQL Server services under a least-privileged account, which places limits on some functionality.

The best defense
Security is a journey, not a destination. You should never assume that any product or technique is secure, because you can't possibly know what new attacks will become possible in the future. Many security vulnerabilities aren't published because attackers want to delay a fix, and manufacturers want to avoid negative publicity. There's an ongoing and unresolved debate over whether publishing security vulnerabilities encourages or helps prevent further attacks.

The most secure Jet database I ever heard of was in a military installation in an underground, nuclear-proof bunker, installed on a standalone computer with no Internet or network connections, and under armed guard 24 hours a day. However, that isn't a likely scenario for most of us. The best you can do -- whether you're working with Access/Jet or SQL Server -- is to keep up with service packs, which often contain security fixes, and to be realistic about possible threats. You should assume failure at some point, and never store truly sensitive data in any database that unauthorized users could conceivably penetrate.

And remember that most data loss occurs through social exploits, not technical ones -- your personnel procedures are usually more important than your encryption algorithms.