SteveOH

I recently came across this error within an Server 2008 Active Directory Environment where I attempted to add a domain user account to Microsoft SQL Server 2008 x64.

As you may see on the internet, the problem is in fact related to SID, but in my situation it was not the SSID of the user, but of the actual server.  The instance of Server 2008 x64 is running in VMWare Workstation 7 and was created by cloning another VM.  This, of course, makes them identical, such that changing the computer name only results in what ‘appears’ to be disparite systems.  In a workgroup this shouldn’t be an issue, but once Active Directory is introduced, it gets wacky.  AD relies on SID’s, and thus, acts unpredictably when 2 machines / users / groups have the same SID.  This is further exagerated when SQL Server is involved, since it creates and / or stores its own SID for the user.

Here are the steps I followed to solve this issue:

1. Remove the server running SQL Server from the domain.  Reboot.
2. Verify the AD Computer Account has been deleted for the system (on a domain controller).
3. Run newSID.exe (found here) and change the SID of the server running SQL Server to a new random SID.  Reboot.
4. Add server back to the domain and verify the account is created within AD.

Once I did all that, the user added with ease.

Good luck.

Now, there are plenty of places to find resources on this issue, because it’s quite common to have to do so.  My issue, was a little different, in that there were multiple layers of security (authentication and authorization) for me to access the files that were needed.  Here’s what the setup looked like:

Web Server -> WorkGroup -> Firewall -> Domain -> File Server -> Network Share -> SAN

As you can see, there are several issues are hand.  Not only do the accounts have to exist to allow for this communication (and an Administrator account is the only way to go), all sorts of permissions have to be valid for this to function (ie. Local Permissions within each OS on each Server, NTFS (file permissions) on each local server, Access permissions through the Firewall, Access permissions of Domain Resources, Local process level permissions, etc).  It was a nightmare, but I believe I figured it out.

There are a few things that needed to be done in this situation.  First, the permissions on the SAN needed to allow Administrators full control.  Additionally, the local user on the file server needed to have administrative privileges, to both the system and the SAN, thus allowing the network share.   Next, the firewall rules needed to allow file traffic (I can’t remember the SMB ports right now – and Windows needs netBios and something else to let file transfers go through, as well as authentication).  Finally, the Web Server local user needs admin rights.

To get this to work, I needed everything involved to run with elevated privileges, from IIS, to each thread in the application.  Since there was going to be a large amount of file movement, as well as resource and memory manipulation, it’s required.

Therefore, the admin credentials were inputted into IIS to map the network share from the Web Server to the File Server.  Next, I had to impersonate the admin user in each thread within the application, a pain, but the guide can be found via Google (if I have time, I’ll track them down again). Next, the machine.config needed to be modified to allow the ASP.NET process to run with the elevated permissions.  The web.config needs to be altered to allow impersonation with the credentials as well.

Now here’s the kicker, and something that’s not quite known but buried within Microsoft’s documentation.  For this to work (Work Group computer to authenticate to another computer) is to mirror the accounts (same username and password) on both servers (these are local accounts, not domain accounts – since the share was local on the server, the domain can be bypassed. If it were a domain resource, we would have to authenticate via NTLM to an Active Directory server, which would have been a bit more complicated).  This allows the hash sent from one system to another to be identical, and thus, you will authenticate (if you know the username and password on one workgroup computer, and it’s the same on another, chances are you’re who you say you are).

This took me a week to figure out, which was not enjoyable.  I hope this saves you some time.