Microsoft Jet & Database Engine Essay, Research Paper

Introduction:

The purpose of this paper is to Define Microsoft Database and Jet Engine, explore its uses, and to a better understanding of the theories and principals related.

“Jet is the default database engine for Visual Basic as well as other Microsoft applications such as Microsoft Access, Microsoft Excel, Project, and Microsoft Foundation Classes (MFC). ” (Microsoft HOWTO: Identify, 1)

Microsoft Access and the Microsoft Jet Database Engine:

“Together, Microsoft Access and the Microsoft Jet database engine form a complete database management system (DBMS). Microsoft Access is responsible for the user interface and all the ways that users view, edit, and manipulate data through forms, queries, reports, and so forth. Microsoft Jet ? the data manager component of the DBMS ? retrieves data from and stores data in user and system databases.” (Microsoft, Access Architecture)

Microsoft Jet is a relational database engine that handles all database processing for Access. Microsoft Jet can also provide data to ODBC (Open Data Base Connectivity) client applications. “(Microsoft, Access Architecture, 23)

Microsoft Jet is made up of a set of dynamic-link libraries (DLLs):

*sum* Microsoft Jet DLL (Msjet35.dll)

“Msjet35.dll is the main program that evaluates and carries out requests for data. If the request is for native data ? data stored in the Microsoft Access Database (MDB) format ? Msjet35.dll also handles the reading and writing of the data. If the request involves non-native data, then Msjet35.dll makes calls to either the ODBC Driver Manager DLL (Odbc32.dll) or one of the external installable ISAM DLLs, as explained later in this section. ” (Microsoft, Access Architecture, 23)

*sum* Data Access Objects DLL (Dao350.dll)

“Dao350.dll is the Microsoft Jet component that provides a developer interface to Microsoft Jet. Data Access Objects (DAO) includes a rich, high-level set of objects that insulates developers from the physical details of reading and writing records. “(Microsoft, Access Architecture, 23)

*sum* Installable ISAM DLLs

“Microsoft Jet provides access to several external Indexed Sequential Access Method (ISAM) format files by using a series of installable DLL files referred to as installable ISAMs. Microsoft Jet supports the external ISAM formats shown in the following table.” (Microsoft, Access Architecture, 23)

ISAM format Supported by DLL

Xbase (dBASE and FoxPro) Msxbse35.dll

Paradox Mspdox35.dll

Lotus Msltus35.dll

Microsoft Excel Msexcl35.dll

Microsoft Exchange/Outlook Msexch35.dll

Text and HTML Mstext35.dll

“These DLLs handle the reading and writing of data stored in dBASE, FoxPro, Paradox, Lotus, Microsoft Excel, Microsoft Exchange, Microsoft Outlook, fixed-width text, delimited text, and HTML files.” (Microsoft, Access Architecture, 24)

Jet and Replication Objects:

“Microsoft Jet and Replication objects (JRO) allow you to add features to your application that are specific to the Microsoft Jet Database Engine. The Jet and Replication objects are based fundamentally on Microsoft Active Data Objects (ADO), because they are connected to an ADO Connection object. However, the Jet and Replication objects function only with Microsoft Jet databases.

With the Jet and Replication objects you can:” (Microsoft JRO, 1)

*sum* Create and synchronize database replica sets.

*sum* Compact a database, and specify options for the compacted database, such as passwords and encryption.

*sum* Refresh the memory cache by writing pending data changes to the database, and reading the most recent data from the database to memory.

The Jet Engine

“The Jet database engine is represented by Jet Engine objects. With Jet Engine objects, you can make a compacted copy of a Jet database and include options for encrypting data, changing database engine versions, passing user IDs and passwords, specifying the system database or registry information, setting or overriding locale-specific collating orders, removing relationships, and repairing damaged replicas. You can also force any pending changes to the data to be processed by completely flushing the cache of database operations.” (Microsoft JRO, 2)

Replicas

“Replicated databases are represented by Replica objects. With Replica objects, you create new replicated databases, define the options for a specific replica (full, partial, design master), synchronize the changes between two replicas, and determine conflicts that occur during synchronization.” (Microsoft JRO, 2)

Filters

“Partial replicas are created using Filter objects, which are collected in the Filter collection of a Replica object. Filter objects define criteria that limit the records that become replicated. Filters can be applied to tables or relationships in a partial replica.” (Microsoft JRO, 2)

Jet in WINS and DHCP:

“Microsoft Windows NT Server includes a utility, Jetpack.exe, that can be used to compact a Windows Internet Name Service (WINS) or Dynamic Host Configuration Protocol (DHCP) database. Microsoft recommends that you compact the WINS database whenever it approaches 30 MB. NOTE: Chapter 5 page 133 of the Microsoft Windows NT Server versions 3.5 and 3.51 TCP/IP manuals states that Compact.exe can be executed to compact the WINS database. This is not correct. The correct file name is Jetpack.exe.” (Microsoft How to Use, 1)

WINS and DHCP Preventive Maintenance:

“Take steps now to keep your NT 4.0 network running smoothly

If you are or ever were a user (i.e. victim) of Windows NT 3.5x’s Windows Internet Naming Service (WINS) and Dynamic Host Configuration Protocol (DHCP), you know that regular maintenance tasks such as periodic jetpacking of the database and checking of log files and event logs are crucial to keeping these important infrastructure services functioning. Even if your firm uses the best networking practices, NT 3.5x’s WINS and DHCP quirks probably periodically cause you stress.” (Mar-Elia, Darren, 1)

Jet Files:

“WINS and DHCP rely on Microsoft’s Jet database engine to store information about their services. WINS stores the records that clients register and the records it replicates from other WINS servers in a Jet database, which is usually in %systemroot%\system32\wins. WINS servers keep important configuration options that relate to their replication partners and their name registration behavior in the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\WINS registry subkey.” (Mar-Elia, Darren, 1-2)

“DHCP uses a Jet database to keep track of which IP addresses the server has leased. DHCP relies heavily on the Registry for storage of scope definitions (the ranges of IP addresses DHCP can distribute) and reservations (the address assignments DHCP hard-codes using machines’ media access control?MAC?addresses). DHCP stores its scope and configuration information in the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\ Services\DHCP-Server registry key. The DHCP database is in %systemroot%\system32\dhcp. WINS and DHCP servers hold several files that relate to each service’s database.” (Mar-Elia, Darren, 3)

“Wins.mdb or dhcp.mdb. The wins.mdb file is the main database file that holds records for WINS. Dhcp.mdb is the main database file that holds DHCP records.

J*.log. Within the DHCP or WINS directory, you’ll find at least two J*.log files, which are transaction files for the database. When you change a WINS or DHCP database, the software writes your change to the current log file. You can verify this fact by viewing the WINS or DHCP database directory. The directory’s files with the most recent modification dates are the log files; the database files reflect the date and time when the WINS or DHCP service last terminated gracefully (i.e. via a shutdown or termination of the service).” (Mar-Elia, Darren, 3)

When you first install these services, you’ll probably see a J50.log file. J50.log is the current transaction log for the database. Each log holds a maximum of 1MB. When the current log fills, the DHCP or WINS service copies it to a backup log. The software usually calls these backup logs J50xxxxx.log, in which xxxxx is a sequential hexadecimal number. DHCP usually generates more frequent log file backups than WINS generates, because DHCP generates a new series of logs (and backups) every time it backs up the DHCP database. By default, DHCP backs up its database hourly. (Mar-Elia, Darren, 3)

J*.chk. You use J*.chk to checkpoint the log (i.e., to keep track of which J*.log transactions DHCP or WINS has written to the database). J*.chk is useful when the database needs to back out of a set of transactions during a recovery, because the file keeps track of which transactions the software has committed since it last wrote to the database. (Mar-Elia, Darren, 4)

Care of the Jet Databases:

“DHCP and WINS Jet databases can be sources of much grief for NT administrators. As DHCP and WINS databases grow, they become less efficient at servicing requests and more susceptible to corruption. If a database becomes corrupted, the WINS or DHCP server simply stops servicing requests. You can usually find evidence of database corruption within NT’s system event log, which you use Event Viewer to view. When a crucial event log error suggests that your database is corrupted, your choices for recovery are limited and probably painful.

WINS is especially problematic, because it is very active in medium to large environments. During business hours, WINS servers are always working with the database. If the servers aren’t registering or renewing client computers’ name registrations, they’re replying to name queries. And every time users log on to a machine that uses WINS, the system registers their username with that machine in WINS. In addition, large NT infrastructures might contain several WINS servers that replicate their databases to one another. As a result of all this activity, WINS databases are subject to fragmentation. As WINS writes and rewrites records to the database, wins.mdb often becomes fragmented and actually grows in size as a result of the fragmentation. Over time, the database can become quite large if you don’t care for it properly.” (Mar-Elia, Darren, 5)

DAO/Jet

DAO is the COM interface to the Jet database engine and with DAO 3.5, an interface to RDO 2.0. Basically, DAO/Jet is primarily designed to access ISAM (The Indexed Sequential Access Method ) databases as it supports most common ISAM data access techniques. Therefore DAO/Jet is the solution when it comes to accessing native Jet (.MDB) or ISAM data sources such as Btrieve, FoxPro, Paradox, and dBase. (Microsoft, Choosing, 7)

“Using the Jet API from Visual Basic is uncalled for (so to speak) as the DAO interface is far, far easier to use and much better suited for Visual Basic development. However, when working with remote data sources, the Jet database engine consumes more resources than the API interfaces or RDO. While DAO/Jet is capable of accessing ODBC data sources, it has limited stored procedure management and multiple result set functionality. It cannot build queries using server-side cursors or leverage much of the potential of these remote DBMS systems.” (Microsoft, Choosing, 7)

ISAM

“To use Data Access Objects (DAO) methods and objects with Visual Basic for Applications in Access to link or import data from Exchange or the Outlook desktop information manager, you must have the Microsoft Jet Exchange installable indexed sequential access method (ISAM) (Msexch35.dll) installed. The Jet Exchange installable ISAM is a driver that allows you to access data that is stored remotely on a Microsoft Exchange Server or that is stored locally in offline folder (.ost), personal folder (.pst), or personal address book (.pab) files.

Note: If you have set up Outlook to use an offline folder file, the offline folder file is a file stored on your hard disk that “mirrors” the folder structure and data stored on your Microsoft Exchange Server. Data from your Microsoft Exchange Server is copied into the offline folder file whenever you synchronize folders (manually or at a scheduled time). When you start Outlook, it gives you the option to connect to your server or work offline. If you choose to connect, you are viewing data directly from the server; if you choose to work offline, you are viewing data from the offline folder.” (Microsoft Accessing, 1)

“For this reason, how you are logged on to Outlook determines whether the Microsoft Jet Exchange installable ISAM reads data from the offline folder file or from the Microsoft Exchange Server. For example, if you initially define a link to your Inbox folder while you are working offline in Outlook, the Microsoft Jet Exchange installable ISAM reads the data from your offline folder file. However, if you open the same database later while Outlook is connected to your Microsoft Exchange Server, the data will be read from the Inbox folder on the server.

Using the Microsoft Jet Exchange installable ISAM to access Exchange and Outlook data is primarily useful for reading information from message folders, public folders, address books, and other Outlook items. You can’t modify existing items. And, though it is possible to write new items to Exchange and Outlook message stores, not all fields are available or up datable, which limits your ability to create some items. If you need more flexibility when working with Outlook objects from Microsoft Access, you should instead use automation interfaces to work with Outlook.” (Microsoft Accessing, 1-2)

Installing the Microsoft Jet Exchange Installable ISAM Driver

Before you can use DAO to gain access to Microsoft Exchange Client or Outlook data, you must install the Microsoft Jet Exchange installable ISAM. Although you can install the Microsoft Jet Exchange installable ISAM using the Dataacc.exe file that is included in the Microsoft Office 97 Value Pack, that version of the driver does not register itself and has certain limitations. To install the most current version of the driver, download and run Wzmapi80.exe to install the Microsoft Exchange and Outlook Wizard. This installs and registers the most current version of Msexch35.dll so you can use it from DAO, and also installs the Microsoft Exchange and Outlook Wizard so you can import and link Microsoft Exchange and Outlook data using the Microsoft Access user interface.

Note: You must have a copy of Microsoft Access 97 installed before you can install the Microsoft Exchange and Outlook Wizard. The Setup program does not provide a way to install just the Microsoft Jet Exchange driver; you must install the wizard components as well. (Microsoft Accessing, 2)

Installing MSDE

“Microsoft Database Engine (MSDE) doesn’t install with the basic setup for Office 2000 and Access 2000. However, you can install MSDE from Office 2000, CD 1. Run setupsql.exe from the \sql\x86\setup folder. Note that you can’t run SQL Server and MSDE on the same computer. MSDE operates as a database server for a local workgroup in a peer-to-peer network or on a Windows NT computer. On Windows NT computers, MSDE supports either Windows NT integrated security or SQL Server security. Applications for MSDE on a Windows 9x computer exclusively support SQL Server security.” (Dobson, 1-2)

“If you install MSDE on a Windows 9x computer and attempt to link to it from a Windows NT computer, your users might experience a network failure. One cause of this failure is that the NT computer attempts to link to the server from Named Pipes, whereas the MSDE installation procedure for a Windows 9x computer installs multiprotocol and TCP/IP settings. To fix this problem, use the Client Network Utility on the NT computer to revise network client settings to match those on the Windows 9x computer running MSDE.” (Dobson, 2)

“A valid reason to install MSDE even when you use Access projects exclusively with MS-SQL Servers is to fix a known problem in the operation of the Tools, Security, Database Security command. This command enables login and user ID creation and permission and role assignments from a GUI that mimics Enterprise Manager’s Action, New Login command. Unfortunately, MSDE won’t operate from Access 2000 unless the workstation that MSDE is running on has MSDE installed on it. Therefore, to set security, even for SQL Server databases from Access projects, you need to install MSDE.” (Dobson, 2)

“Although MSDE has a database technology similar to SQL Server 7.0, it has several distinct limitations. First, Microsoft optimized MSDE for a handful of users. Microsoft asserts that performance starts to degrade after five users. You’ll want to check your MSDE applications for performance in real situations to assess whether any performance degradation is within acceptable limits for your needs. Second, the maximum size of an MSDE database is 2GB, instead of the essentially unlimited size of a SQL Server database. Third, SQL Server has many features that Access doesn’t have. For example, a MSDE database can’t serve as a replication publisher for transactional replication. And, MSDE can’t act as an OLAP or English Query server.” (Dobson, 3)

Access Security and Passwords

MS Access features three independent types of passwords:

*sum* Database passwords

“Database passwords were introduced in the times of MS Access 95 as a new, easy to use security feature. Setting up a single password that controls who can open a database is both faster and easier compared to setting up a complicated user-level security. If there’s a database password set, all users must enter that password to open the database.” (Robinson, 1)

“The database password is encrypted and stored in the header of a database file. In Access versions 95 and 97 encryption was insecure and a single byte change in the header was enough to remove the password.” (Robinson, 1)

“If a database header gets corrupted (usually happens when a databases is opened and saved using a text editor, MS Word is a popular choice) Access might erroneously report the database password protected instead of damaged.” (Robinson, 1)

*sum* Security Accounts Passwords

“The most powerful yet complex way to secure a database is to set up user-level security. If user-level security is defined, the members of a workgroup are recorded in user and group accounts that are stored in a Microsoft Access workgroup information file (also known as System Database).” (Robinson, 1)

“Each user and group account has its own unique Access Security ID (SID) that is created from user or group name, Personal ID and Workgroup ID. There are also some predefined SIDs that remain the same for all the Workgroups: ‘Admin’ and ‘guest’ accounts, and ‘Guests’, ‘Users’ groups. Each workgroup information file contains MSysAccounts table that stores all user and group account names, encrypted user passwords and SIDs.” (Robinson, 2)

“Access uses SIDs to check user’s permissions. Each database has MSysACEs table (both Hidden and System, not visible by default) with permissions information for all the objects in the database. In the table there’s a list of SID/applicable permissions pairs for each database object. Before a user attempts any action on a database object MS Access (or Jet Engine to be exact) scans MSysACEs table using current user SID (and all the SIDs of user’s groups) to verify permissions for the action.” (Robinson, 2)

Update Patch

“Microsoft has released an updated patch that eliminates security vulnerabilities in the Microsoft Jet database engine. A patch originally was released in August 1999, but an additional variant of one vulnerability, the text I-ISAM vulnerability, was subsequently discovered. The new variant could allow a database query to delete files on a user’s computer. This bulletin has been re-released to discuss the vulnerabilities in their entirety.” (InfiniSource, 1)

“The vulnerabilities in total could affect any application that runs atop Jet, and could allow a database query to take virtually any action on a user’s computer. Microsoft recommends that all customers who are running applications that use Jet, especially users of Microsoft Office 97 and Office 2000, install the patch. Customers who applied the original patch should apply the new one to ensure that they are fully protected against all variants. Customers who did not previously apply the patch need only apply the new version. Jet is a database engine used by Microsoft products such as Microsoft Office97 and Office2000.” (InfiniSource, 2)

Two vulnerabilities exist in Jet:

*sum* “The VBA Shell vulnerability, which affects all versions of Jet except Jet 4.0. An operating system command embedded within a database query could be executed when the query is processed. This would allow a spreadsheet, database, or other application file that contained such a query to take virtually any action on the user’s computer when the query was executed.” (InfiniSource, 2)

*sum* “The Text I-ISAM vulnerability, which affects all versions of Jet. Jet provides a way to modify the contents of text files, as a way of allowing data exchange between it and other systems. However, a malicious user could use this capability to modify system files via a database query. The original patch for this vulnerability allowed “drop table” operations to be used, which could allow files on the user’s computer to be deleted; the new patch eliminates this variant. “(InfiniSource, 2)

“Microsoft Office uses the Jet engine, and Office users are particularly at risk from these vulnerabilities. (The VBA Shell vulnerability affects all versions of Office prior to Office2000, and also affects one member of the Office2000 suite, Access2000. The Text I-ISAM vulnerability affects all versions of Office). The vulnerabilities are an especially serious threat to Office users for three reasons:” (InfiniSource, 3)

1. Scenarios for exploiting these vulnerabilities via Office documents are publicly known.

2. The ubiquity of Office would make it an attractive target for mounting attacks via these vulnerabilities.

3. The ability of Office documents to perform Document Object Hosting would permit users to be attacked simply by visiting a malicious user’s Web site.

“Microsoft Jet also is used by several other Microsoft products, as well as many third party applications. However, the ability to exploit this vulnerability through these products is highly dependent on the specific application. Although Microsoft has not identified a means of exploiting these vulnerabilities through any Microsoft products except Office, we recommend that all customers who have Microsoft Jet installed on their computer update it. This will ensure that they are protected against any possible attacks that may be developed.

Affected Software Versions “(InfiniSource, 3)

*sum* Microsoft Jet, all versions Note: Jet serves as the database engine for a number of Microsoft products, including but not limited to:

*sum* Microsoft Office

*sum* Microsoft Visual Studio

*sum* Microsoft Publisher

*sum* Microsoft Streets & Trips

“Jet also serves as the database engine for many third-party software products. The patch does not require any change to any of the applications that use Jet; instead, it operates directly on the Jet database engine and restores proper functionality to it. “(InfiniSource, 3)

What is In the Future?

“Developers and architects designing new applications don?t want to get half way through the implementation phase only to discover that a new Microsoft-promoted technology solves their problems with less code and with better functionality. With this in mind, consider that ADO are certain to play a very important role in future data access strategies. As such ADO should always be considered before committing to any long-term strategy. While ADO is still under development, we know that it will eventually have all of RDO (Remote Data Object) 2.0 ?s functionality. Much of that work is expected to be done in the next few quarters. ” (Microsoft Choosing, 9)

“So, if a new design is underway, consider ADO. If its current implementation is adequate, given the state of the software as described below, then use ADO. If, however, it falls short and you can?t wait, use RDO for relational data sources and DAO for ISAM data sources. The following diagram shows how ADO uses OLE DB (defines interfaces for accessing and manipulating all types of data), as RDO uses ODBC to access its data sources.” (Microsoft Choosing, 10)

b91

Dobson, Rick. “MSDE Primer for Access 2000″.

SQL Server Magazine, January 2000

Accessed: 03/27/01 @ 10:54 pm.

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=7720

InfiniSource, Inc “Office ODBC Driver Vulnerability”

Oct. 9, 1999

Accessed 03/28/01 @ 10:32

http://www.infinisource.com/features/obdc-vulnerability.html

Mar-Elia, Darren “WINS and DHCP Preventive Maintenance”.

Windows 2000 Magazine, March 1999

Accessed:03/27/01 @ 1:05 am.

http://www.winntmag.com/Articles/Index.cfm?ArticleID=4872&SearchStrin

g=wins

Microsoft Corporation “Accessing Microsoft Exchange and Outlook Data Using

Visual Basic”

MSDN Library, February 1997

Accessed: 03/25/01 @ 11:02 pm.

http://msdn.microsoft.com/library/techart/olexcoutlk.htm

Microsoft Corporation “Choosing the Right Visual Basic Data Access Interface”

MSDN Library, April 8, 1997

Accessed: 03/26/01 @12:26 am.

http://msdn.microsoft.com/library/backgrnd/html/msdn_rightvb.htm

Microsoft Corporation “HOWTO: Identify the Jet Database Engine Components”

Microsoft Product Support Services, January 9 2001

Accessed: 03/25/01 @ 11:53 pm.

http://support.microsoft.com/support/kb/articles/Q141/7/96.asp

Microsoft Corporation “How to Use Jetpack.exe to Compact a WINS or DHCP

Database” Microsoft Product Support Services, March 22, 2001

Accessed: 03/25/01 @ 11:37 pm.

http://support.microsoft.com/support/kb/articles/q145/8/81.asp

Microsoft Corporation “JRO Version 2.5″

Platform SDK, 2000

Accessed: 03/25/01 @ 11:27 pm.

http://msdn.microsoft.com/library/psdk/msjro/JROS9h4j.htm

Microsoft Corporation “Microsoft Access Architecture”

Microsoft Office Resource Kit, 1998

Accessed: 03/24/01 @ 12:14 am.

http://www.microsoft.com/OFFICE/ORK/035/035.htm

Robinson, Gary. “Access Security And Passwords” The Toolshed

Accessed 03/24/01 @ 10:18 pm.

http://www.vb123.com/toolshed/00_accvb/accesssecurity.htm