SQL Server Communication Issues 1

[SgtJarrow]

If there is a better forum for this, please let me know!!!

Scenario:
The network team planned a SQL Server hardware upgrade. The plan was to bring a new server online, get everything set up, take the old server offline, backup and restore the databases, rename the new server to the old, and bring it fully online.

Couple of things: The hardware was GREATLY imporved. SQL was upgraded from SQL 2000 SP4 to SQL 2005 SP3.

During testing, while both servers were online and a copy of live database was present, everything was perfect and super fast.

Issues:
Now that the switch has been fully performed, the following error is received anytime we try to do anything from another server to the new one:

[OLE/DB provider returned message: Invalid authorization specification]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005:   ].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.

We've tried all kinds of things - removing the server from the domain and rejoining it, removing all linked connections and recreating, etc.

If anyone has seent his or something similar, please let me know. Ask questions as well....I am looking for a sounding board as well to try to figure this out Thanks.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[ptheriault]

When you renamed the new server did you change the name of the SQL instance as well. Also, there is no sp3 for SQL 2005. SP2 was just released. Is that the service pack you are at?

If you select @@severname does it match the new server name?


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SgtJarrow]

Ack.....Yeah....we are now at SP2.

The SQL instance was also renamed. But I posted this I stumbled on another reference.

Order of operations should be:

Rename Server
Reboot
Run SQL Installer - Select Upgrade against database
Reboot
Run two system sprocs to rename instance info

The run SQL installer and upgrade was not completed. We are currently testing this and have fingers crossed.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[ptheriault]

Ok, In the past when I've had to rename a server we renamed the server then the instance then rebooted.

good luck...



- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SgtJarrow]

Followup to any interested on how I solved this.

Turns out I had two issues. The first was related to the linking of the servers. Renaming the server and then running the select @@servername as suggested by ptheriault did in fact show the SQL did not register the change. Thanks to ptheriault for the assist. I completed the changes and that's when the second issue reared its head.

This one is probably a bit more interesting...I did not mention that the server WAS a SQL 2000 32-bit and is now a SQL 2005 64-bit. After a bit of searching I ran across a KB article from Microsoft KB906954. The errors described there were exactly my problem. My older 32-bit servers could talk to the new 64-bit server, my new 64-bit server could talk to a new 32-bit, but my 64-bit could not talk to the old 32-bit servers. By talking I mean that on the 64-bit server, running a distributed query against my 32-bit server would fail.

Short and sweet answer is that the old 32-bit server did not know how to resolve the query request because the stored procedures were too old to have the new coding practices. The KB article by Microsoft talks about running a instcat.sql on your 2000 32-bit machine. It also tells you to run it using a command prompt with osql command.

BUT what the article DOES NOT say is that you need to run the instcat.sql from your 64-bit server. This was my missing piece that took me three days to figure out. And now that I found it it makes perfect sense. Running the instcat.sql from the 32-bit server would not update the stored procedures properly...because the code needed is in 64-bit server. DUH to me! The article also does not say that you can simply open this query in Enterprise Manager on your 32-bit SQL 2000 server and run it there. AND one more thing the article does not say is that after you run this script, you ALSO need to run the simple RECONFIGURE command. I am not sure why, but I had to do this for the changes to take effect.

I hope this helps anyone else suffering from the issues I had. Three days of racking my brain, kicking the network team, etc... Not fun and glad to have the management team off my back about this issue.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer