1

In order to help out one of our departments here I've cloned a machine that was on its last legs and is actually hosting some important data. The plan is to migrate to a VMware VM as soon as possible, but in the meantime I cloned the machine onto another PC for testing among other things.

I've renamed the original machine's hostname, so the new machine is running ASP.NET and SQL Server 2005 (Express) and the database-driven website (Intranet-only) is now back up and running but we're getting an error 4200 with ODBC about a login failed.

On checking the event viewer there's the following message:

Error in Event viewer application log

However it seems that SQL server still sees the local accounts as being under a much older hostname (see below:)

old hostname held by SQL server

If I try to add the user "CURRENTHOSTNAME\ASPNET" to the database in question it only sees the old hostname. Is this what's causing my problem ? I'm not sure.

I do know that to get IIS working in the first place I had to do "aspnet_reg.exe -ga CURRENTHOSTNAME\ASPNET" and that fixed my first problem but this second one has me stumped.

The third party company that supply the software are being very slow to help on this. I usually do PHP & MySQL so I've little to no knowledge of Microsoft SQL. If anybody can help me you'd really be saving my bacon as this is dragging on a couple of days now.

Thanks in advance!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TheDavil
  • 706
  • 2
  • 7
  • 22

3 Answers3

0

Renaming a machine with a SQL server instance on it can be tricky. My guess is that you get the old machine name when you run this query:

SELECT @@SERVERNAME

If you do, the old computer name is still stored in the sys.servers table. You might be able to simply follow Microsoft's instructions at renaming, which is just running a couple of stored procedures:

sp_dropserver 'old_host_name'
GO
sp_addserver 'new_host_name', local
GO
Cᴏʀʏ
  • 105,112
  • 20
  • 162
  • 194
  • I did that and rebooted, and now when I SELECT @@SERVERNAME, I get NULL – TheDavil Jan 23 '12 at 10:25
  • no I had done it wrong, dropped it again, restarted SQL server, @@SERVERNAME is now correct. the main problem is now with users. It's still saying 'login failed for 'NEWHOSTNAME\ASPNET' - so I'm going to try to figure out how to sort out the users on the SQL – TheDavil Jan 23 '12 at 11:28
0

By cloning a machine you're opening a big can of worms. My suggestion would be to backup data only and restore it to new computer with fresh installs of everything. But in your case, there are two things that I'd try:

  • after cloning you must reset SID - check this link
  • sql recognises users by their id/guid, so delete all users from sql server/database and add them from scratch
Vnuk
  • 2,673
  • 1
  • 32
  • 49
  • I'm not an expert on this but I will try to do same. Thanks (EDIT) - sorry I forgot to mention I already ran newsid before I wrote this post, it's just the second bit I might have to do. – TheDavil Jan 23 '12 at 10:27
0

Okay it seems to be fixed now - I re-added the NEWHOSTNAME\ASPNET to Security\Logins under "Microsoft SQL Server Management Studio Express" and then it appeared under Databases\MyDatabase\Security\Users" or I was able to add it anyway (can't remember which) - not sure if renaming the server that shows up under SELECT @@SERVERNAME makes a difference, but this error is fixed now anyway

TheDavil
  • 706
  • 2
  • 7
  • 22