Access
From Just Solve the File Format Problem
(Difference between revisions)
Ross-spencer (Talk | contribs) m (Added extra context to that last link.) |
(→Sample Files) |
||
| (24 intermediate revisions by 9 users not shown) | |||
| Line 2: | Line 2: | ||
| formattype=electronic | | formattype=electronic | ||
| subcat=Databases | | subcat=Databases | ||
| − | | extensions={{ext|mdb}} | + | | extensions={{ext|mdb}}, {{ext|mde}}, {{ext|accdb}}, {{ext|accde}} |
| − | | pronom={{PRONOM|x-fmt/66}} | + | | pronom={{PRONOM|x-fmt/66}}, {{PRONOM|x-fmt/238}}, {{PRONOM|x-fmt/239}}, {{PRONOM|x-fmt/240}}, {{PRONOM|x-fmt/241}}, {{PRONOM|x-fmt/275}} |
| + | |locfdd={{LoCFDD|fdd000462}}, {{LoCFDD|fdd000463}} | ||
| + | |wikidata={{wikidata|Q1931564}} | ||
| released=1992 | | released=1992 | ||
}} | }} | ||
| − | '''Access''' released by '''Microsoft''' also known as '''Jet DB''' is a standalone database that forms part of the Microsoft Office Suite of productivity tools. It was first released in 1992. | + | '''Access''' released by '''Microsoft''' also known as '''Jet DB''' is a standalone database that forms part of the [[Microsoft Office]] Suite of productivity tools. It was first released in 1992. |
Access databases are composed of multiple page types. The length of these pages depends on the version of the Jet Database employed by the specific version of Microsoft Access. The first page of Access 95/97 and Access 2000/2003 databases is always a '''Database Definition Page'''. | Access databases are composed of multiple page types. The length of these pages depends on the version of the Jet Database employed by the specific version of Microsoft Access. The first page of Access 95/97 and Access 2000/2003 databases is always a '''Database Definition Page'''. | ||
| + | |||
| + | == Microsoft Access 1.0, 1.1, 2.0 == | ||
| + | |||
| + | Databases created in version 1.0 have a size limit of 128 megabytes, while databases in versions 1.1 and 2.0 have a size limit of one gigabyte. | ||
| + | |||
| + | Identification of version can be found at offset 1034 (0x40A). | ||
| + | * Access 1.0 - 0x07 | ||
| + | * Access 1.1 - 0x08 | ||
| + | * Access 2.0 - 0x09 | ||
| + | |||
| + | The last version of Access which can open these versions is Access 2007.<ref>https://support.microsoft.com/en-us/office/import-access-2-0-and-access-95-databases-into-current-versions-2e9d8851-101d-4407-a881-65d06bb12aa7</ref> | ||
== Microsoft Access 95/97 - Jet 3 Database == | == Microsoft Access 95/97 - Jet 3 Database == | ||
| Line 18: | Line 31: | ||
Microsoft Access 2000/2003 utilizes the Jet 4 database engine. The page size of the Jet4 database engine is '''4096''' bytes. | Microsoft Access 2000/2003 utilizes the Jet 4 database engine. The page size of the Jet4 database engine is '''4096''' bytes. | ||
| + | |||
| + | == Microsoft Access 2007 - Jet ACE Database == | ||
| + | |||
| + | Microsoft Access 2007+ utilizes the Jet ACE (Access database engine).<ref>https://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine</ref> | ||
| + | |||
| + | == Additional Formats == | ||
| + | Microsoft Access has additional formats and extensions.<ref>https://en.wikipedia.org/wiki/Microsoft_Access#File_extensions</ref> One being the MDE format which is a "special extension of the standard Microsoft Access MDB format, and is used to distribute an application."<ref>https://www.loc.gov/preservation/digital/formats/fdd/fdd000462.shtml#notes</ref> An MDE is a compiled read-only format used to distribute databases which cannot be edited. MDE format was replaced with the ACCDE format with Access 2007. | ||
| + | |||
| + | Another being the Snapshot report ([[SNP]]) format. | ||
== Internal structure of the format == | == Internal structure of the format == | ||
| Line 25: | Line 47: | ||
The version and build number of Microsoft Access used to create the file is stored in the format. These two values are stored within a [http://jabakobob.net/mdb/data-page.html LVAL Data Page] and deeper into that structure a [http://jabakobob.net/mdb/lvprop.html LvProp column] - part of the MSysObjects table. It is possible to extract this information from here alone, the complete MSysObject table definition is always the second page of the database (zero-based index). | The version and build number of Microsoft Access used to create the file is stored in the format. These two values are stored within a [http://jabakobob.net/mdb/data-page.html LVAL Data Page] and deeper into that structure a [http://jabakobob.net/mdb/lvprop.html LvProp column] - part of the MSysObjects table. It is possible to extract this information from here alone, the complete MSysObject table definition is always the second page of the database (zero-based index). | ||
| − | The block of data at offset 0x18 running for 126 bytes (Jet 3) and 128+ bytes (Jet 4) represents RC4 encrypted data. It is thought this uses the key: '''0x6b39dac7'''. | + | The block of data at offset 0x18 running for 126 bytes (Jet 3) and 128+ bytes (Jet 4) represents [[RC4]] encrypted data. It is thought this uses the key: '''0x6b39dac7'''. |
== Database Password == | == Database Password == | ||
| Line 35: | Line 57: | ||
'''Jet 3 Password''' | '''Jet 3 Password''' | ||
| − | XOR-ing the byte string running from 0x42 to 0x56 in a non-password protected database with that of a password protected database will reveal the password in plain text. | + | [[Exclusive Or|XOR-ing]] the byte string running from 0x42 to 0x56 in a non-password protected database with that of a password protected database will reveal the password in plain text. |
Studying the Maxsoft tool under [http://fileformats.archiveteam.org/wiki/Access#Password_Tools Password Tools] will help users to understand how Access databases are protected. | Studying the Maxsoft tool under [http://fileformats.archiveteam.org/wiki/Access#Password_Tools Password Tools] will help users to understand how Access databases are protected. | ||
| Line 41: | Line 63: | ||
'''Jet 4 Password''' | '''Jet 4 Password''' | ||
| − | The Jet 4 password can be revealed similarly by XOR-ing the byte string running from 0x42 to 0x68. If after the XOR operation if you haven't an ASCII character, that is, you have a value greater than 0xFF you need to XOR that value again with a further key value (byte) at offset 0x54. | + | The Jet 4 password can be revealed similarly by [[Exclusive Or|XOR-ing]] the byte string running from 0x42 to 0x68. If after the XOR operation if you haven't an ASCII character, that is, you have a value greater than 0xFF you need to XOR that value again with a further key value (byte) at offset 0x54. |
Studying the Maxsoft tool under [http://fileformats.archiveteam.org/wiki/Access#Password_Tools Password Tools] will help users to understand how Access databases are protected. | Studying the Maxsoft tool under [http://fileformats.archiveteam.org/wiki/Access#Password_Tools Password Tools] will help users to understand how Access databases are protected. | ||
| Line 47: | Line 69: | ||
== Jet DB and Access Build Numbers == | == Jet DB and Access Build Numbers == | ||
| − | The following table shows that the version of the Jet Database '''format''' remained fairly consistent between versions of Access: | + | The following table shows that the version of the Jet Database '''format''' remained fairly consistent between versions of Access<ref>https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/database-version-property-dao</ref>: |
Client Application Jet Engine Used Recommended Jet DB Format | Client Application Jet Engine Used Recommended Jet DB Format | ||
| Line 56: | Line 78: | ||
Access 2000 Jet 4.0 Jet 4.0 | Access 2000 Jet 4.0 Jet 4.0 | ||
Access 2002 Jet 4.0 Jet 4.0 | Access 2002 Jet 4.0 Jet 4.0 | ||
| + | Access 2007 ACE 12 | ||
| + | Access 2010 ACE 14 | ||
| + | Access 2013 ACE 15 | ||
| + | Access 2016 ACE 16 | ||
| − | The controller used to read and write the format by Access versions did change. The version of Access and its build number can help us to understand the version of the DLL used to interact with the format. The version and build number is embedded in the MDB objects (as described as part of its internal structure above) so can be easily extracted. Build numbers and DLL versions are described below ([http://support.microsoft.com/kb/248710 KB Article 248710] and [http://support.microsoft.com/kb/255275 KB Article 255275] and [http://support.microsoft.com/kb/291331 KB Article 291331]): | + | The controller used to read and write the format by Access versions did change. The version of Access and its build number can help us to understand the version of the DLL used to interact with the format. The version and build number is embedded in the MDB objects (as described as part of its internal structure above) so can be easily extracted. Build numbers and DLL versions are described below ([https://web.archive.org/web/20110414065858/http://support.microsoft.com/kb/248710 KB Article 248710] and [https://web.archive.org/web/20110303093732/http://support.microsoft.com/kb/255275 KB Article 255275] and [https://web.archive.org/web/20110222004849/http://support.microsoft.com/kb/291331 KB Article 291331]): |
Version File Version Patches | Version File Version Patches | ||
| Line 74: | Line 100: | ||
Access 2002 10.0.6501.0 SP-3 | Access 2002 10.0.6501.0 SP-3 | ||
| − | Access Version linked with MSJet35.dll ([http://support.microsoft.com/kb/248710 KB Article 248710]): | + | Access Version linked with MSJet35.dll ([https://web.archive.org/web/20110414065858/http://support.microsoft.com/kb/248710 KB Article 248710]): |
Version DLL Version | Version DLL Version | ||
| Line 82: | Line 108: | ||
Access 97 SR-2 and Jet35Upd.exe (3rd rel) 3.51.2026.0 | Access 97 SR-2 and Jet35Upd.exe (3rd rel) 3.51.2026.0 | ||
| − | The following table from [http://support.microsoft.com/kb/178880 KB Article 178880] describes backward compatibility between Jet DB engines and MDB version: | + | The following table from [http://web.archive.org/web/20111115230803/http://support.microsoft.com/kb/178880 KB Article 178880] describes backward compatibility between Jet DB engines and MDB version: |
---------------------------------------------- | ---------------------------------------------- | ||
| Line 94: | Line 120: | ||
3.0 N N N N Y Y | 3.0 N N N N Y Y | ||
| − | '''Note:''' See the [http://support.microsoft.com/kb/178880 KB Article] for information on all DLL updates. | + | '''Note:''' See the [http://web.archive.org/web/20111115230803/http://support.microsoft.com/kb/178880 KB Article] for information on all DLL updates. |
== Specifications == | == Specifications == | ||
| Line 105: | Line 131: | ||
== Sample Files == | == Sample Files == | ||
| − | |||
* [http://office.microsoft.com/en-ca/templates/results.aspx?qu=access&queryid=64492973-33dd-48c8-a228-6ffdf681fad6&avg=zac Microsoft.com: Sample Database Templates] | * [http://office.microsoft.com/en-ca/templates/results.aspx?qu=access&queryid=64492973-33dd-48c8-a228-6ffdf681fad6&avg=zac Microsoft.com: Sample Database Templates] | ||
| − | |||
* [http://www.utterangel.com/UtterAngel/utterangel.aspx?cat=acc utterangel.com: Sample Access Databases and Applications] | * [http://www.utterangel.com/UtterAngel/utterangel.aspx?cat=acc utterangel.com: Sample Access Databases and Applications] | ||
| − | |||
* [https://isda.ncsa.uiuc.edu/NARA/CSR/php/search/formats.php?&cInput=mdb&srg=1&cName=default_extension&fid=866&fd=1&asc=1&num1=1 Conversion Software Registry: Sample Access Databases] | * [https://isda.ncsa.uiuc.edu/NARA/CSR/php/search/formats.php?&cInput=mdb&srg=1&cName=default_extension&fid=866&fd=1&asc=1&num1=1 Conversion Software Registry: Sample Access Databases] | ||
| + | * {{DexvertSamples|archive/accessDatabase}} | ||
| + | * [https://archive.org/download/que-using-microsoft-access-2000/Que-UsingMicrosoftAccess2000-1999.iso/ Access 2000 Sample File] | ||
== General Tools == | == General Tools == | ||
* [https://github.com/brianb/mdbtools Brian Bruns GutHub.com mdbtools] | * [https://github.com/brianb/mdbtools Brian Bruns GutHub.com mdbtools] | ||
| + | * [https://github.com/exponential-decay/MDBValidator MDBValidator: Tool to read and output the statistics of a Microsoft Access Database] | ||
== Password Tools == | == Password Tools == | ||
| − | * [ | + | * [https://github.com/Planet-Source-Code/hamza-chohan-access-database-password-viewer__1-73390 Planet Source Code (via GitHub) - Access Database Password Viewer] |
| − | * [ | + | * [https://github.com/Planet-Source-Code/rashid-w-haddadin-maxsoft-access-password-recovery__1-72736 Planet Source Code (via GitHub) - Maxsoft Access Password Recovery] |
== References == | == References == | ||
| Line 127: | Line 153: | ||
* [http://jabakobob.net/mdb/first-page.html Database definition and encryption key documentation] | * [http://jabakobob.net/mdb/first-page.html Database definition and encryption key documentation] | ||
* [http://www.everythingaccess.com/tutorials.asp?ID=Jet-MDB-security---under-the-hood EverythingAccess.com Jet MDB Security - Under The Hood] | * [http://www.everythingaccess.com/tutorials.asp?ID=Jet-MDB-security---under-the-hood EverythingAccess.com Jet MDB Security - Under The Hood] | ||
| + | * [https://archive.org/details/specialeditionus00roge/page/1182/mode/2up Understanding the MDB file Upgrade Process] | ||
| + | <references/> | ||
== See Also == | == See Also == | ||
| Line 138: | Line 166: | ||
* [http://tutorialsto.com/view/printview-3771.html MDB Password View Tutorial] | * [http://tutorialsto.com/view/printview-3771.html MDB Password View Tutorial] | ||
* [https://groups.google.com/forum/#!topic/pronom/_fz-eP0ge9o PRONOM Google Group Discussion about the distinction between Access versions in the PRONOM registry] | * [https://groups.google.com/forum/#!topic/pronom/_fz-eP0ge9o PRONOM Google Group Discussion about the distinction between Access versions in the PRONOM registry] | ||
| + | * [http://www.textfiles.com/programming/FORMATS/q88173.txt ODBC Setup for Access and SQL Server] | ||
| + | * [https://medium.com/young-coder/microsoft-access-the-zombie-database-software-that-wont-die-5b09e389c166 Microsoft Access: The Database Software That Won’t Die] | ||
| + | * [https://docs.microsoft.com/en-us/previous-versions//cc966378(v=technet.10)?redirectedfrom=MSDN Microsoft Jet Database Engine Programmer's Guide - Introduction] | ||
| + | |||
| + | [[Category:Microsoft]] | ||
Latest revision as of 17:37, 14 January 2024
Access released by Microsoft also known as Jet DB is a standalone database that forms part of the Microsoft Office Suite of productivity tools. It was first released in 1992.
Access databases are composed of multiple page types. The length of these pages depends on the version of the Jet Database employed by the specific version of Microsoft Access. The first page of Access 95/97 and Access 2000/2003 databases is always a Database Definition Page.
[edit] Microsoft Access 1.0, 1.1, 2.0
Databases created in version 1.0 have a size limit of 128 megabytes, while databases in versions 1.1 and 2.0 have a size limit of one gigabyte.
Identification of version can be found at offset 1034 (0x40A).
- Access 1.0 - 0x07
- Access 1.1 - 0x08
- Access 2.0 - 0x09
The last version of Access which can open these versions is Access 2007.[1]
[edit] Microsoft Access 95/97 - Jet 3 Database
Microsoft Access 95/97 utilizes the Jet3 database engine. The page size of the Jet3 database engine is 2048 bytes.
[edit] Microsoft Access 2000/2002 - Jet 4 Database
Microsoft Access 2000/2003 utilizes the Jet 4 database engine. The page size of the Jet4 database engine is 4096 bytes.
[edit] Microsoft Access 2007 - Jet ACE Database
Microsoft Access 2007+ utilizes the Jet ACE (Access database engine).[2]
[edit] Additional Formats
Microsoft Access has additional formats and extensions.[3] One being the MDE format which is a "special extension of the standard Microsoft Access MDB format, and is used to distribute an application."[4] An MDE is a compiled read-only format used to distribute databases which cannot be edited. MDE format was replaced with the ACCDE format with Access 2007.
Another being the Snapshot report (SNP) format.
[edit] Internal structure of the format
The Jet DB version can be found in the file header by reading the 4 byte little-endian integer at offset 0x13. Jet3 databases have a value of 0. Jet 4 databases have a value of 1.
The version and build number of Microsoft Access used to create the file is stored in the format. These two values are stored within a LVAL Data Page and deeper into that structure a LvProp column - part of the MSysObjects table. It is possible to extract this information from here alone, the complete MSysObject table definition is always the second page of the database (zero-based index).
The block of data at offset 0x18 running for 126 bytes (Jet 3) and 128+ bytes (Jet 4) represents RC4 encrypted data. It is thought this uses the key: 0x6b39dac7.
[edit] Database Password
Note: The information under this section should only be used by owners and custodians of databases where the password has been misplaced or lost and not for any illegal or illegitimate premises.
Among other techniques Jet Databases employ a simple database password. The password field is 0x42 bytes into the file. Jet 3 database password fields are 20 bytes in length, Jet 4 are 40 bytes in length.
Jet 3 Password
XOR-ing the byte string running from 0x42 to 0x56 in a non-password protected database with that of a password protected database will reveal the password in plain text.
Studying the Maxsoft tool under Password Tools will help users to understand how Access databases are protected.
Jet 4 Password
The Jet 4 password can be revealed similarly by XOR-ing the byte string running from 0x42 to 0x68. If after the XOR operation if you haven't an ASCII character, that is, you have a value greater than 0xFF you need to XOR that value again with a further key value (byte) at offset 0x54.
Studying the Maxsoft tool under Password Tools will help users to understand how Access databases are protected.
[edit] Jet DB and Access Build Numbers
The following table shows that the version of the Jet Database format remained fairly consistent between versions of Access[5]:
Client Application Jet Engine Used Recommended Jet DB Format ------------------------- --------------- ------------------------- Access 2.0 Jet 2.0 Jet 2.0 Access 95 Jet 3.0 Jet 3.0 Access 97 Jet 3.5 Jet 3.0 Access 2000 Jet 4.0 Jet 4.0 Access 2002 Jet 4.0 Jet 4.0 Access 2007 ACE 12 Access 2010 ACE 14 Access 2013 ACE 15 Access 2016 ACE 16
The controller used to read and write the format by Access versions did change. The version of Access and its build number can help us to understand the version of the DLL used to interact with the format. The version and build number is embedded in the MDB objects (as described as part of its internal structure above) so can be easily extracted. Build numbers and DLL versions are described below (KB Article 248710 and KB Article 255275 and KB Article 291331):
Version File Version Patches -------------- ------------ ------- Access 95 7.0 Unknown Access 97 8.0.3512 n/a Access 97 SR-1 8.0.4122 n/a Access 97 SR-2 8.0.5903 SR-1 Access 2000 9.0.2720 Access 2000 9.0.3821 SR-1 Access 2000 9.0.4402 SR-1 Access 2000 9.0.6926 SP-3 Access 2002 10.0.2627.1 Access 2002 10.0.3409.0 SP-1 Access 2002 10.0.4302.0 SP-2 Access 2002 10.0.6501.0 SP-3
Access Version linked with MSJet35.dll (KB Article 248710):
Version DLL Version ------- ----------- Access 97 3.50.3428.0 Access 97 SR-1 3.50.3907.5 Access 97 SR-2 and Jet35Upd.exe (3rd rel) 3.51.2026.0
The following table from KB Article 178880 describes backward compatibility between Jet DB engines and MDB version:
---------------------------------------------- Jet Version 1.0 1.1 2.0 2.5 3.0 3.5 ---------------------------------------------- MDB Version ----------- 1.0 Y Y Y Y Y Y 1.1 N Y Y Y Y Y 2.0 N N Y Y Y Y 3.0 N N N N Y Y
Note: See the KB Article for information on all DLL updates.
[edit] Specifications
A formal specification from Microsoft has not been made available. The following links are attempts by third parties to reverse engineer and document the format:
[edit] Sample Files
- Microsoft.com: Sample Database Templates
- utterangel.com: Sample Access Databases and Applications
- Conversion Software Registry: Sample Access Databases
- dexvert samples — archive/accessDatabase
- Access 2000 Sample File
[edit] General Tools
- Brian Bruns GutHub.com mdbtools
- MDBValidator: Tool to read and output the statistics of a Microsoft Access Database
[edit] Password Tools
- Planet Source Code (via GitHub) - Access Database Password Viewer
- Planet Source Code (via GitHub) - Maxsoft Access Password Recovery
[edit] References
- Wikipedia:Microsoft_Access
- Reverse engineered documentation used by mdbtools on GitHub
- Database definition and encryption key documentation
- EverythingAccess.com Jet MDB Security - Under The Hood
- Understanding the MDB file Upgrade Process
- ↑ https://support.microsoft.com/en-us/office/import-access-2-0-and-access-95-databases-into-current-versions-2e9d8851-101d-4407-a881-65d06bb12aa7
- ↑ https://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine
- ↑ https://en.wikipedia.org/wiki/Microsoft_Access#File_extensions
- ↑ https://www.loc.gov/preservation/digital/formats/fdd/fdd000462.shtml#notes
- ↑ https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/database-version-property-dao
[edit] See Also
- Wikipedia:Microsoft_Jet_Database_Engine
- Developer resource discussing DLL versions and sample code to retrieve Access version
- Site collating version and build numbers from XP through to 2010
- Wikibooks Jet Database/Manipulation tools
- WebCheatSheet.com Microsoft Access Specifications and Limitations
- PC Welt Germany discussing vulnerabilities in Microsoft Money 99 which is built on Jet DB 3.0
- MDB Password View Tutorial
- PRONOM Google Group Discussion about the distinction between Access versions in the PRONOM registry
- ODBC Setup for Access and SQL Server
- Microsoft Access: The Database Software That Won’t Die
- Microsoft Jet Database Engine Programmer's Guide - Introduction
