sql 2005 renaming problem

Question

Post reply

sql 2005 renaming problem

Barkingdog

SSCoach

Points: 18670
More actions
April 24, 2006 at 10:06 am

#113009

I installed sql 2005 on a box, then later renamed the box. Everything seemed OK. But when I tried applying sql 2005 Sp1 I could not get past the authentication page, regardless of the form of authentication used. I found that while the Reporting Services engine was running, the owner of the reporting databases was <my old, non-existent, computer name>\<my user name>. No wonder the service pack complained during the authentication stage! When I renamed the box I expected all sql references to the old computer name to be changed to the new name.
Can you offer any advice on how to rename a sql 2005 successfully?
TIA,

Bill

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

icata Hall of Fame Points: 3176 More actions · Answer 1

icata

Hall of Fame

Points: 3176

April 25, 2006 at 10:09 am

#634095

can you change the owner of the database to sa?

Barkingdog SSCoach Points: 18670 More actions · Answer 2

Unfortunately I didn't try that (good idea).

Bill

dharper3 SSCrazy Points: 2391 More actions · Answer 3

In order for the sp1 apply for reporting services the db engine must be started to allow authentication. SSRS seems to be the only product in the SP1 to complain if the db engine is not running.

Arfat Chougulay SSC Enthusiast Points: 178 More actions · Answer 4

I also faced the same problem as u did,

but to be just better then smart and let the thing work and keep going ahead,I renamed my machine back to old name installed SP1 which worked fine and then again switched back to the new name, SMART isnt it.

any way here is the trick to change the server name and be on the safe side.

After starting SQL Server, you need to take care of the sysservers table in the master database. SQL Server stores the local machine name here and this will not match if you have renamed the machine in Windows. This is also easy to fix:

EXEC sp_dropserver '<old_name>'

go

EXEC sp_addserver '<new_name>', 'local'

go

You need to replace <old_name> above with the old machine name and <new_name> with the new machine name.

Hope this will do the need full for you

Barkingdog SSCoach Points: 18670 More actions · Answer 5

Great!

Everything you said checks out. The more of these types of "problems" I encounter the more I think Microsoft must have been rushed to get this product out th door.

TIA,

Bill

P.S. Here's another one I found: on an x64 bit machine, Microsoft did not write an x64 bit JET provider. In other words, their SSIS tool (the new "DTS") can not import an Excel spreadsheet into a table in x 64 mode. (One has to run in a WOW environemnt in 32-bit emulation). Yes, their 32-bit SSIS can impot an Excel spreadsheet.