How does changing Windows SID affect SQL Server?

Question

Our Windows admins have identified an issue with the way that they clone OS. Apparently, some of the cloned servers end up with the same SID at the OS level. I hear MSFT doesn't support computers that have duplicate SIDs. Therefore, the SIDs on these servers need to be changed. I'm curious how that affects SQL Server. Any ideas? How does it affects clustered database servers?

Answer 1

I would definitely recommend against changing the SID, until after you read: Changing Machine SID With NewSID Breaks SQL Server (And How To Fix It)

Apparently, some of the cloned servers end up with the same SID at the OS level.

I would hazard to guess ALL of your cloned systems have the same SID. GhostWalk can regenerate SIDs for you. Using sysprep on your initial clone image could save you on future systems as well.

If you installed SQL Server DO NOT CHANGE THE SID. Bad things will happen.

Answer 2

Leave the SID alone. NewSID has been retired because Mark Russinovich did some digging and found that the whole "duplicate SIDs == bad!" line we've all had drummed into our skulls over the past decade or so is just a load of nonsense.

See Mark's latest blog entry: The Machine SID Duplication Myth.

Answer 3

Use the now Microsoft owned tool NewSID or sysprep, which is kind of like re-installing windows without all the file copying.

I don't think you can join two computers to the same domain with the same SID, so I'd say that clustered SQL Servers wouldn't stand a chance since servers need to be on a domain.

Answer 4

You can use sysinternals NewSID: http://technet.microsoft.com/en-us/sysinternals/bb897418.aspx

Change the computer name in SQL:

use master
sp_dropserver '<old computer name>'
GO
sp_addserver '<new computer name>', local
GO

sp_helpserver -- will show you the new computer name

Then restart the sql server service.

Answer 5

The only supported way to clone a system is with sysprep. There are a bunch of reason why not to clone a sql server:

-Its not supported by Microsoft CSS.

-SQL will not work properly until it is "renamed".

-If you have reporting services it will be hosed as well.

-The System and Network Service accounts will get new SID and passwords, so if you have used these as service accounts there will be some pain.

-SQL Server creates a good few local groups with the format. SQLServer2005MSSQLUser$$MSSQLSERVER. Its not supported to rename these

In order to rectify the situation I would-

Break the cluster, rebuild the system, install SQL, create a new cluster, run a backup on the server that's not been rebuilt- then stop it, restore that backup to the new cluster, point the application to the new cluster, rebuild the remaining server and add it to the new cluster

Answer 6

If your database does remote transactions using Microsoft Distributed Transaction Coordinator, be aware that cloned machines also have the same MSDTC ID, which is not the SID and not changed by NewSID.

You will see this in the Event Viewer:

The local MS DTC detected that the MS DTC on SERVER has the same unique identity as the local MS DTC. This means that the two MS DTC will not be able to communicate with each other. This problem typically occurs if one of the systems were cloned using unsupported cloning tools. MS DTC requires that the systems be cloned using supported cloning tools such as SYSPREP. Running 'msdtc -uninstall' and then 'msdtc -install' from the command prompt will fix the problem. Note: Running 'msdtc -uninstall' will result in the system losing all MS DTC configuration information.

I resolve it like so:

msdtc -uninstall

Wait a couple of minutes, then

msdtc -install
sc config msdtc start= auto
sc start msdtc