Unable to connect to Enterprise Manager in SQL Server 2000
Problem Description
===============
Encountered the following error message when tried to connect to Enterprise manager in SQL Server 2000
A connection could not be established to (LOCAL).
Reason: Client unable to establish connection
Unable to load communication module. Driver has not been correctly installed..
Please verify SQL Server is running and check your SQL Server registration properties (by right-clicking on the (LOCAL) node) and try again.
Trying to reinstall Client tools also did not solve the problem. This shows that the issue is not with the installation of the Client tools
Went to services control manager and found the SQL Server service to be running but this was not the same case with SQL Agent service
ERRORLOG did not have much information about the issue we are facing
Found some errors in the SQLAgent.out log though
/////////SQLAgent.out/////////
2009-06-24 18:31:16 - ! [165] ODBC Error: 0, Client unable to establish connection [SQLSTATE 08001]
2009-06-24 18:31:16 - ! [298] SQLServer Error: 126, Unable to load communication module.
Driver has not been correctly installed. [SQLSTATE HY000]
2009-06-24 18:31:16 - ! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start
2009-06-24 18:31:16 - ? [098] SQLServerAgent terminated (normally)
Resolution
========
· Suspected that some of the DLL pertaining to SQL Server 2000 should be missing as the error says "Unable to load communication module"
· Downloaded MDAC component checker utility from
o The Component Checker tool checks the files on the computer, compares them to a list from each version of Microsoft Data Access Components (MDAC)
· Ran the component checker against the MDAC version installed on the operating system.
· To know the MDAC version installed, check https://support.microsoft.com/kb/301202
· In the Summary report, in the File report, we found two dlls, one for each MISMATCH and NOTFOUND.
1. </group><group name="MISMATCH" count="1">
<file xmlns="https://tempuri.org/CCFilestream_template.xsd" name="msxml3.dll" dir="%SYSTEMDIR%">
<release name="MDAC 2.8 SP2 on Windows Server 2003 SP2" version="8.80.1185.0" fileversion="8.80.1185.0"/>
<found xmlns="" Description="MSXML 3.0 SP9" date="05/31/2007" fileVersion="8.90.1101.0" size="1119232" version="8.90.1101.0"/><result xmlns="" mismatch_fields="version"/></file>
2. </group><group name="NOTFOUND" count="1">
<file xmlns="https://tempuri.org/CCFilestream_template.xsd" name="dbnetlib.dll" dir="%SYSTEMDIR%">
<release name="MDAC 2.8 SP2 on Windows Server 2003 SP2" version="2000.86.3959.0" fileversion="8.0.3959.0"/>
· From the above XML entry, we can make out that there is a mismatch with the DLL msxml3.dll
· The expected version was 8.80.1185.0 and the version of the DLL found on the machine was 8.90.1101.0
Name : msxml3.dll
expected version :8.80.1185.0
Path : C:\Windows\System32\
Found Version : 8.90.1101.0
· Searched to see if we find the DLL msxml3.dll with the version 8.90.1101.0 anywhere else on the machine
· Found it under C:\windows\servicepackfiles\i386\
· Copied the file from C:\windows\servicepackfiles\i386\ to C:\Windows\System32\
· After this we were able to connect to Enterprise Manager
More information
=============
· If we find a higher number of mismatches for the Component Checker report, then we need to re-run the Service Pack for Windows for Windows 2003 Servers SP2 and above.
· The found version should be higher or equal to the expected version in the report. So, all mismatches are not harmful.
Aravind Lakshminarayanan
SE, Microsoft SQL Server
Reviewed by
Shamik Ghosh & Amit Banerjee
TL, Microsoft SQL Server