SQL Migration from 2000 to 2008

pdmdkm
pdmdkm used Ask the Experts™
on
I moved all elements from my SQL 2000 Server and everything is up and running fine except for a couple Access databases and a couple websites that all have the common theme of making a connection to the server by using a URL in the connection string to a file named "Execute.asp" located on the server.  I was able to "trick" one web site function into working by changing the name of the server in the URL to the name of the old server (that is still up and running, just under a new name), but that connection string is buried in several different DLLs throughout the environment, so the "trick" was only good on that one Javascript file.  Moving from SQL Server 2000 on a Windows Server 2000 to SQL Server 2008 on a Windows Server 2008 has exposed me to just a few changes from what I am most familiar with, so I don't have much (any) experience with IIS 7, and I am very new to the Windows Server 2008 environment.  I have found the DLLs that were being used on the old server and I have registered them on the new server which did change the error I was getting initially to the "ActiveX component can't create object" error that I am getting now.  That error is the same error whether I am running an Access database or a website function or page that attempts to retrieve data from the new server.  If there are any DB gurus out there who are willing to help me out on this fine Saturday, I am more than open for suggestions.  I have exhausted all the suggestions that I have found regarding IIS configurations and file permissions, so I am turning to the experts.  Somebody, please help!!!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ted BouskillSenior Software Developer
Top Expert 2009

Commented:
There is a very simple trick.  Install the SQL client tools on the web server then create a SQL client alias.

Author

Commented:
The SQL Server is the web server, so does the SQL client alias solution still apply?  If so, can you explain the client alias a little further?
Ted BouskillSenior Software Developer
Top Expert 2009

Commented:
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
I apologize if this is elementary, but I have never done this before.  What ALIAS do I use?  Do I need to figure out the alias that may have been set up on the old server, or is there something that would tell me what the clients were connecting to on the old server?

Author

Commented:
Also, do I set the alias up with a Named Pipe or TCP/IP as the protocol?
Distinguished Expert 2017

Commented:
The issue seems to be less with what you migrated to to how configurable were your objects/dlls.  The issue you have is that you have hard coded the connection string in dlls/etc.  The only fix is to get the source and recompile the dlls.
What you may want to do is to use a system DSN based connection or provide a mechanism through configuration files to provide information on the connection string.
Dll should only have the functions.
The alias suggestion deals with defining within your DNS server that servera is now pointing to servernew.
This means that servera must not be up and running as servera.
The problem is that you are chaining and asking for trouble in the long run i.e. when you no longer have access to the DLL source. Or ......

Author

Commented:
Uh...what if I don't have access to the DLL source now?
Distinguished Expert 2017

Commented:
You have to use the alias method.
I.e. create a CNAME record in the internal DNS for servera IN CNAME serverb
This will direct the connection from the DLL to servera to go to serverb.  The problem is that if the DLL is setup with secure connection, the connection will fail because the certificate serverb will present will not reflect servera that the dll might expect.

The options you have are to rename the new server to use the old server's name.
The other problem is that you might not know whether the DLL connection is using the name or is it using the IP unless when it runs it outputs the error i.e. mssql blah blah blah servername or IP not accessible.?

Author

Commented:
The new server does have the exact same name as the old server, and it was because of these stupid DLLs that I planned my migration process to include the renaming of the servers.  It just seems like I have missed a step on the new server that is causing the communication between the server and the clients to fail, or at least not be complete.
Distinguished Expert 2017

Commented:
The problem might be with the difference in versions.  See whether configuring the database that is being addressed by these DLLS in windows 2000 compatibility mode will fix the problem.
The DLL might not have the proper way to talk to a SQL 2008 server. i.e. the DLL is using sql2000 connection libraries.

properties of the database and I think it should be under options.

Author

Commented:
Yep, they are set with a compatibility level of SQL Server 2000.

I don't know, am I even chasing the right tail here?  Is the ActiveX component being unable to create an object error an indication that a connection to the database is failing?  Or is that an indication that something is failing before a connection is made, or perhaps even after a connection is made?
Distinguished Expert 2017

Commented:
it is hard to say, the activeX presumably runs on the client system.  It generates a request to the webserver. The webserver either does or does not fire the correct process that then fires the connection the database that then tries to perform a query that then gets a response and the data has to flow back.
Do you know what the DLL does and check it locally to make sure the functions it contains work.
Did you register/approve the DLL in win2k8 or IIS7?

What is the object that activeX is trying to create?
Distinguished Expert 2017

Commented:
One thing to try, access the old website and see whether it still functions while accessing the new sql server.

Author

Commented:
I did register some DLLs from an administrative command prompt on the new server with the understanding that I was registering them for the server.  I didn't know about registering them for or in IIS 7.  Is that required?  If so, how?

I have no idea what object activex is trying to create.

In my initial post I eluded to a "trick" that I tried on one of the web processes that was failing.  What I did was I changed the URL that I found in a javascript that is run as a scheduled task to update the inventory on the website.  The URL was "http://servername/..." (the 2008 server) and I simply changed it to "http://oldservername/..." (the 2000 server) and I stopped getting the error telling me that the process was failing.  I am pretty sure that I manually launched that scheduled task and I was able to see the process running on the new 2008 SQL server.  Since the URL pointed to the old server, but the actual query was running on the new server, it seemed like a connection to the DB server was succeeding, but whatever that URL was actually being used for was failing.  This seemed to indicate to me that I needed to focus on the IIS settings on the new server to find some hidden setting that is either different than it was on the 2000 server or is new on the 2008 server.
Distinguished Expert 2017

Commented:
http://www.iis.net/ConfigReference
The problem I have is I have no idea what the interaction between the activeX that runs on the client side with the IIS is.  Do you have logging enabled?  You could check the logs for 4xx or 5xx errors for access attempts and see where it errors out and then investigate why it errors out.  

Many things depends on how the DLL is being used and whether access to it is Allowed within IIS (basing this on the IIS 6 configuration model).

Author

Commented:
Ok, I just found some code for one of the DLLs, but I have no idea if it is even close to the same version that created the DLL that is currently being used.  Anyway, it is using ADODB.Connection and the library version referenced is Microsoft ActiveX Data Objects 2.6.  From what I have found online, this shouldn't be an issue for my 2008 server, should it?

I figured out how to turn on "tracing" and I can now see when hits are made to the URL and I can see a fair amount of what it is running.  I taking a stab at the fact that the following line is the object that is failing to get created:

Set objDatabaseAccessor = Server.CreateObject("ZData.clsDatabaseAccessor")

and ZData is the DLL that I copied from the old server and successfully registered on the new server.
Distinguished Expert 2017

Commented:
could you check whether the zdata DLL is also allowed on the IIS 7?
Do you get an access related error to the create object or do you have a programatic/syntax type error from the dll?
Do you have a test application that you can use outside the web server to test the DLL functionality?
i.e. wshell script that will do the same thing. as part of a vbs script?

Author

Commented:
There is also this line before the line I referred to in my last post:

Set objDocument = Server.CreateObject("MSXML2.DOMDocument")

but I don't think there is a problem with it, is there?

Author

Commented:
I can check on whether zdata DLL is allowed if you tell me how and where to check.

Author

Commented:
I found something, I found something.  I just found an Enable 32-bit applications setting in IIS.  I enabled it and now I am getting a EXECUTE permission denied error on a stored procedure.  Wooohooo, I think that has to be it.  I'll post back once I am positive.
Top Expert 2012
Commented:
>>I just found an Enable 32-bit applications setting in IIS.<<
The O/S must be 64-bit.  You are basically telling the O/S to not take advantage of 64-bit O/S and instead swithch to 32-bit mode.

>>I enabled it and now I am getting a EXECUTE permission denied error on a stored procedure.
This can be resolved by doing the following in SSMS:
GRANT EXECUTE ON YourStoredProcedureGoesHere TO YourASPUserNameGoesHere

Author

Commented:
I tried closing this question several weeks ago by marking my own comment as the solution since no other posts actually resolved the issue.  The system told me it was going to allow others to "Object" to my decision before it closed the question.  I guess that didn't happen.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial