Microsoft Data Access Components (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 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 most recent version of MDAC is 2.8 Service Pack 1. Windows DAC is the equivalent framework on Windows Vista.
Architecture
The latest version of MDAC (2.8) consists of several interacting components, all of which are Windows 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.
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 NT 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 Sockets 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.Kalen Delaney (2000), "Inside Microsoft SQL Server 2000", 3rd edition. Redmond: Microsoft Press, pp. 70–74.
OLE DB
OLE DB (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.
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 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.MSDN, "Creating and Configuring Universal Data Link (.udl) Files". Microsoft. Archived February 17, 2007; accessed April 28, 2007.
ODBC
Open Database Connectivity (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. According to Microsoft, "After SQL Server 2012, the ODBC driver will be updated for the most recent server features, including Microsoft Windows Azure SQL Database, and released as the Microsoft ODBC Driver for SQL Server."Prash Shirolkar, Alyssa Henry, Stephen Pepitone, Acey J. Bunch and David Schwartz (September 2011). "Data Access Technologies Road Map", Microsoft Corporation. Archived 8 November 2017; accessed March 1, 2018.
ADO
ActiveX Data Objects (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: 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 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.MSDN, "Fields Collection". Microsoft. Archived September 7, 2012; accessed September 19, 2005. Properties: An object can have more than one Property object, which are contained in the object's Properties collection.MSDN, "Properties Collection". Microsoft. Archived June 6, 2011; accessed September 19, 2005. 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 collectionMSDN, "Parameters Collection". Microsoft. Archived June 6, 2011; accessed September 19, 2005. 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.MSDN, "Errors Collection". Microsoft. Archived June 6, 2011; accessed September 19, 2005.
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';").MSDN, "ADO 2.8: Using a connection object". Microsoft. Archived September 7, 2009; accessed September 10, 2005. 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="