Microsoft Data Access Components
Encyclopedia
Microsoft Data Access Components (commonly abbreviated MDAC; also known as Windows DAC) is a framework
of interrelated Microsoft
technologies that allows programmers a uniform and comprehensive way of developing applications that can access almost any data store. Its components include: ActiveX Data Objects
(ADO), OLE DB
, and Open Database Connectivity
(ODBC). There have been several deprecated components as well, such as the Microsoft Jet Database Engine
, MSDASQL (the OLE DB provider for ODBC), and Remote Data Services
(RDS). Some components have also become obsolete, such as the former Data Access Objects
API and Remote Data Objects
.
The first version of MDAC was released in August 1996. At that time Microsoft stated MDAC was more a concept than a stand-alone program and had no widespread distribution method. Later Microsoft released upgrades to MDAC as web-based redistributable packages. Eventually, later versions were integrated with Microsoft Windows
and Internet Explorer
, and in MDAC 2.8 SP1 they ceased offering MDAC as a redistributable package.
Throughout its history MDAC has been the subject of several security flaws, which led to attacks such as an escalated privileges attack, although the vulnerabilities were generally fixed in later versions and fairly promptly. The current version is 2.8 service pack
1, but the product has had many different versions and many of its components have been deprecated and replaced by newer Microsoft technologies. MDAC is now known as Windows DAC in Windows Vista
.
specific except for ODBC (which is available on several platforms). MDAC architecture may be viewed as three layers: a programming interface layer, consisting of ADO
and ADO.NET
, a database access layer developed by database vendors such as Oracle and Microsoft (OLE DB
, .NET managed providers and ODBC drivers), and the database itself. These component layers are all made available to applications through the MDAC API
. The Microsoft SQL Server
Network Library, a proprietary access method specific to Microsoft SQL Server, is also included in the MDAC. Developers of Windows applications are encouraged to use ADO or ADO.NET for data access, the benefit being that users of the application program are not constrained in their choice of database architecture except that it should be supported by MDAC. Naturally, developers still have the choice of writing applications which directly access OLE DB and ODBC.
operating system line's Win32 subsystem. The SQL Server Network Library is controlled through the use of a Client Network Utility, which is bundled with the SQL Server.
Each Net-Lib supported network protocol has a separate driver (not to be confused with a device driver
), and has support for a session layer
in its protocol stack. There are two general types of Net-Lib: the primary and the secondary. The primary Net-Lib consists of a Super Socket Net-Lib and the Shared Memory Net-Lib, while there are numerous secondary Net-Libs, including TCP/IP and named pipes network libraries (named pipes are a method of communicating with other processes via a system-persistent pipeline
that is given an identity). The Microsoft OLE DB Provider for SQL Server (SQLOLEDB) communicates via primary Net-Libs.
The Super Socket Net-Lib deals with inter-computer communications and coordinates the secondary Net-Libs — though the TCP/IP secondary Net-Lib is an exception in that it calls on the Windows Socket 2 API directly. The Banyan VINES
, AppleTalk
, ServerNet
, IPX/SPX
, Giganet, and RPC
Net-Libs were dropped from MDAC 2.5 onwards. The Network Library router had the job of managing all these protocols, however now only the named pipes secondary Net-Lib is managed by the router. The Super Socket Net-Lib also handles data encryption via the use of the Windows SSL API.
The Shared Memory Net-Lib, on the other hand, manages connections between multiple instances of SQL Server that exist on one computer. It uses a shared memory
area to communicate between the processes. This is inherently secure; there is no need for data encryption between instances of SQL Server that exist on one computer as the operating system does not allow any other process access to the instances' area of shared memory.
Net-Lib is also able to support the impersonation of a logged in user's security context for protocols that support authenticated connections (called trusted connections). This allows Net-Lib to provide an integrated logon authentication mechanism via the use of Windows Authentication. Windows Authentication is not supported on Windows 98 or Windows Me.
(also called OLEDB or OLE-DB) allows MDAC applications access to different types of ([data]) stores in a uniform manner. Microsoft has used this technology to separate the application from the data store that it needs to access. This was done because different applications need access to different types and sources of data, and do not necessarily need to know how to access technology-specific functionality. The technology is conceptually divided into consumers and providers. The consumers are the applications that need access to the data, and the provider is the software component that exposes an OLE DB interface through the use of the Component Object Model
(or COM).
OLE DB is the database access interface technology used by MDAC. OLE DB providers can be created to access such simple data stores as a text file or spreadsheet
, through to such complex databases as Oracle
and SQL Server
. However, because different data store technology can have different capabilities, OLE DB providers may not implement every possible interface available. The capabilities that are available are implemented through the use of COM objects—an OLE DB provider will map the data store technology's functionality to a particular COM interface. Microsoft calls the availability of an interface to be "provider-specific" as it may not be applicable depending on the database technology involved. Additionally, however, providers may also augment the capabilities of a data store; these capabilities are known as services in Microsoft parlance.
The Microsoft OLE DB Provider for SQL Server (SQLOLEDB) is the OLE DB provider that Microsoft provides for the Microsoft SQL Server
from version 6.5 upwards. According to Microsoft, SQLOLEDB will be "the primary focus of future MDAC feature enhancements [and] will be available on the 64-bit Windows operating system."
Universal data link files (or '.udl files') provide a common user interface for specifying connection attributes. A user can use a Data Link Properties dialog box
to save connection information in a .udl file as an alternative to directly specifying them by hand in a connection string. Consequently, these files provide a convenient level of indirection
. Additionally, the dialog box specifies a number of alternate OLE DB data providers for a variety of target applications.
(ODBC) is a native interface that is accessed through a programming language
(usually C
) that can make calls into a native library. In MDAC this interface is defined as a DLL. A separate module or driver is needed for each database that must be accessed. The functions in the ODBC API are implemented by these DBMS-specific drivers. The driver that Microsoft provides in MDAC is called the SQL Server ODBC Driver (SQLODBC), and (as the name implies) is designed for Microsoft's SQL Server. It supports SQL Server v6.5 and upwards. ODBC allows programs to use SQL
requests that will access databases without having to know the proprietary interfaces to the databases. It handles the SQL request and converts it into a request that the individual database system understands.
(ADO) is a high level programming interface to OLE DB. It uses a hierarchical object model to allow applications to programmatically create, retrieve, update and delete
data from sources supported by OLE DB. ADO consists of a series of hierarchical COM-based objects and collections, an object that acts as a container of many other objects. A programmer can directly access ADO objects to manipulate data, or can send an SQL
query to the database via several ADO mechanisms. ADO is made up of nine objects and four collections.
The collections are:
The objects are:
is the latest version of ADO (after ADO 2.8, now often referred to as ADO Classic) and is part of the MDAC 2.8 stack alongside classic ADO. It is built around Microsoft .NET. Though sometimes seen as an evolutionary step up from ADO, some fundamental structural changes were made by Microsoft. ADO.NET runs through a .NET Managed Provider, a modified version of an OLE DB provider specifically designed for .NET. The object structure is no longer built around a Recordset object. Instead a Dataset object is used to contain data gathered from multiple sources. This is transparent to the programmer. Unlike the old ADO Recordset, the Dataset's design allows for disconnected data. Conceptually, a Dataset object can be seen as a small in-memory relational database in its own right that allows for manipulation of data in any direction (a Recordset was a forward-only reader). In order to propagate changes back into the database, a Dataadapter object is used that transfers data from between the data source and the DataSet object. Cursor
s were also deprecated in ADO.NET, being replaced with a Datareader object, which is used to efficiently process a large list of results one record at a time without storing them.
used for Microsoft Access
, Microsoft Exchange Server
and Visual Basic
. Jet was part of a Relational Database Management System
(RDBMS) and offered a single interface
that other software could use to access Microsoft databases. Jet also provided support for security, referential integrity
, transaction
processing
, index
ing, record
and page locking, and data replication. In later versions of Jet, the engine was extended to run SQL
queries, store character data in Unicode
format, create views
, and allowed bi-directional replication with the Microsoft SQL Server. It has since been superseded by MSDE
.
There were three modules to Jet. One was the Native Jet ISAM Driver, a Jet dynamic link library (DLL) that could directly manipulate Microsoft Access database files (MDB), which was a modified form of an Indexed Sequential Access Method (ISAM) database. Another one of the modules were the ISAM Drivers, DLLs that allowed access to ISAM databases, among them being Xbase
, Paradox
, Btrieve
and FoxPro
files. The final module was the Data Access Objects (DAO) DLL, DAO
allowed programmers access to the Jet engine. It was basically an object-oriented data language used by Access Basic and Visual Basic
application developers to access Jet.
Similarly, the Microsoft Jet OLE DB Provider and Replication Objects (JRO) which allowed replication between Jet data sources was removed from MDAC 2.6
access to databases via any ODBC driver. There were several OLE-DB providers supplied by Microsoft (providers available were for the Indexing Service, Active Directory
, Jet, SQL Server, Oracle and Internet Publishing), however unless specified, MSDASQL was the default provider used by ADO. After MDAC 2.5 both the Oracle ODBC driver and MSDASQL supported Oracle 7 and partially supported Oracle 8i. Features that were not supported were:
Microsoft initially deprecated the MSDASQL component for their 64-bit operating systems and the Microsoft Oracle ODBC driver was later superseded by a .NET Managed Oracle Provider, which supported Oracle 9i. However, Windows Server 2008 and Windows Vista SP1 ship with a 64-bit version of MSDASQL.
(RDS) allowed the retrieval of a set of data from the server, which the client then altered in some way and then sent back to the server for further processing. With the popular adoption of Transact-SQL
, which extends SQL with such programming constructs as loops and conditional statements, this became less necessary and it was eventually deprecated in MDAC 2.7. Microsoft produced SOAP
Toolkit 2.0, which allows clients to do this via an open XML
-based standard.
and allowed data to viewable as an XML
file. It has not actually been deprecated but has been removed from later versions of MDAC, though Microsoft does provide it as a downloadable component and will support it on their 64-bit operating systems.
MDAC 1.0
MDAC 1.0 was first released in August 1996. According to Microsoft, "MDAC 1.0 existed more as concept than a coordinated, stand-alone setup program." The MDAC 1.0 stack consisted of ODBC 3.0, OLE DB 1.1, ADO 1.0, and the Advanced Data Connector (ADC) 1.0 — which according to Microsoft was the precursor to the Remote Data Service of MDAC 1.5. It also included ODBC drivers for Access/Jet, SQL Server and Oracle
databases. MDAC 1.0 was released via several mechanisms: the Advanced Data Connector shipped with Internet Information Server (IIS) 3.0 and as a downloadable cab file; OLE DB 1.1 and ADO 1.0 shipped with the OLE DB 1.1 SDK, which came with Visual Studio 97 and was also downloadable. MDAC 1.0 came with Active Server Pages, that itself came in IIS 3.0, and also came with Visual InterDev
1.0.
MDAC 1.5
MDAC 1.5 was released between September 1997 and March 1998, and involved a more centralised distribution mechanism than MDAC 1.0. It was released with Microsoft Internet Explorer 4.0, the Internet Client SDK 4.0 and through a CD-ROM given out at the 1997 Professional Developers Conference
(PDC). There were five versions of MDAC 1.5:
The different versions of MDAC 1.5 consisted of:
This version of MDAC had a security flaw that made it vulnerable to an escalated privileges attack. The vulnerability caused systems that had both IIS and MDAC installed to give an unauthorized web user the ability to execute shell commands on the IIS system as a privileged user. This allowed the attacker to use MDAC to tunnel SQL and other ODBC data requests through the public connection to a private back-end network when on a multi-homed Internet-connected IIS system. It also allowed the user to gain unauthorized access to secured, non-published files on the IIS system
MDAC 1.5 was the last data access component release supported under Windows NT 3.51 SP5.
MDAC 2.0
MDAC 2.0 was distributed with the Data Access 2.0 SDK and included the contents of MDAC 1.5, the ODBC 3.5 SDK and the OLE DB 1.5 SDK, and the OLE DB for OLAP
Specification. It also had included many updates to the core product, including a security feature added to the RDS which prevented it from being used maliciously an IIS server. This version came included in Windows NT 4.0 SP4, and also with Visual Studio 6.0, which came with the full Data Access SDK.
MDAC 2.1
MDAC 2.1 was distributed with SQL Server
7.0 and SQL Server 6.5 SP5. MDAC 2.1 SP1 was distributed with Internet Explorer 5 and MDAC 2.1 SP1a (GA) was distributed with Microsoft Office
2000, BackOffice 4.5 and Visual Studio 98 SP3. However, none of these versions of MDAC were released to the general public via the world wide web
. MDAC 2.1 SP2 was distributed from Microsoft's website. The components that were included with 2.1 were:
This version had security vulnerabilities whereby an unchecked buffer could allow an elevated privileges attack. This was found some time later and it affected MDAC 2.1, 2.5 and 2.6 and was addressed in a later patch
MDAC 2.5
MDAC 2.5 was released on February 17, 2000 and distributed with Windows 2000
, and the MDAC service packs were released in parallel with the Windows 2000 service packs. They were also distributed through Microsoft's website. Three service packs were released. The components included with 2.5 were:
Several issues were found in this version of MDAC. When using OLE DB Session Pooling, Microsoft COM+
would try to continuously load and unload OLE DB, and a conflict could arise that caused the OLE DB Session Pooling to run at 100% CPU usage. This was later fixed. Microsoft published a full list of bugs fixed in MDAC 2.5 Service Pack 2 and MDAC 2.5 Service Pack 3. A security vulnerability also existed (later fixed) whereby an unchecked buffer in was found in the SQL Server Driver. This flaw was introduced in MDAC 2.5 SP2.
MDAC 2.6
MDAC 2.6 was released in September 2000 and was distributed through the web and with Microsoft SQL Server 2000 MDAC 2.6 RTM, SP1 (released June 20, 2001), and SP2 (released June 11, 2002) were distributed in parallel with the Microsoft SQL Server 2000 service packs, and could also be downloaded from the Microsoft website.
Beginning with this version of MDAC, Microsoft Jet, Microsoft Jet OLE DB Provider, and the ODBC Desktop Database Drivers were not included. Instead, these could be installed manually. Microsoft also released an alert warning that MDAC 2.6 should not be installed on an SQL Server 7.0 Cluster, because "if you install MDAC 2.6 or later on any node in the cluster, directly or through the installation of another program, it may cause a catastrophic failure of the SQL Server Agent or other SQL Server services." This issue affected Veritas Software
's Backup Exec 9.0 for Windows Servers, because it installs Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) as its database. Revision 4367 installed MDAC version 2.6 SP2 while revision 4454 installed MDAC version 2.7 SP1, which did not have the problem
MDAC 2.7
MDAC 2.7 was released in October 2001 through Microsoft's website. A refresh release was issued in April 2002 through the release of Windows XP and through Microsoft's website. Version 2.7 was available in U.S. English
, Chinese (Traditional and Simplified), German, Japanese, Korean, Brazilian Portuguese
, Czech
, Danish, Greek, Slovak
, Slovenian
, Spanish, Finnish, French, Hungarian, Italian, Dutch
, Norwegian, Polish, Portuguese, Russian, Swedish, and Turkish
. Hebrew
and Arabic
were only available through Windows XP.
The main feature change was support for Microsoft's 64-bit
operating system, however support for Banyan VINES
was also dropped from this version of MDAC. There were several known issues: MDAC 2.7 continued causing connectivity problems on clustered servers running Microsoft SQL Server 6.5 or SQL Server 7.0, with no workaround provided by Microsoft. When creating or configuring ODBC data source names (DSNs) using the Microsoft SQL Server ODBC driver the network library protocol might unexpectedly switch to TCP/IP, even if the DSN was configured to use named pipes. This issue was found by InfoWorld
reporter Randall C. Kennedy
, who identified that the change was actually made in MDAC 2.6 but was never documented. It was discovered when testing client/server database workloads on a Windows XP computer; InfoWorld claims that although overall server CPU utilization rose by only 8 percent using TCP/IP, context switch
es per second dropped by more than 150 percent (which is of course impossible because you would then have a negative context switch rate - the drop is either 33% or 60% depending on which planet the author was on at the time of writing) for a 10-user workload. They were unimpressed that a fundamental functional change to the default behaviour of Net-Lib occurred without more than a passing mention in an unrelated document. Windows XP users also sometimes experienced problems connecting to SQL Server because SQL Server attempts to use certificate
s it finds on the local computer, however if there is more than one certificate available it did not know which one to use. When attempting to use Microsoft Analysis Services
2000 RTM, an error would sometimes appear when trying to browse cubes. Microsoft also discovered a problem in Windows 95
, Windows 98
, and Windows Me
's setup program which prevented the MDAC installation program from rolling back when it encountered an installation error.
Several security issues were resolved by Microsoft for MDAC 2.7. David Litchfield
of Next Generation Security Software Ltd
reported a security vulnerability that results because one of the ODBC functions in MDAC that is used to connect to data sources contained an unchecked buffer. Another vulnerability that was fixed was one whereby an attacker could respond to an SQL Server discovery message broadcast by clients with a specially crafted packet that could cause a buffer overflow. Another flaw was found whereby code could be executed remotely when the attacker responded to the broadcast with another specially crafted packet.
MDAC 2.8
MDAC 2.8 was released in August 2003 and distributed with Microsoft Windows Server 2003
, as well as on Microsoft's Data Access Technologies website. It did not introduce any new features to the product but fixed a number of bugs and security issues — a reg file (automates changes to the registry) was removed that made the server run in an "unsafe" mode whereby the RDS could be exploited to gain unauthorized access to the system and a new restriction was imposed on the length of the Shape query string. There were also several ODBC Administrator changes.
On May 23, 2005 Brad Rhodes (Lead Program Manager of Microsoft Data Access Technologies) announced that MDAC 2.8 SP1 was the last stand-alone redistributable of MDAC that Microsoft will ship. MDAC is now an official component of the Microsoft's operating system, though they will be providing ongoing bug and security fixes to previously released versions of the web-distributable version. However, Microsoft have created a new component called the SQL Native Client (SQLNCLI), which is a stand alone data access API that has combined the OLE DB and ODBC libraries into one DLL. It was formed to be independent of MDAC, which is now reliant on the state the operating system is in — a developer now links to this library and avoids situations where an update of the operating system which updates MDAC breaks applications built to a different version of MDAC.
Windows 7 SP1 has broken backwards compatibility of MDAC 2.8. Software compiled on Windows 7 SP1 that relies on MDAC ADO will not work on Windows versions prior to Windows 7 SP1 (including Windows 7 RTM, Vista, XP). Microsoft has provided solutions to work around this issue for some applications but VBA applications remain affected.
Windows DAC 6.0
Windows Vista
will no longer use MDAC, but instead use Windows DAC, which consists of updated versions of ADO, OLE DB, and ODBC components. According to Microsoft, "Windows DAC includes some changes to work with Windows Vista, but is almost entirely functionally equivalent to MDAC 2.8."
. Microsoft notes that this information may be incorrect for versions of MDAC prior to 2.1 when compared with the versions of the MDAC files installed to the system
Software framework
In computer programming, a software framework is an abstraction in which software providing generic functionality can be selectively changed by user code, thus providing application specific software...
of interrelated Microsoft
Microsoft
Microsoft Corporation is an American public multinational corporation headquartered in Redmond, Washington, USA that develops, manufactures, licenses, and supports a wide range of products and services predominantly related to computing through its various product divisions...
technologies that allows programmers a uniform and comprehensive way of developing applications that can access almost any data store. Its components include: ActiveX Data Objects
ActiveX Data Objects
Microsoft's ActiveX Data Objects is a set of Component Object Model objects for accessing data sources. A part of MDAC, it provides a middleware layer between programming languages and OLE DB...
(ADO), OLE DB
OLE DB
OLE DB is an API designed by Microsoft for accessing data from a variety of sources in an uniform manner. It is a set of interfaces implemented using the Component Object Model ; it is otherwise unrelated to OLE...
, and Open Database Connectivity
Open Database Connectivity
In computing, ODBC is a standard C interface for accessing database management systems . The designers of ODBC aimed to make it independent of database systems and operating systems...
(ODBC). There have been several deprecated components as well, such as the Microsoft Jet Database Engine
Microsoft Jet Database Engine
The Microsoft Jet Database Engine is a database engine on which several Microsoft products have been built. A database engine is the underlying component of a database, a collection of information stored on a computer in a systematic way...
, MSDASQL (the OLE DB provider for ODBC), and Remote Data Services
Remote Data Services
Remote Data Services is a Microsoft technology used in conjunction with ActiveX Data Objects that allowed the retrieval of a set of data from a database server, which the client then altered in some way and then sent back to the server for further processing...
(RDS). Some components have also become obsolete, such as the former Data Access Objects
Data Access Objects
Jet Data Access Objects is a deprecated general programming interface for database access on Microsoft Windows systems. It is unrelated to the data access object design pattern used in object-oriented software design.- History :...
API and Remote Data Objects
Remote Data Objects
Remote Data Objects is the name of an obsolete data access application programming interface primarily used in Microsoft Visual Basic applications on Windows 95 and later operating systems. This includes database connection, queries, stored procedures, result manipulation, and change commits...
.
The first version of MDAC was released in August 1996. At that time Microsoft stated MDAC was more a concept than a stand-alone program and had no widespread distribution method. Later Microsoft released upgrades to MDAC as web-based redistributable packages. Eventually, later versions were integrated with Microsoft Windows
Microsoft Windows
Microsoft Windows is a series of operating systems produced by Microsoft.Microsoft introduced an operating environment named Windows on November 20, 1985 as an add-on to MS-DOS in response to the growing interest in graphical user interfaces . Microsoft Windows came to dominate the world's personal...
and Internet Explorer
Internet Explorer
Windows Internet Explorer is a series of graphical web browsers developed by Microsoft and included as part of the Microsoft Windows line of operating systems, starting in 1995. It was first released as part of the add-on package Plus! for Windows 95 that year...
, and in MDAC 2.8 SP1 they ceased offering MDAC as a redistributable package.
Throughout its history MDAC has been the subject of several security flaws, which led to attacks such as an escalated privileges attack, although the vulnerabilities were generally fixed in later versions and fairly promptly. The current version is 2.8 service pack
Service pack
A service pack is a collection of updates, fixes or enhancements to a software program delivered in the form of a single installable package. Many companies, such as Microsoft or Autodesk, typically release a service pack when the number of individual patches to a given program reaches a certain ...
1, but the product has had many different versions and many of its components have been deprecated and replaced by newer Microsoft technologies. MDAC is now known as Windows DAC in Windows Vista
Windows Vista
Windows Vista is an operating system released in several variations developed by Microsoft for use on personal computers, including home and business desktops, laptops, tablet PCs, and media center PCs...
.
Architecture
The latest version of MDAC (2.8) consists of several interacting components, all of which are WindowsMicrosoft Windows
Microsoft Windows is a series of operating systems produced by Microsoft.Microsoft introduced an operating environment named Windows on November 20, 1985 as an add-on to MS-DOS in response to the growing interest in graphical user interfaces . Microsoft Windows came to dominate the world's personal...
specific except for ODBC (which is available on several platforms). MDAC architecture may be viewed as three layers: a programming interface layer, consisting of ADO
ActiveX Data Objects
Microsoft's ActiveX Data Objects is a set of Component Object Model objects for accessing data sources. A part of MDAC, it provides a middleware layer between programming languages and OLE DB...
and ADO.NET
ADO.NET
ADO.NET is a set of computer software components that programmers can use to access data and data services. It is a part of the base class library that is included with the Microsoft .NET Framework. It is commonly used by programmers to access and modify data stored in relational database systems,...
, a database access layer developed by database vendors such as Oracle and Microsoft (OLE DB
OLE DB
OLE DB is an API designed by Microsoft for accessing data from a variety of sources in an uniform manner. It is a set of interfaces implemented using the Component Object Model ; it is otherwise unrelated to OLE...
, .NET managed providers and ODBC drivers), and the database itself. These component layers are all made available to applications through the MDAC API
Application programming interface
An application programming interface is a source code based specification intended to be used as an interface by software components to communicate with each other...
. The Microsoft SQL Server
Microsoft SQL Server
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
Network Library, a proprietary access method specific to Microsoft SQL Server, is also included in the MDAC. Developers of Windows applications are encouraged to use ADO or ADO.NET for data access, the benefit being that users of the application program are not constrained in their choice of database architecture except that it should be supported by MDAC. Naturally, developers still have the choice of writing applications which directly access OLE DB and ODBC.
Microsoft SQL Server Network Library
The Microsoft SQL Server Network Library (also known as Net-Lib) is used by the Microsoft SQL Server to read and write data using many different network protocols. Though Net-Lib is specific to the SQL Server, Microsoft includes it with MDAC. The SQL Server uses the Open Data Services (ODS) library to communicate with Net-Lib, which interfaces directly with the Windows NTWindows NT
Windows NT is a family of operating systems produced by Microsoft, the first version of which was released in July 1993. It was a powerful high-level-language-based, processor-independent, multiprocessing, multiuser operating system with features comparable to Unix. It was intended to complement...
operating system line's Win32 subsystem. The SQL Server Network Library is controlled through the use of a Client Network Utility, which is bundled with the SQL Server.
Each Net-Lib supported network protocol has a separate driver (not to be confused with a device driver
Device driver
In computing, a device driver or software driver is a computer program allowing higher-level computer programs to interact with a hardware device....
), and has support for a session layer
Session layer
The session layer is layer 5 of the seven-layer OSI model of computer networking.The session layer provides the mechanism for opening, closing and managing a session between end-user application processes, i.e., a semi-permanent dialogue. Communication sessions consist of requests and responses...
in its protocol stack. There are two general types of Net-Lib: the primary and the secondary. The primary Net-Lib consists of a Super Socket Net-Lib and the Shared Memory Net-Lib, while there are numerous secondary Net-Libs, including TCP/IP and named pipes network libraries (named pipes are a method of communicating with other processes via a system-persistent pipeline
Pipeline (software)
In software engineering, a pipeline consists of a chain of processing elements , arranged so that the output of each element is the input of the next. Usually some amount of buffering is provided between consecutive elements...
that is given an identity). The Microsoft OLE DB Provider for SQL Server (SQLOLEDB) communicates via primary Net-Libs.
The Super Socket Net-Lib deals with inter-computer communications and coordinates the secondary Net-Libs — though the TCP/IP secondary Net-Lib is an exception in that it calls on the Windows Socket 2 API directly. The Banyan VINES
Banyan VINES
Banyan VINES was a computer network operating system and the set of computer network protocols it used to talk to client machines on the network. The Banyan company based the VINES operating system on Unix, and the network protocols on the archetypical Xerox XNS stack...
, AppleTalk
AppleTalk
AppleTalk is a proprietary suite of protocols developed by Apple Inc. for networking computers. It was included in the original Macintosh released in 1984, but is now unsupported as of the release of Mac OS X v10.6 in 2009 in favor of TCP/IP networking...
, ServerNet
ServerNet (Tandem)
- History :ServerNet is a switched fabric communications link primarily used in proprietary computers made by Tandem Computers, Compaq, and HP. Its features include good scalability, clean fault containment, error detection and failover. The ServerNet architecture specification defines a connection...
, IPX/SPX
IPX/SPX
IPX/SPX stands for Internetwork Packet Exchange/Sequenced Packet Exchange. IPX and SPX are networking protocols used primarily on networks using the Novell NetWare operating systems.-Protocol Layers:...
, Giganet, and RPC
Remote procedure call
In computer science, a remote procedure call is an inter-process communication that allows a computer program to cause a subroutine or procedure to execute in another address space without the programmer explicitly coding the details for this remote interaction...
Net-Libs were dropped from MDAC 2.5 onwards. The Network Library router had the job of managing all these protocols, however now only the named pipes secondary Net-Lib is managed by the router. The Super Socket Net-Lib also handles data encryption via the use of the Windows SSL API.
The Shared Memory Net-Lib, on the other hand, manages connections between multiple instances of SQL Server that exist on one computer. It uses a shared memory
Shared memory
In computing, shared memory is memory that may be simultaneously accessed by multiple programs with an intent to provide communication among them or avoid redundant copies. Depending on context, programs may run on a single processor or on multiple separate processors...
area to communicate between the processes. This is inherently secure; there is no need for data encryption between instances of SQL Server that exist on one computer as the operating system does not allow any other process access to the instances' area of shared memory.
Net-Lib is also able to support the impersonation of a logged in user's security context for protocols that support authenticated connections (called trusted connections). This allows Net-Lib to provide an integrated logon authentication mechanism via the use of Windows Authentication. Windows Authentication is not supported on Windows 98 or Windows Me.
OLE DB
OLE DBOLE DB
OLE DB is an API designed by Microsoft for accessing data from a variety of sources in an uniform manner. It is a set of interfaces implemented using the Component Object Model ; it is otherwise unrelated to OLE...
(also called OLEDB or OLE-DB) allows MDAC applications access to different types of ([data]) stores in a uniform manner. Microsoft has used this technology to separate the application from the data store that it needs to access. This was done because different applications need access to different types and sources of data, and do not necessarily need to know how to access technology-specific functionality. The technology is conceptually divided into consumers and providers. The consumers are the applications that need access to the data, and the provider is the software component that exposes an OLE DB interface through the use of the Component Object Model
Component Object Model
Component Object Model is a binary-interface standard for software componentry introduced by Microsoft in 1993. It is used to enable interprocess communication and dynamic object creation in a large range of programming languages...
(or COM).
OLE DB is the database access interface technology used by MDAC. OLE DB providers can be created to access such simple data stores as a text file or spreadsheet
Spreadsheet
A spreadsheet is a computer application that simulates a paper accounting worksheet. It displays multiple cells usually in a two-dimensional matrix or grid consisting of rows and columns. Each cell contains alphanumeric text, numeric values or formulas...
, through to such complex databases as Oracle
Oracle database
The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
and SQL Server
Microsoft SQL Server
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
. However, because different data store technology can have different capabilities, OLE DB providers may not implement every possible interface available. The capabilities that are available are implemented through the use of COM objects—an OLE DB provider will map the data store technology's functionality to a particular COM interface. Microsoft calls the availability of an interface to be "provider-specific" as it may not be applicable depending on the database technology involved. Additionally, however, providers may also augment the capabilities of a data store; these capabilities are known as services in Microsoft parlance.
The Microsoft OLE DB Provider for SQL Server (SQLOLEDB) is the OLE DB provider that Microsoft provides for the Microsoft SQL Server
Microsoft SQL Server
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
from version 6.5 upwards. According to Microsoft, SQLOLEDB will be "the primary focus of future MDAC feature enhancements [and] will be available on the 64-bit Windows operating system."
Universal data link
Universal data link files (or '.udl files') provide a common user interface for specifying connection attributes. A user can use a Data Link Properties dialog box
Dialog box
In a graphical user interface of computers, a dialog box is a type of window used to enable reciprocal communication or "dialog" between a computer and its user. It may communicate information to the user, prompt the user for a response, or both...
to save connection information in a .udl file as an alternative to directly specifying them by hand in a connection string. Consequently, these files provide a convenient level of indirection
Indirection
In computer programming, indirection is the ability to reference something using a name, reference, or container instead of the value itself. The most common form of indirection is the act of manipulating a value through its memory address. For example, accessing a variable through the use of a...
. Additionally, the dialog box specifies a number of alternate OLE DB data providers for a variety of target applications.
ODBC
Open Database ConnectivityOpen Database Connectivity
In computing, ODBC is a standard C interface for accessing database management systems . The designers of ODBC aimed to make it independent of database systems and operating systems...
(ODBC) is a native interface that is accessed through a programming language
Programming language
A programming language is an artificial language designed to communicate instructions to a machine, particularly a computer. Programming languages can be used to create programs that control the behavior of a machine and/or to express algorithms precisely....
(usually C
C (programming language)
C is a general-purpose computer programming language developed between 1969 and 1973 by Dennis Ritchie at the Bell Telephone Laboratories for use with the Unix operating system....
) that can make calls into a native library. In MDAC this interface is defined as a DLL. A separate module or driver is needed for each database that must be accessed. The functions in the ODBC API are implemented by these DBMS-specific drivers. The driver that Microsoft provides in MDAC is called the SQL Server ODBC Driver (SQLODBC), and (as the name implies) is designed for Microsoft's SQL Server. It supports SQL Server v6.5 and upwards. ODBC allows programs to use SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
requests that will access databases without having to know the proprietary interfaces to the databases. It handles the SQL request and converts it into a request that the individual database system understands.
ADO
ActiveX Data ObjectsActiveX Data Objects
Microsoft's ActiveX Data Objects is a set of Component Object Model objects for accessing data sources. A part of MDAC, it provides a middleware layer between programming languages and OLE DB...
(ADO) is a high level programming interface to OLE DB. It uses a hierarchical object model to allow applications to programmatically create, retrieve, update and delete
CRUD (acronym)
In computer programming, create, read, update and delete are the four basic functions of persistent storage. Sometimes CRUD is expanded with the words retrieve instead of read or destroy instead of delete...
data from sources supported by OLE DB. ADO consists of a series of hierarchical COM-based objects and collections, an object that acts as a container of many other objects. A programmer can directly access ADO objects to manipulate data, or can send an SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
query to the database via several ADO mechanisms. ADO is made up of nine objects and four collections.
The collections are:
- Fields: This collection contains a set of Field objects. The Collection can be used in either a Recordset object or in a Record object. In a Recordset object, each of the Field objects that make up the Fields collection corresponds to a column in that Recordset object. In a Record object, a Field can be an absolute or relative URL that points into a tree-structured namespace (used for semi-structured dataSemi-structured dataSemi-structured data is a form of structured data that does not conform with the formal structure of tables and data models associated with relational databases but nonetheless contains tags or other markers to separate semantic elements and enforce hierarchies of records and fields within the data...
providers like the Microsoft OLE DB Provider for Internet Publishing) or as a reference to the default Stream object associated with that Record object. - Properties: An object can have more than one Property object, which are contained in the object's Properties collection.
- Parameters: A Command object can have several Parameter commands to change its predefined behaviour, and each of the Parameter objects are contained in the Command object's Parameters collection
- Errors: All provider created errors are passed to a collection of Error objects, while the Errors collection itself is contained in a Connection object. When an ADO operation creates an error, the collection is cleared and a new group of Error objects are created in the collection.
The objects are:
- Connection: The connection object is ADO's connection to a data store via OLE DB. The connection object stores information about the session and provides methods of connecting to the data store. As some data stores have different methods of establishing a connection, some methods may not be supported in the connection object for particular OLE DB providers. A connection object connects to the data store using its 'Open' method with a connection string which specifies the connection as a list of key value pairs (for example: "
Provider='SQLOLEDB';Data Source='TheSqlServer'; Initial Catalog='Northwind';Integrated Security='SSPI';"). The start of which must identify the type of data store connection that the connection object requires. This must be either:- an OLE DB provider (for example SQLOLEDB), using the syntax "provider="
- a file name, using the syntax "file name="
- a remote provider and server (see RDS), using the syntax "Remote provider=" and "Remote server="
- an absolute URLUniform Resource LocatorIn computing, a uniform resource locator or universal resource locator is a specific character string that constitutes a reference to an Internet resource....
, using the syntax "URL="
- Command: After the connection object establishes a session to the data source, instructions are sent to the data provider via the command object. The command object can send SQL queries directly to the provider through the use of the CommandText property, send a parameterised query or stored procedureStored procedureA stored procedure is a subroutine available to applications that access a relational database system. A stored procedure is actually stored in the database data dictionary.Typical uses for stored procedures include data validation or access control mechanisms...
through the use of a Parameter object or Parameters collection or run a query and return the results to a dataset object via the Execute method. There are several other methods that can be used in the Command object relating to other objects, such as the Stream, RecordSet or Connection objects. - Recordset: A recordsetRecordsetA recordset is a data structure that consists of a group of database records, and can either come from a base table or as the result of a query to the table....
is a group of records, and can either come from a base table or as the result of a query to the table. The RecordSet object contains a Fields collection and a Properties collection. The Fields collection is a set of Field objects, which are the corresponding columnsColumn (database)In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed....
in the table. The Properties collection is a set of Property objects, which defines a particular functionality of an OLE DB provider. The RecordSet has numerous methods and properties for examining the data that exists within it. Records can be updated in the recordset by changing the values in the record and then calling on theUpdateorUpdateBatchmethod. Adding new records is performed through theAddNewfunction and then by calling on theUpdateorUpdateBatchmethod. Records are also deleted in the recordset with the Delete method and then by calling on the Update method. However, if for some reason the deletion cannot occur, such as because of violations in referential integrityReferential integrityReferential integrity is a property of data which, when satisfied, requires every value of one attribute of a relation to exist as a value of another attribute in a different relation ....
, then the recordset will remain in edit mode after the call to theUpdatemethod. The programmer must explicitly call on theCancelUpdatefunction to cancel the update. Additionally, ADO can rollback transactions (if this is supported) and cancel batch updates. Recordsets can also be updated in one of three ways: via an immediate update, via a batch update, or through the use of transactions:- Immediate: The recordset is locked using the
adLockOptimisticoradLockPessimisticlock. The data are updated at the data source after the record is changed and theUpdatemethod is called. - Batch: The recordset is locked using
adLockBatchOptimisticand each timeUpdateis called the data are updated in a temporary buffer. Finally, whenUpdateBatchis called the data are completely updated back at the data source. This has the advantage of it all being done in memory, and if a problem occurs thenUpdateCancelis called and the updates are not sent to the data source - Transaction: If the OLE DB provider allows it, transactionsDatabase transactionA transaction comprises a unit of work performed within a database management system against a database, and treated in a coherent and reliable way independent of other transactions...
can be used. To start the transaction, the programmer invokes theBeginTransmethod and does the required updates. When they are all done, the programmer invokes theCommitTransmethod.RollbackTranscan be invoked to cancel any changes made inside the transaction and rollbackRollback (data management)In database technologies, a rollback is an operation which returns the database to some previous state. Rollbacks are important for database integrity, because they mean that the database can be restored to a clean copy even after erroneous operations are performed...
the database to the state before the transaction began
- Immediate: The recordset is locked using the
- Record: This object represents one record in the database, and contains a fields collection. A RecordSet consists of a collection of Record objects.
- Stream: A stream, mainly used in a RecordSet object, is a means of reading and writing a stream of bytes. It is mostly used to save a recordset in an XML format, to send commands to an OLE DB provider as an alternative to the CommandText object and to contain the contents of a binary or text file.
- Parameter: A parameter is a means of altering the behaviour of a common piece of functionality, for instance a stored procedure might have different parameters passed to it depending on what needs to be done; these are called parameterised commands.
- Field: Each Record object contains many fields, and a RecordSet object has a corresponding Field object also. The RecordSet object's Field object corresponds to a columnColumn (database)In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed....
in the database table that it references. - Property: This object is specific to the OLE DB provider and defines an ability that the provider has implemented. A property object can be either a built-in property — it is a well defined property implemented by ADO already and thus cannot be altered — or can be a dynamic property — defined by the underlying data provider and can be changed
- Error: When an OLE DB provider errorErrorThe word error entails different meanings and usages relative to how it is conceptually applied. The concrete meaning of the Latin word "error" is "wandering" or "straying". Unlike an illusion, an error or a mistake can sometimes be dispelled through knowledge...
occurs during the use of ADO, an Error object will be created in the Errors collection. Other errors do not go into an Error object, however. For instance, any errors that occur when manipulating data in a RecordSet or Field object are stored in a Status property.
ADO.NET
ADO.NETADO.NET
ADO.NET is a set of computer software components that programmers can use to access data and data services. It is a part of the base class library that is included with the Microsoft .NET Framework. It is commonly used by programmers to access and modify data stored in relational database systems,...
is the latest version of ADO (after ADO 2.8, now often referred to as ADO Classic) and is part of the MDAC 2.8 stack alongside classic ADO. It is built around Microsoft .NET. Though sometimes seen as an evolutionary step up from ADO, some fundamental structural changes were made by Microsoft. ADO.NET runs through a .NET Managed Provider, a modified version of an OLE DB provider specifically designed for .NET. The object structure is no longer built around a Recordset object. Instead a Dataset object is used to contain data gathered from multiple sources. This is transparent to the programmer. Unlike the old ADO Recordset, the Dataset's design allows for disconnected data. Conceptually, a Dataset object can be seen as a small in-memory relational database in its own right that allows for manipulation of data in any direction (a Recordset was a forward-only reader). In order to propagate changes back into the database, a Dataadapter object is used that transfers data from between the data source and the DataSet object. Cursor
Cursor (databases)
In computer science and technology, a database cursor is a control structure that enables traversal over the records in a database. Cursors facilitate subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database records...
s were also deprecated in ADO.NET, being replaced with a Datareader object, which is used to efficiently process a large list of results one record at a time without storing them.
Deprecated and obsolete components
MDAC is a continually evolving component framework. As such, there have been several components that were previously part of it but have since been deprecated or removed entirely from the framework.Microsoft Jet Database Engine and JRO
Jet stands for Joint Engine Technology and was a database engineDatabase engine
A database engine is the underlying software component that a database management system uses to create, read, update and delete data from a database....
used for Microsoft Access
Microsoft Access
Microsoft Office Access, previously known as Microsoft Access, is a relational database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of...
, Microsoft Exchange Server
Microsoft Exchange Server
Microsoft Exchange Server is the server side of a client–server, collaborative application product developed by Microsoft. It is part of the Microsoft Servers line of server products and is used by enterprises using Microsoft infrastructure products...
and Visual Basic
Visual Basic
Visual Basic is the third-generation event-driven programming language and integrated development environment from Microsoft for its COM programming model...
. Jet was part of a Relational Database Management System
Relational database management system
A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
(RDBMS) and offered a single interface
Interface (computer science)
In the field of computer science, an interface is a tool and concept that refers to a point of interaction between components, and is applicable at the level of both hardware and software...
that other software could use to access Microsoft databases. Jet also provided support for security, referential integrity
Referential integrity
Referential integrity is a property of data which, when satisfied, requires every value of one attribute of a relation to exist as a value of another attribute in a different relation ....
, transaction
Database transaction
A transaction comprises a unit of work performed within a database management system against a database, and treated in a coherent and reliable way independent of other transactions...
processing
Transaction processing
In computer science, transaction processing is information processing that is divided into individual, indivisible operations, called transactions. Each transaction must succeed or fail as a complete unit; it cannot remain in an intermediate state...
, index
Index (database)
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space...
ing, record
Record locking
Record locking is the technique of preventing simultaneous access to data in a database, to prevent inconsistent results.The classic example is demonstrated by two bank clerks attempting to update the same bank account for two different transactions. Clerks 1 and 2 both retrieve the account's...
and page locking, and data replication. In later versions of Jet, the engine was extended to run SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
queries, store character data in Unicode
Unicode
Unicode is a computing industry standard for the consistent encoding, representation and handling of text expressed in most of the world's writing systems...
format, create views
View (database)
In database theory, a view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented database composed of the result set of a query or map and reduce functions...
, and allowed bi-directional replication with the Microsoft SQL Server. It has since been superseded by MSDE
MSDE
Microsoft SQL Server Data Engine is a relational database management system developed by Microsoft. It is a scaled-down version of Microsoft SQL Server 7.0 or 2000 which is free for non-commercial use as well as certain limited commercial use...
.
There were three modules to Jet. One was the Native Jet ISAM Driver, a Jet dynamic link library (DLL) that could directly manipulate Microsoft Access database files (MDB), which was a modified form of an Indexed Sequential Access Method (ISAM) database. Another one of the modules were the ISAM Drivers, DLLs that allowed access to ISAM databases, among them being Xbase
XBase
xBase is the generic term for all programming languages that derive from the original dBASE programming language and database formats. These are sometimes informally known as dBASE "clones"...
, Paradox
Paradox (database)
Paradox is a relational database management system currently published by Corel Corporation. It was originally released for DOS by Ansa Software, and then by Borland after it bought the company...
, Btrieve
Btrieve
Btrieve is a transactional database software product. It is based on Indexed Sequential Access Method , which is a way of storing data for fast retrieval...
and FoxPro
Visual FoxPro
Visual FoxPro is a data-centric object-oriented and procedural programming language produced by Microsoft. It is derived from FoxPro which was developed by Fox Software beginning in 1984. Fox Technologies merged with Microsoft in 1992, after which the software acquired further features and the...
files. The final module was the Data Access Objects (DAO) DLL, DAO
Data Access Objects
Jet Data Access Objects is a deprecated general programming interface for database access on Microsoft Windows systems. It is unrelated to the data access object design pattern used in object-oriented software design.- History :...
allowed programmers access to the Jet engine. It was basically an object-oriented data language used by Access Basic and Visual Basic
Visual Basic
Visual Basic is the third-generation event-driven programming language and integrated development environment from Microsoft for its COM programming model...
application developers to access Jet.
Similarly, the Microsoft Jet OLE DB Provider and Replication Objects (JRO) which allowed replication between Jet data sources was removed from MDAC 2.6
MSDASQL and Oracle ODBC
The Microsoft OLE DB Provider for ODBC, or MSDASQL, was an OLE DB provider for allowing ActiveX Data ObjectsActiveX Data Objects
Microsoft's ActiveX Data Objects is a set of Component Object Model objects for accessing data sources. A part of MDAC, it provides a middleware layer between programming languages and OLE DB...
access to databases via any ODBC driver. There were several OLE-DB providers supplied by Microsoft (providers available were for the Indexing Service, Active Directory
Active Directory
Active Directory is a directory service created by Microsoft for Windows domain networks. It is included in most Windows Server operating systems. Server computers on which Active Directory is running are called domain controllers....
, Jet, SQL Server, Oracle and Internet Publishing), however unless specified, MSDASQL was the default provider used by ADO. After MDAC 2.5 both the Oracle ODBC driver and MSDASQL supported Oracle 7 and partially supported Oracle 8i. Features that were not supported were:
- CLOBCharacter large objectA Character Large Object is a collection of character data in a database management system, usually stored in a separate location that is referenced in the table itself...
, BLOBBlob- In biology :* Blob , sections of the visual cortex where groups of color-sensitive neurons assemble* Globster, an unidentified organic mass that washes up on the shoreline of an ocean or other body of water...
, BFILE, NCHAR, NCLOB, and NVARCHAR2 Oracle datatypes - Unicode support for Oracle 7.x and 8i
- multiple client instances of Oracle
- nested outer joins
Microsoft initially deprecated the MSDASQL component for their 64-bit operating systems and the Microsoft Oracle ODBC driver was later superseded by a .NET Managed Oracle Provider, which supported Oracle 9i. However, Windows Server 2008 and Windows Vista SP1 ship with a 64-bit version of MSDASQL.
Remote Data Services (RDS)
Remote Data ServicesRemote Data Services
Remote Data Services is a Microsoft technology used in conjunction with ActiveX Data Objects that allowed the retrieval of a set of data from a database server, which the client then altered in some way and then sent back to the server for further processing...
(RDS) allowed the retrieval of a set of data from the server, which the client then altered in some way and then sent back to the server for further processing. With the popular adoption of Transact-SQL
Transact-SQL
Transact-SQL is Microsoft's and Sybase's proprietary extension to SQL. SQL, often expanded to Structured Query Language, is a standardized computer language that was originally developed by IBM for querying, altering and defining relational databases, using declarative statements...
, which extends SQL with such programming constructs as loops and conditional statements, this became less necessary and it was eventually deprecated in MDAC 2.7. Microsoft produced SOAP
SOAP
SOAP, originally defined as Simple Object Access Protocol, is a protocol specification for exchanging structured information in the implementation of Web Services in computer networks...
Toolkit 2.0, which allows clients to do this via an open XML
XML
Extensible Markup Language is a set of rules for encoding documents in machine-readable form. It is defined in the XML 1.0 Specification produced by the W3C, and several other related specifications, all gratis open standards....
-based standard.
SQLXML
SQLXML was designed for SQL Server 2000, but was deprecated with MDAC 2.6. It allowed Microsoft's relational database to be viewed by XPathXPath
XPath is a language for selecting nodes from an XML document. In addition, XPath may be used to compute values from the content of an XML document...
and allowed data to viewable as an XML
XML
Extensible Markup Language is a set of rules for encoding documents in machine-readable form. It is defined in the XML 1.0 Specification produced by the W3C, and several other related specifications, all gratis open standards....
file. It has not actually been deprecated but has been removed from later versions of MDAC, though Microsoft does provide it as a downloadable component and will support it on their 64-bit operating systems.
Obsolete components
Several components have been completely removed from MDAC by Microsoft and are no longer supported. They are:- ESQL/C: Embedded SQLEmbedded SQLEmbedded SQL is a method of combining the computing power of a programming language and the database manipulation capabilities of SQL. Embedded SQL statements are SQL statements written inline with the program source code of the host language. The embedded SQL statements are parsed by an embedded...
(also known as E-SQL or ESQL/C) is a way of using SQL when programming in Visual C. Microsoft dropped support for this after SQL Server 6.5 was released, though they did license some of the ESQL/C run-time environment to a company called Micro Focus, who develops COBOLCOBOLCOBOL is one of the oldest programming languages. Its name is an acronym for COmmon Business-Oriented Language, defining its primary domain in business, finance, and administrative systems for companies and governments....
compilers and tools - DAO: DAO, or Data Access ObjectsData Access ObjectsJet Data Access Objects is a deprecated general programming interface for database access on Microsoft Windows systems. It is unrelated to the data access object design pattern used in object-oriented software design.- History :...
were an object oriented interface created by Microsoft which allowed early versions of Microsoft AccessMicrosoft AccessMicrosoft Office Access, previously known as Microsoft Access, is a relational database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of...
and Visual BasicVisual BasicVisual Basic is the third-generation event-driven programming language and integrated development environment from Microsoft for its COM programming model...
to access the Jet database engineMicrosoft Jet Database EngineThe Microsoft Jet Database Engine is a database engine on which several Microsoft products have been built. A database engine is the underlying component of a database, a collection of information stored on a computer in a systematic way...
. Later (in version 3.5) it was able to bypass the Jet engine altogether and directly access ODBC data sources. - RDO: Remote Data ObjectsRemote Data ObjectsRemote Data Objects is the name of an obsolete data access application programming interface primarily used in Microsoft Visual Basic applications on Windows 95 and later operating systems. This includes database connection, queries, stored procedures, result manipulation, and change commits...
, or RDO, was a MicrosoftMicrosoftMicrosoft Corporation is an American public multinational corporation headquartered in Redmond, Washington, USA that develops, manufactures, licenses, and supports a wide range of products and services predominantly related to computing through its various product divisions...
technology that allowed for the creation of interfaces that directly called on ODBC. RDO version 2.0 was the final version developed by Microsoft. - DB-Library: a C-based API that allowed an application to interact with SQL Server. It will not be supported on any product after SQL Server 2000, and no features were added after SQL Server 6.5.
History
Microsoft has released several versions of MDAC over time. The distribution method has varied and the feature-set is different for each version.MDAC 1.0
MDAC 1.0 was first released in August 1996. According to Microsoft, "MDAC 1.0 existed more as concept than a coordinated, stand-alone setup program." The MDAC 1.0 stack consisted of ODBC 3.0, OLE DB 1.1, ADO 1.0, and the Advanced Data Connector (ADC) 1.0 — which according to Microsoft was the precursor to the Remote Data Service of MDAC 1.5. It also included ODBC drivers for Access/Jet, SQL Server and Oracle
Oracle database
The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
databases. MDAC 1.0 was released via several mechanisms: the Advanced Data Connector shipped with Internet Information Server (IIS) 3.0 and as a downloadable cab file; OLE DB 1.1 and ADO 1.0 shipped with the OLE DB 1.1 SDK, which came with Visual Studio 97 and was also downloadable. MDAC 1.0 came with Active Server Pages, that itself came in IIS 3.0, and also came with Visual InterDev
Visual InterDev
Microsoft Visual InterDev, part of Microsoft Visual Studio 97 and 6.0, is an IDE used to create web applications using Microsoft Active Server Pages technologies. It has code completion, database server management tools, and an integrated debugger....
1.0.
MDAC 1.5
MDAC 1.5 was released between September 1997 and March 1998, and involved a more centralised distribution mechanism than MDAC 1.0. It was released with Microsoft Internet Explorer 4.0, the Internet Client SDK 4.0 and through a CD-ROM given out at the 1997 Professional Developers Conference
Professional Developers Conference
Microsoft's Professional Developers Conference is a conference for software developers, normally Windows developers.It covers new and upcoming technology from Microsoft, and so only occurs in the years when there is something new to talk about...
(PDC). There were five versions of MDAC 1.5:
- MDAC 1.5 (initial release): included with Internet Explorer 4.0 and the Internet Client SDK.
- MDAC 1.5a: downloadable from Microsoft's website
- MDAC 1.5b: came with Windows NT 4.0Windows NT 4.0Windows NT 4.0 is a preemptive, graphical and business-oriented operating system designed to work with either uniprocessor or symmetric multi-processor computers. It was the next release of Microsoft's Windows NT line of operating systems and was released to manufacturing on 31 July 1996...
Option Pack & Office 97 - MDAC 1.5c: fixed issues with ADO threading and ODBC Connection Pooling and was distributed via the Microsoft website. It only came with the ADO/MDAC runtime components.
- MDAC 1.5d: came included with Windows 98Windows 98Windows 98 is a graphical operating system by Microsoft. It is the second major release in the Windows 9x line of operating systems. It was released to manufacturing on 15 May 1998 and to retail on 25 June 1998. Windows 98 is the successor to Windows 95. Like its predecessor, it is a hybrid...
and Internet ExplorerInternet ExplorerWindows Internet Explorer is a series of graphical web browsers developed by Microsoft and included as part of the Microsoft Windows line of operating systems, starting in 1995. It was first released as part of the add-on package Plus! for Windows 95 that year...
4.01 service pack 1.
The different versions of MDAC 1.5 consisted of:
- ODBC 3.5
- OLE DB 1.5
- ADO 1.5
- Remote Data Service 1.5, which superseded the Advanced Data Connector.
This version of MDAC had a security flaw that made it vulnerable to an escalated privileges attack. The vulnerability caused systems that had both IIS and MDAC installed to give an unauthorized web user the ability to execute shell commands on the IIS system as a privileged user. This allowed the attacker to use MDAC to tunnel SQL and other ODBC data requests through the public connection to a private back-end network when on a multi-homed Internet-connected IIS system. It also allowed the user to gain unauthorized access to secured, non-published files on the IIS system
MDAC 1.5 was the last data access component release supported under Windows NT 3.51 SP5.
MDAC 2.0
MDAC 2.0 was distributed with the Data Access 2.0 SDK and included the contents of MDAC 1.5, the ODBC 3.5 SDK and the OLE DB 1.5 SDK, and the OLE DB for OLAP
OLAP
In computing, online analytical processing, or OLAP , is an approach to swiftly answer multi-dimensional analytical queries. OLAP is part of the broader category of business intelligence, which also encompasses relational reporting and data mining...
Specification. It also had included many updates to the core product, including a security feature added to the RDS which prevented it from being used maliciously an IIS server. This version came included in Windows NT 4.0 SP4, and also with Visual Studio 6.0, which came with the full Data Access SDK.
MDAC 2.1
MDAC 2.1 was distributed with SQL Server
Microsoft SQL Server
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
7.0 and SQL Server 6.5 SP5. MDAC 2.1 SP1 was distributed with Internet Explorer 5 and MDAC 2.1 SP1a (GA) was distributed with Microsoft Office
Microsoft Office
Microsoft Office is a non-free commercial office suite of inter-related desktop applications, servers and services for the Microsoft Windows and Mac OS X operating systems, introduced by Microsoft in August 1, 1989. Initially a marketing term for a bundled set of applications, the first version of...
2000, BackOffice 4.5 and Visual Studio 98 SP3. However, none of these versions of MDAC were released to the general public via the world wide web
World Wide Web
The World Wide Web is a system of interlinked hypertext documents accessed via the Internet...
. MDAC 2.1 SP2 was distributed from Microsoft's website. The components that were included with 2.1 were:
- ADO 2.1
- RDS 2.1
- OLE DB 2.1
- the OLE DB Provider for ODBC, SQL Server and Oracle
- JRO 2.1
- a Jet driver
- RDO.
This version had security vulnerabilities whereby an unchecked buffer could allow an elevated privileges attack. This was found some time later and it affected MDAC 2.1, 2.5 and 2.6 and was addressed in a later patch
MDAC 2.5
MDAC 2.5 was released on February 17, 2000 and distributed with Windows 2000
Windows 2000
Windows 2000 is a line of operating systems produced by Microsoft for use on personal computers, business desktops, laptops, and servers. Windows 2000 was released to manufacturing on 15 December 1999 and launched to retail on 17 February 2000. It is the successor to Windows NT 4.0, and is the...
, and the MDAC service packs were released in parallel with the Windows 2000 service packs. They were also distributed through Microsoft's website. Three service packs were released. The components included with 2.5 were:
- ADO 2.5
- ADO MD 2.5
- ADOX 2.5
- RDS 2.5
- OLE DB 2.5
- many OLE DB Providers
- JRO 2.5
- ODBC 3.51
- many ODBC drivers
- many Jet drivers.
Several issues were found in this version of MDAC. When using OLE DB Session Pooling, Microsoft COM+
Component Object Model
Component Object Model is a binary-interface standard for software componentry introduced by Microsoft in 1993. It is used to enable interprocess communication and dynamic object creation in a large range of programming languages...
would try to continuously load and unload OLE DB, and a conflict could arise that caused the OLE DB Session Pooling to run at 100% CPU usage. This was later fixed. Microsoft published a full list of bugs fixed in MDAC 2.5 Service Pack 2 and MDAC 2.5 Service Pack 3. A security vulnerability also existed (later fixed) whereby an unchecked buffer in was found in the SQL Server Driver. This flaw was introduced in MDAC 2.5 SP2.
MDAC 2.6
MDAC 2.6 was released in September 2000 and was distributed through the web and with Microsoft SQL Server 2000 MDAC 2.6 RTM, SP1 (released June 20, 2001), and SP2 (released June 11, 2002) were distributed in parallel with the Microsoft SQL Server 2000 service packs, and could also be downloaded from the Microsoft website.
Beginning with this version of MDAC, Microsoft Jet, Microsoft Jet OLE DB Provider, and the ODBC Desktop Database Drivers were not included. Instead, these could be installed manually. Microsoft also released an alert warning that MDAC 2.6 should not be installed on an SQL Server 7.0 Cluster, because "if you install MDAC 2.6 or later on any node in the cluster, directly or through the installation of another program, it may cause a catastrophic failure of the SQL Server Agent or other SQL Server services." This issue affected Veritas Software
VERITAS Software
Veritas Software Corp. was an international software company that was founded in 1983 as Tolerant Systems, renamed Veritas Software Corp. in 1989, and merged with Symantec in 2005. It was headquartered in Mountain View, California...
's Backup Exec 9.0 for Windows Servers, because it installs Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) as its database. Revision 4367 installed MDAC version 2.6 SP2 while revision 4454 installed MDAC version 2.7 SP1, which did not have the problem
MDAC 2.7
MDAC 2.7 was released in October 2001 through Microsoft's website. A refresh release was issued in April 2002 through the release of Windows XP and through Microsoft's website. Version 2.7 was available in U.S. English
American English
American English is a set of dialects of the English language used mostly in the United States. Approximately two-thirds of the world's native speakers of English live in the United States....
, Chinese (Traditional and Simplified), German, Japanese, Korean, Brazilian Portuguese
Brazilian Portuguese
Brazilian Portuguese is a group of Portuguese dialects written and spoken by most of the 190 million inhabitants of Brazil and by a few million Brazilian emigrants, mainly in the United States, United Kingdom, Portugal, Canada, Japan and Paraguay....
, Czech
Czech language
Czech is a West Slavic language with about 12 million native speakers; it is the majority language in the Czech Republic and spoken by Czechs worldwide. The language was known as Bohemian in English until the late 19th century...
, Danish, Greek, Slovak
Slovak language
Slovak , is an Indo-European language that belongs to the West Slavic languages .Slovak is the official language of Slovakia, where it is spoken by 5 million people...
, Slovenian
Slovenian language
Slovene or Slovenian is a South Slavic language spoken by approximately 2.5 million speakers worldwide, the majority of whom live in Slovenia. It is the first language of about 1.85 million people and is one of the 23 official and working languages of the European Union...
, Spanish, Finnish, French, Hungarian, Italian, Dutch
Dutch language
Dutch is a West Germanic language and the native language of the majority of the population of the Netherlands, Belgium, and Suriname, the three member states of the Dutch Language Union. Most speakers live in the European Union, where it is a first language for about 23 million and a second...
, Norwegian, Polish, Portuguese, Russian, Swedish, and Turkish
Turkish language
Turkish is a language spoken as a native language by over 83 million people worldwide, making it the most commonly spoken of the Turkic languages. Its speakers are located predominantly in Turkey and Northern Cyprus with smaller groups in Iraq, Greece, Bulgaria, the Republic of Macedonia, Kosovo,...
. Hebrew
Hebrew language
Hebrew is a Semitic language of the Afroasiatic language family. Culturally, is it considered by Jews and other religious groups as the language of the Jewish people, though other Jewish languages had originated among diaspora Jews, and the Hebrew language is also used by non-Jewish groups, such...
and Arabic
Arabic language
Arabic is a name applied to the descendants of the Classical Arabic language of the 6th century AD, used most prominently in the Quran, the Islamic Holy Book...
were only available through Windows XP.
The main feature change was support for Microsoft's 64-bit
64-bit
64-bit is a word size that defines certain classes of computer architecture, buses, memory and CPUs, and by extension the software that runs on them. 64-bit CPUs have existed in supercomputers since the 1970s and in RISC-based workstations and servers since the early 1990s...
operating system, however support for Banyan VINES
Banyan VINES
Banyan VINES was a computer network operating system and the set of computer network protocols it used to talk to client machines on the network. The Banyan company based the VINES operating system on Unix, and the network protocols on the archetypical Xerox XNS stack...
was also dropped from this version of MDAC. There were several known issues: MDAC 2.7 continued causing connectivity problems on clustered servers running Microsoft SQL Server 6.5 or SQL Server 7.0, with no workaround provided by Microsoft. When creating or configuring ODBC data source names (DSNs) using the Microsoft SQL Server ODBC driver the network library protocol might unexpectedly switch to TCP/IP, even if the DSN was configured to use named pipes. This issue was found by InfoWorld
InfoWorld
InfoWorld is an information technology online media and events business operating under the umbrella of InfoWorld Media Group, a division of IDG...
reporter Randall C. Kennedy
Randall C. Kennedy
Randall C Kennedy is director of research and cofounder of Competitive Systems Analysis, an IT consulting company. He was a former systems analyst for Giga Information Group. Kennedy was a contributor for InfoWorld, focusing on Windows, Microsoft and other topics, but was dismissed on February 21,...
, who identified that the change was actually made in MDAC 2.6 but was never documented. It was discovered when testing client/server database workloads on a Windows XP computer; InfoWorld claims that although overall server CPU utilization rose by only 8 percent using TCP/IP, context switch
Context switch
A context switch is the computing process of storing and restoring the state of a CPU so that execution can be resumed from the same point at a later time. This enables multiple processes to share a single CPU. The context switch is an essential feature of a multitasking operating system...
es per second dropped by more than 150 percent (which is of course impossible because you would then have a negative context switch rate - the drop is either 33% or 60% depending on which planet the author was on at the time of writing) for a 10-user workload. They were unimpressed that a fundamental functional change to the default behaviour of Net-Lib occurred without more than a passing mention in an unrelated document. Windows XP users also sometimes experienced problems connecting to SQL Server because SQL Server attempts to use certificate
Public key certificate
In cryptography, a public key certificate is an electronic document which uses a digital signature to bind a public key with an identity — information such as the name of a person or an organization, their address, and so forth...
s it finds on the local computer, however if there is more than one certificate available it did not know which one to use. When attempting to use Microsoft Analysis Services
Microsoft Analysis Services
Microsoft SQL Server Analysis Services is part of Microsoft SQL Server, a database management system. Microsoft has included a number of services in SQL Server related to business intelligence and data warehousing. These services include Integration Services and Analysis Services...
2000 RTM, an error would sometimes appear when trying to browse cubes. Microsoft also discovered a problem in Windows 95
Windows 95
Windows 95 is a consumer-oriented graphical user interface-based operating system. It was released on August 24, 1995 by Microsoft, and was a significant progression from the company's previous Windows products...
, Windows 98
Windows 98
Windows 98 is a graphical operating system by Microsoft. It is the second major release in the Windows 9x line of operating systems. It was released to manufacturing on 15 May 1998 and to retail on 25 June 1998. Windows 98 is the successor to Windows 95. Like its predecessor, it is a hybrid...
, and Windows Me
Windows Me
Windows Millennium Edition, or Windows Me , is a graphical operating system released on September 14, 2000 by Microsoft, and was the last operating system released in the Windows 9x series. Support for Windows Me ended on July 11, 2006....
's setup program which prevented the MDAC installation program from rolling back when it encountered an installation error.
Several security issues were resolved by Microsoft for MDAC 2.7. David Litchfield
David Litchfield
David Litchfield is a renowned security expert from the United Kingdom, who focuses on the discovery and publication of computer security vulnerabilities with a special focus on database server software...
of Next Generation Security Software Ltd
Next Generation Security Software Ltd
Nest Generation Security Software has been changed to NGS Secure Ltd is a security company headquartered in Manchester, England providing information assurance and compliance services....
reported a security vulnerability that results because one of the ODBC functions in MDAC that is used to connect to data sources contained an unchecked buffer. Another vulnerability that was fixed was one whereby an attacker could respond to an SQL Server discovery message broadcast by clients with a specially crafted packet that could cause a buffer overflow. Another flaw was found whereby code could be executed remotely when the attacker responded to the broadcast with another specially crafted packet.
MDAC 2.8
MDAC 2.8 was released in August 2003 and distributed with Microsoft Windows Server 2003
Windows Server 2003
Windows Server 2003 is a server operating system produced by Microsoft, introduced on 24 April 2003. An updated version, Windows Server 2003 R2, was released to manufacturing on 6 December 2005...
, as well as on Microsoft's Data Access Technologies website. It did not introduce any new features to the product but fixed a number of bugs and security issues — a reg file (automates changes to the registry) was removed that made the server run in an "unsafe" mode whereby the RDS could be exploited to gain unauthorized access to the system and a new restriction was imposed on the length of the Shape query string. There were also several ODBC Administrator changes.
On May 23, 2005 Brad Rhodes (Lead Program Manager of Microsoft Data Access Technologies) announced that MDAC 2.8 SP1 was the last stand-alone redistributable of MDAC that Microsoft will ship. MDAC is now an official component of the Microsoft's operating system, though they will be providing ongoing bug and security fixes to previously released versions of the web-distributable version. However, Microsoft have created a new component called the SQL Native Client (SQLNCLI), which is a stand alone data access API that has combined the OLE DB and ODBC libraries into one DLL. It was formed to be independent of MDAC, which is now reliant on the state the operating system is in — a developer now links to this library and avoids situations where an update of the operating system which updates MDAC breaks applications built to a different version of MDAC.
Windows 7 SP1 has broken backwards compatibility of MDAC 2.8. Software compiled on Windows 7 SP1 that relies on MDAC ADO will not work on Windows versions prior to Windows 7 SP1 (including Windows 7 RTM, Vista, XP). Microsoft has provided solutions to work around this issue for some applications but VBA applications remain affected.
Windows DAC 6.0
Windows Vista
Windows Vista
Windows Vista is an operating system released in several variations developed by Microsoft for use on personal computers, including home and business desktops, laptops, tablet PCs, and media center PCs...
will no longer use MDAC, but instead use Windows DAC, which consists of updated versions of ADO, OLE DB, and ODBC components. According to Microsoft, "Windows DAC includes some changes to work with Windows Vista, but is almost entirely functionally equivalent to MDAC 2.8."
Version-checking
There are two ways of checking the version of MDAC that is installed on a computer. One way is via Microsoft's Component Checker program, which compares the value of each installed MDAC DLL to the MDAC file manifest. The second way is to check the keyHKEY_LOCAL_MACHINE\Software\Microsoft\DataAccess\FullInstallVer in the Windows registryWindows registry
The Windows Registry is a hierarchical database that stores configuration settings and options on Microsoft Windows operating systems. It contains settings for low-level operating system components as well as the applications running on the platform: the kernel, device drivers, services, SAM, user...
. Microsoft notes that this information may be incorrect for versions of MDAC prior to 2.1 when compared with the versions of the MDAC files installed to the system
| Version | Release date | Distribution mechanism | Features | Security issues |
|---|---|---|---|---|
| 1.0 | August 1996 | No coordinated release:
|
|
No bulletins released |
1.5 |
September 1997 – March 1998 |
|
|
|
| 1.5a | September 1997–March 1998 |
|
Service release |
|
| 1.5b | September 1997–March 1998 |
|
Service release |
|
| 1.5c | September 1997–March 1998 |
|
|
|
| 2.0 | July 1, 1998 |
|
|
|
| 2.0SP1 | July 1, 1998 |
|
Y2K remediation for Windows NT 4.0 |
|
| 2.0SP2 | July 1, 1998 |
|
Y2K remediation for all platforms |
|
| 2.1 | July 11, 1998 |
|
|
|
| 2.1 SP1 | March 15, 1999 |
|
||
| 2.1 SP1a (GA) | April 1, 1999 |
|
||
| 2.1 SP2 | July 1999 |
|
||
| 2.5 | February 17, 2000 |
|
|
|
| 2.5 SP1 | July 31, 2000 |
|
||
| 2.5 SP2 | April 2000 |
|
||
| 2.5 SPS3 | December 2003 |
|
||
|
2.6 |
September 2000 |
|
Not included (manually installed):
|
|
| 2.6 SP1 | May 2001 |
|
||
| 2.6 SP2 | May 2002 |
|
||
| 2.7 | October 2001 |
|
|
|
| 2.8 | August 2003 |
|
Fixed bugs and security issues |
|
| 2.8 SP1 | May 2005 |
|
Fixed bugs |
|
| 2.8 SP2 | March 2005 |
|
||
| 9.0 | Never released: Microsoft now includes MDAC with operating system updates. |
|
||
| Windows DAC 6.0 (a variant of MDAC for use with Vista) | November 2006 |
Windows Vista Windows Vista is an operating system released in several variations developed by Microsoft for use on personal computers, including home and business desktops, laptops, tablet PCs, and media center PCs... |
No new features, for use with Windows Vista |