MDAC Install Issues - Excellent Overview
1 Checking whether MDAC is installed correctly
There are many problems that can be caused by MDAC installation issues ranging from missing entry points to crashes to mysterious behavior.
1.1 Using Component Checker
1.1.1 Downloading the MDAC Component Checker
The MDAC Component Checker is the main tool for verifying MDAC installation. It can be downloaded from the MDAC Downloads page:
https://msdn.microsoft.com/data/mdac/downloads/default.aspx
Once downloaded, unzip into a new directory. e.g. c:\comcheck.
1.1.2 Running Component Checker
The first time you run the MDAC Component Checker, allow it to determine the version of MDAC on the machine. It will either report a single version of MDAC, a mixture of MDAC versions, or it will be unable to determine the version on the computer. In the latter two cases, work with the customer to determine what version they want and help them install. Obviously, the newer the version, the better.
Once the Component Checker determines a specific MDAC version, run it a second time and select that version explicitly. It will then produce a variance report showing which DLL and registry entries match that version and which do not.
1.1.3 Running Component Checker Remotely
The MDAC Component Checker can be run remotely under certain circumstances and not under others.
Environment Status
Citrix/Terminal Services – user session Will not run
Citrix/Terminal Services – remote administration Will run
Citrix/Terminal Services – main console in install mode Will run
Microsoft Office Live Meeting, PC Anywhere,
or other remote control software Will run, subject to limitations above
1.1.4 Running Component Checker when the Common Files directory is not on C:
Some systems have a profile set up where the Program Files and Common Files directories have been redirected to another drive, such as L:, in order to save disk space on the C: drive. The MDAC Component Checker will not work in this scenario. You can see this by typing SET at the MS/DOS Command Prompt. e.g.
CommonProgramFiles=C:\Program Files\Common Files
ProgramFiles=C:\Program Files
According to the Microsoft, while it is okay to redirect the Program Files directory to another drive, it is not okay to redirect the Common Files. You can modify the following Registry entry via REGEDIT:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion!CommonFilesDir
Change this back to c:\program files\common files and re-run the MDAC Component Checker. You do not have to restart the computer.
1.1.5 What Mismatches are Okay
The MDAC Component Checker’s variance report will include a list of expected DLL versions and actual DLL versions found on the system. Some mismatches are okay and others are not.
In general, a mismatch is okay if the major and minor version numbers match and the build number is newer than the expected value. This indicates the system has a patch. The MDAC Component Checker is not aware of patched DLLs and will list them as a mismatch.
For example, MSDASQL.DLL in MDAC 2.8 RTM has a version of 2.80.1022.0. One patched version is 2.80.1054.0. Once you have identified a DLL as being patched, you need to verify whether all other DLLs in the patch have been applied also. You’re the DLL name and version number into the internal Knowledge Base. This will bring up and article about the hot fix. For this example, the following article contains the description of the affected files.
823109 FIX: An access violation may occur when a program calls the UpdateBatch method of an ADO Recordset object by using the OLE DB Provider for ODBC in Data Access Components
The MSINTERNAL section shows only one DLL is affected by this patch. If more than one DLL was affected, you need to make sure that all DLLs listed show up as being patched in the Component Checker’s variance report.
A second case where DLL mismatches are okay is with the Windows XP MDAC 2.81 check. This check includes some DLLs that do not ship with MDAC, such as MSXML3 and some Microsoft Jet DLLs. Mismatches in these DLLs are okay.
1.1.6 When Mismatches are NOT Okay
In any other case than listed in the previous section, mismatches are not okay. i.e. older DLLs and DLLs where the major or minor version numbers do not match or where patches have not been consistently applied.
1.2 MPS Reports for MDAC
The MDAC Component Checker is not totally exhaustive. Occasionally other DLLs that MDAC may depend on could be causing an issue. The MPS Reports for MDAC will gather a list of all DLLs in SYSTEM32 and in the MDAC directories so we can see other DLL versions.
Download the MPS Reports from:
818742 Overview of the Microsoft Configuration Capture Utility (MPS_REPORTS)
https://support.microsoft.com/default.aspx?scid=kb;EN-US;818742
The data obtained would be used in advanced DLL-Hell analysis, such as identifying duplicate MDAC DLLs.
1.3 Process Explorer or FILEMON
1.3.1 Downloading Process Explorer and PROCESS MONITOR
These are two tools that can be downloaded from https://www.microsoft.com/technet/sysinternals/default.mspx. They can be used to see what DLLs are actually being loaded by an application and from where. Unzip the files and copy to a directory where they can be run. There is no installation process. To remove, just delete the files.
1.3.2 Using Process Explorer and PROCESS MONITOR
These tools can be used to determine whether MDAC DLLs are being loaded from the expected directories and give a clue whether there could be duplicate MDAC DLLs on the machine that need to be removed. This type of problem will generally not show up with the MDAC Component Checker.
1 Fixing MDAC
MDAC versions prior to MDAC 2.5 are not supported at all. Applications compiled against earlier versions of MDAC may not run on current versions as there were some breaking interface changes between MDAC 2.1 and MDAC 2.5; the applications will have to be recompiled to use the newer libraries.
1.1.1 SQL Native Client installation
SQL Native Client installations comes as a separate installation and not as part of MDAC.
1.1.2 Installing via SMS
Installing via SMS is supported by the SMS team.
1.2 General Install Requirements
MDAC install has a number of requirements that must be met for a successful installation. Many of these are the same as for successfully running the MDAC Component Checker.
- For Citrix/Terminal Server machines, it is best to run via Add/Remove Programs. You can also run from a remote Administrative console or via the main console while in Install mode.
- You have to be an Administrator on the machine.
- Turn off antivirus applications.
- Shut down applications and services that may be using MDAC components, such as SQL Server, SQL Reporting Services, etc., and any 3rd-party services.
- If the Common Files directory is redirected to another drive than C:, change the registry before installing, as indicated in the MDAC Component Checker section related to this.
- For persistent problems, install MDAC while booted into SAFE mode.
- After the installation, reboot and log back in as an Administrator to complete the install.
1.3 Install Issues
Make sure you install via the guidelines specified above. The MDAC installer makes a log file called DASETUP.LOG. If installing a hot fix, then get DAHOTFIX.LOG. Both files are located in the Windows directory. They can be used for advanced analysis.
1.4 Repair via Upgrade
Repair via Upgrade is the safest and best way to fix MDAC problems. Install a newer version of MDAC than you have on the system, run MDAC_TYPE.EXE to install, and then reboot. Login as an Administrator.
If no newer version exists for your system, then you will have to Reinstall MDAC if it is supported version, or repair the operating system or roll back an OS patch if the version you want to achieve is supported.
Instead of installing a new version of MDAC, especially of it is an OS version, you can often install a hot fix. MDAC hot fixes are cumulative. You can search the Knowledge Base for these articles. e.g.
841758 FIX: Hotfixes are available for MDAC 2.5 Service Pack 3
836799 FIX: Hotfixes are available for MDAC 2.7 Service Pack 1
839801 FIX: Hotfixes are available for MDAC 2.8
884103 How to obtain the latest MDAC 2.8 service pack *** includes SP1, SP2
1.5 Reinstall and Rollback
1.5.1 Standalone MDAC versions
On Windows NT4 and Win9x, we cannot roll back MDAC if MDAC 2.8 is installed. For earlier versions of MDAC, they can be rolled back with an old version of the MDAC Component Checker (v1.0) that is not available for download any more. In general, we should avoid trying to roll back MDAC on these machines and try to roll forward. NT4 and Win9x do not use ExceptionComponents and file versions are used as in most standard install programs.
On Windows 2000, you can install any arbitrary MDAC version, even if older than the current version, by following the steps below. Since MDAC is an operating system component, its installation is governed by ExceptionComponents registry keys. By deleting these keys, you can reinstall the same MDAC version or an older MDAC version. For newer versions, you do not have to delete these keys. They are located under the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Setup\ExceptionComponents
The keys have GUID names and a FriendlyName value. Any ExceptionComponent key with a FriendlyName containing the words MDAC, SQL, OLEDB, ODBC, MSXML, WEBDATA, etc. should be deleted. Most keys are MDAC keys. Generally, the only non-MDAC keys commonly seen are for Internet Explorer, DirectX, and Media Player.
If in doubt, delete the key, since accidentally leaving an MDAC key will prevent the installation from proceeding correctly.
1.5.2 OS MDAC versions
To reinstall or roll back of supported versions of MDAC requires an OS repair or OS Service Pack rollback.
1.5.3 Examples
Example 1
You have Windows 2000 SP3 and MDAC 2.53 (2.5 SP3). You want MDAC 2.52. This version comes with Windows 2000 SP2. You'll have to roll back the OS to Windows 2000 SP2 in order to get that version of MDAC.
Example 2
You have Windows 2000 SP4 and MDAC 2.8. You want MDAC 2.71. Follow the reinstall steps in the previous section.
Example 3
You have Windows 2000 SP4 and MDAC 2.8. You want MDAC 2.53. Then you'll have to repair/reinstall Windows 2000 SP4 in order to get this MDAC version.
Example 4
You have Windows 2003 RTM and MDAC 2.8. You want MDAC 2.81. This is not available. If you want MDAC 2.82, you have to install Windows 2003 SP1.
1 Other Issues
1.1 Terminal Services Issues
If someone tries to install MDAC on a Citrix or Terminal Services machine and is not in Install mode, then the MDAC files will be scattered across shared and private directories. The General Install Requirements section includes instructions on how to properly install MDAC on a Terminal Services machine.
1.2 Corrupt ODBC Registry
In some installs, no drivers will show in the ODBC administrator. This is due to missing registry keys under HKLM\SOFTWARE\ODBC. The easy fix for this is to export this sub-tree from a good machine that has the same Windows directory name and then import onto the broken machine.
1.3 Corrupt ODBC Permissions
On some systems, only the Administrator or a member of the Administrators group has access to the ODBC registry keys. To fix, use REGEDIT to set the permissions to give Everyone READ access (at a minimum) to HKLM\SOFTWARE\ODBC and force this to propagate to sub keys.
1.4 Corrupt OLE DB Registry
Run the MDAC Repair tool to reregister all MDAC DLLs. Needs Microsoft PSS support.l
1.5 .NET DLL Load Order Issues
.NET Applications do not follow the COM load order. As such, the wrong MDAC DLLs can be loaded if there are duplicates on the system. Use FILEMON from https://www.sysinternals.com or DependencyWalker from https://www.dependencywalker.com to determine where the files are being loaded from and are they the right files. If there are duplicates, delete them and try running the application again. If it’s a web application, restart IIS (Start | Run | IISRESET).
1.6 Windows 64-Bit
64-bit versions of Windows have 2 versions of MDAC installed – the usual 32-bit version plus a 64-bit version. When you run MDAC tools, such as UDL files or the ODBC Administrator, these are 64-bit utilities and only use the 64-bit DLLs. Many drivers and providers will appear to be missing because they only exist in the 32-bit version of MDAC, which cannot be used by 64-bit applications.
We have a Windows 64-bit machine you can run via:
mstsc /v ncsql64amd2
Use your domain login.
1.6.1 Do we need 32-bit or 64-bit Drivers and Providers
Run the application and open Task Manager. If the application has *32 after the name, then it is a 32-bit application and will use the 32-bit MDAC and MDAC drivers and providers. If the application does not have anything after the name, then it is a 64-bit application and will use the 64-bit version of MDAC and the 64-bit drivers and providers.
1.6.2 Running 32-bit ODBC Administrator
You can run the 32-bit version of the ODBC Administrator from the following path:
c:\windows\syswow64\odbcad32.exe
The 64-bit version is in the system32 directory. This sounds backwards but was a conscious decision on the development teams to put the mainstream (i.e. 64-bit) files in system32 and the emulated or 32-bit files in the syswow64 directory. This stands for SYStem Windows [32] On Windows 64.
1.6.3 Examining the 32-bit Registry keys
REGEDIT will show the 32-bit registry keys under HKLM\SOFTWARE\Wow6432Node. For example, the 32-bit ODBC tree is:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC
1.6.4 Running a 32-bit Test Script
You can test 32-bit vs. 64-bit functionality by running the following VBScript applet:
dim cn, rs
set cn = createobject("adodb.connection")
cn.open "provider=sqloledb;server=localhost;integrated security=sspi;database=northwind"
msgbox "connected"
set rs = createobject("adodb.recordset")
rs.cursorlocation=3
rs.open "SELECT * FROM Table1", cn
msgbox "recordset has " & rs.recordcount & " rows."
rs.close
cn.close
You will need to fix-up the connection string and the SELECT statement.
To run as a 32-bit application, use the following command-line:
C:\WINDOWS\SysWOW64\wscript.exe c:\test.vbs
To run as a 64-bit application, use the following command-line:
C:\WINDOWS\SYSTEM32\wscript.exe c:\test.vbs
or
wscript.exe c:\test.vbs
1.6.5 Running a 32-bit Data Link Properties (UDL) dialog
Unfortunately, you cannot just run the 32-bit version of RunDLL32.exe to get the 32-bit UDL dialog. You have to set up a file extension. The instructions below associate the UDL32 extension with the 32-bit version of this dialog.
1. In Windows Explorer, use Tools | Folder Options | File Types to make a new file association.
2. Click New.
3. In the Create New Extension dialog, type UDL32 for the Extension and click OK. This will add the file extension to the Folder Options dialog.
4. Select the new extension and click Advanced.
5. For a name, type Microsoft Data Link 32.
6. Click New... to add a new action.
7. Type open as the Action name.
8. For the Application used to perform action, enter:
c:\windows\syswow64\Rundll32.exe C:\PROGRA~2\COMMON~1\System\OLEDB~1\oledb32.dll,OpenDSLFile %1
Note: You may have to alter the WINDOWS directory path. %windir% does not work as a generic path prefix. In addition, using long file names does not appear to work either. You can use DIR C:\ /X to confirm the Program Files (x86) folder has the short name of PROGRA~2. If not, you will have to alter this part of the path also.
9. Select DDE and then click OK.
10. Click OK on the Edit File Type dialog.
11. Click Close on the Folder Options dialog.
12. Right-click the desktop and create a new text document called test.udl32. A modified UDL icon will appear. Double-click the file and the Data Link Properties dialog will be displayed. You can verify this is running the 32-bit version of RUNDLL32 by checking in the Task Manager. In addition, you should see the familiar 32-bit OLE DB Providers for Jet, Oracle, MSDATASHAPE, etc.