Changing Machine SID With NewSID Breaks SQL Server (And How To Fix It)

<div><p><strong>Background <br></strong>Like a lot of other companies, we use imaging software to get new\repurposed machines up and running quickly. We recently switched to a new imaging product and it seemed to work well. We used it on a couple of SQL Servers that made their way into production and shortly after they went live we noticed intermittent errors with clients trying to connect using Windows authentication that looked like this:</p> <blockquote> <p><font face="Courier New">Login failed for user ''. The user is not associated with a trusted SQL Server connection.</font></p></blockquote> <p>This usually happened a few times a day for a couple of minutes and then the system self-corrected. We also found that right before the login failures occurred was this entry in the system event log:</p> <blockquote> <p><font face="Courier New">Event Type:    Error <br>Event Source:    NETLOGON <br>Event ID:    5719 <br>Description: <br>This computer was not able to set up a secure session with a domain controller in domain [<em>OUR DOMAIN</em>] due to the following: <br>Not enough storage is available to process this command.  <br>This may lead to authentication problems. Make sure that this computer is connected to the network. If the problem persists, please contact your domain administrator.</font>  </p></blockquote> <p>So the NETLOGON service was failing and killing SQL’s ability to authenticate users. While digging through the logs from each image looking for a possible clue one of my coworkers noticed a single line buried deep within that said something to the effect of “SID change failed”.</p> <p>SID stands for “Security Identifier” and is a unique alpha character string that’s used in Windows and Active Directory as the basis for identifying computers and objects in the operating system (<a href="http://en.wikipedia.org/wiki/Security_Identifier" target="_blank">read more about SIDs here</a>). Usually when you apply an image you change the SID to make your server unique, thereby avoiding potential security problems (see <a href="http://support.microsoft.com/kb/162001" target="_blank">Microsoft KB article 162001</a>), only in our case the imaging software failed to do so and as a result we had several machines on the network that had duplicate SIDs.</p> <p><strong></strong></p> <p><strong>One Solution…And The Problem It Causes  <br></strong>One solution to the dupe SID problem is to use the <a href="http://technet.microsoft.com/en-us/sysinternals/bb897418.aspx" target="_blank">NewSID</a> utility from Sysinternals to create a new SID for the affected servers. But here’s the catch: Local accounts, groups, object names, etc. in the OS are based on the machine SID. Change the machine SID and every other SID gets changed too. During installation SQL Server creates a set of local machine groups (e.g. <font face="Courier New"><em>MACHINE</em>\SQLServer2005MSSQLUser$<em>MACHINE</em>$MSSQLSERVER</font>) that are granted rights throughout the OS and in SQL Server so that the services can run. SQL Server stores the logins - and SID - for each of those groups in <code style="font-size: 12px"><span style="color: blue">master</span><span style="color: black">.dbo.syslogins</span></code>. NewSID wasn’t written with SQL awareness so after using it to change the machine SID (which in turn changes the SID for each local group) you’re going to be left with the old group SIDs in SQL, new group SIDs in the OS, a SQL service that won’t start, and probably a lot of people wondering “WTF when is the server going to be back up?”</p> <p><strong></strong></p> <p><strong>A Solution To The Problem Caused By The Solution <br></strong>I’ve been through the SID change exercise a few times now so I’ll share with you what to do if you need to change the SID on one of your SQL severs.</p> <p>0. Find out what Service Pack and Cumulative Update your SQL service is running using the statement below. Get a copy of it and keep it handy because you’ll need it later.</p> <pre class="brush: sql">SELECT SERVERPROPERTY('productversion') , SERVERPROPERTY('productlevel') , SERVERPROPERTY('edition');</pre> <p>1. Change SQL service and SQL Agent service to run as Local Service (or Local System) and manual startup.</p> <p>2. Remove server from domain. Reboot.</p> <p>3. Change SID using NewSID (note: record your old SID before changing it in case you have to roll back!). Reboot.</p> <p>4. Your SQL service is now broken by the SID change. Here’s where it gets fun. The SQL service will start but the SQL Agent service won’t and a couple of other components like DB Mail will throw permissions errors that show up in the SQL error log. To get things back on track resynchronize what’s in <code style="font-size: 12px"><span style="color: blue">master</span><span style="color: black">.dbo.syslogins </span></code>with the new local group SIDs by dropping and recreating the SQL logins (and associated permissions) for the groups. First, using Object Explorer script out the drop and create statements for each local group login. Then use <a href="http://kendalvandyke.blogspot.com/2009/01/scripting-server-permissions-and-role.html" target="_blank">this script</a> I posted previously to script the statements which grant all the necessary server level permissions to each group. Cut and paste the statements that apply to the local groups into your working script. What you’re going to execute for each group will look something like this:</p> <pre class="brush: sql">DROP LOGIN [MACHINE\SQLServer2005MSSQLUser$MACHINE$MSSQLSERVER]; GO CREATE LOGIN [MACHINE\SQLServer2005MSSQLUser$MACHINE$MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]; GO EXEC sp_addsrvrolemember @rolename = 'sysadmin', @loginame = 'SQL2300\SQLServer2005MSSQLUser$MACHINE$MSSQLSERVER'; GO GRANT CONNECT SQL TO [MACHINE\SQLServer2005MSSQLUser$MACHINE$MSSQLSERVER]; GO</pre> <p>Execute your script and restart the SQL and SQL Agent services to get them back up and running.</p> <p>5. Join the server back into the domain. Reboot. </p> <p>6. When you use Configuration Manager to try and change your services to run under a domain account again you’ll get this WMI error:</p> <p><img alt="No mapping between account names and security IDs was done [0x80070534]" border="0" height="145" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiK-aGgRfkNYHSPyNyNh9-utT2YeH3hbJbmuL1iinnMouVyVXuE5tygU60K4NrPxQp32vZjU4EIFQNPdiJ_T-eEU4S3AKh0EaSn_wMs6-k7bZtw5QUqqCugVKzzSMoKbBTy_ZtU1XJ3SZrn/?imgmax=800" style="border-left-width: 0px; border-right-width: 0px; border-bottom-width: 0px; float: none; margin-left: auto; display: block; border-top-width: 0px; margin-right: auto" title="No mapping between account names and security IDs was done [0x80070534]" width="411"></p> <p>To fix the WMI error simply reapply a Service Pack\Cumulative Update that is the same or newer than the SQL service is already running at. But…when you try to do the install it will fail. Dig through the install log in C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix and you’ll find this error:</p> <blockquote> <p><em>MSP Error: 29528  The setup has encountered an unexpected error while Setting Internal Properties. The error is: Fatal error during installation.</em></p></blockquote> <p>Basically the problem is that old SIDs are stored in the registry and need to be deleted. Check out <a href="http://support.microsoft.com/default.aspx/kb/925976" target="_blank">Microsoft KB article 925976</a> for details on what to do. Once you’ve deleted the registry entries you can proceed with reinstalling the current SP\CU. Reboot. Now you should be able to use Configuration Manager to change your services to run under a different account.</p> <p>At this point everything should be running again; the problems caused by changing the SID are resolved and the login errors from the dupe SID should also go away. *PHEW*!</p> <p><strong>Disclaimer</strong> <br>Although I was able to reproduce and fix the problem several times your installation might have some nuances that mine doesn’t, so consider these steps as more of a guide than a hard and fast rule. Although I used NewSID there are other SID changing utilities out there that might create the same problem. As always, test this out in a non-production environment first!</p></div>

Share This

• Twitter
• Google+
• Linked In
• Facebook

Related Posts

Security SQL Server 2005 SQL Server 2008 Tips Troubleshooting