Compatibility problems between DMO and SMO connectivity on newer versions of SQL Server
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
Comments
- Anonymous
October 18, 2013
Thanks for the post. It really helps us to resolve the issue.