Compatibility problems between DMO and SMO connectivity on newer versions of SQL Server

  • Article

We've recently been looking at a problem which triggered this error:

[Microsoft][ODBC SQL Server Driver][SQL Server]To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO)

In our case it was actually being thrown by the SQL Server Web Data Administrator which is available on codeplex, but it could just as well be any application that you've written yourselves. In this case the application in question was running on an old Windows 2003 server and had been working fine for a long time, when connecting to old SQL 2000 instances. However when they started trying to connect to later versions of SQL they were getting the above error within the following stack:

at SqlAdmin.ISqlServer.Connect(Object ServerName, Object Login, Object Password)
at SqlAdmin.SqlServer.Connect()
at SqlWebAdmin.databases.Page_Load(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain()

Being as you can check out the source of this application I did check it out, but excluded this as a problem fairly early on. We came back to focus on the SQLDMO.dll itself and via some rigorous structured testing in the lab we managed to reproduce the exact error and provide an appropriate solution. (If you wonder what I mean by rigorous structured troubleshooting, I mean that I build a windows 2003 virtual machine in a lab and then applied updates to it sequentially checkpointing everytime something changed, and testing various hypotheses that I had on each checkpoint. When you're working with DLL versioning problems I find this to be a very effective - and in my opinion necessary methodology).

Anyway the customer web server in question was runing the following version of SQLDMO.DLL on the web server

Module[945] [C:\PROGRAM FILES\WINDOWS\SYSTEM32\SQLDMO.DLL]
  Company Name:      Microsoft Corporation
  File Description:  Distributed Management Objects OLE DLL for SQL Enterprise Manager
  Product Version:   (8.0:760.0)
  File Version:      (2000.80:760.0)
  File Size (bytes): 4215360
  File Date:         ti joulu 17 19:25:22 2002
    Module TimeDateStamp = 0x3dffbfbc - Wed Dec 18 02:22:20 2002
    Module Checksum      = 0x00411b8a
    Module SizeOfImage   = 0x00405000
  Module Pointer to PDB = [dll\sqldmo.pdb]
    Module PDB Signature = 0x3dff938b
    Module PDB Age = 0x9e

(I was running MPSReports during my tests to extract and save exact configurations). This is a build from SQL Server 2000 SP3a, a fairly recognisable version number for those of us who have been around SQL for a few years!

To cut a long story short all SQL 2000 versions of this DLL are incompatible with higher major versions of SQL Server, and you need to patch this DLL. There are a number of articles about installing SQLDMO on the web, but I didn;t find any of them to be particularly 100% reliable for all circumstances, which is why I tested it so thoroughly. I wanted to be sure that I could patch this DLL and only this DLL without changing anything else on a production machine. (For example it was hypothesized that installing SQL2005 or higher would resolve the problem - this in fact turned out to be true, but we didn;t want to do this. I also didn't want to be manually registering DLLs.)

We instead used the backward compatibility MSI from SQL 2005, which can be downloaded and run indepently here:

Feature Pack for Microsoft SQL Server 2005 - November 2005

When running this we removed all the features apart from SQLDMO, and this ensured that we correctly upgraded SQLDMO.DLL (and its related objects such as SQLDMO.DLL) in the SQL 2000 directory here

C:\Program Files\Microsoft SQL Server\80\Tools\Binn

This upgrades SQLDMO.DLL to a version of this format

8.05.2312

The 05 being the key identifier here. Everything will then work fine.

The main point being here that one can install and run a version of DMO which will be compatible with all major versions of SQL higher than 2000, and you can do it in a controlled and easy manner, without resorting to the manual registrations listed in older KBs like these

https://support.microsoft.com/kb/326613
https://support.microsoft.com/kb/248241
https://msdn.microsoft.com/en-us/library/aa274817(v=sql.80).aspx

Hope this helps.

Graham