ReMapping Windows account Login SIDs on Standalone server
-
I’ve got the Windows Account/SIDs Mapping Blues. I know there are already dozens of resources available regarding this topic but the recommended approach seems cumbersome and sloppy to me.
I am now working in a Domain-less world and am migrating a complex SQL Server 2000/sp3a instance from a Windows 2000/sp4 to Windows Server 2003 machine. Both are standalone, which is fundamentally similar to moving to a new Domain. I have the luxery of having the downtime to copy all SQL Server Data and Log files from old to new server.
With no Domain Controller, of course the Windows accounts on the new server all have different SIDs than the old server and won't authenticate to the instance.
I understand how the SIDs in the master.dbo.sysxlogins table must match the account SIDs in the OS, and also how the sysxlogin SIDs must map to the same SID in each database SYSUSERS table. Since I moved the old Master DB, I have no orphan SIDs between Logins and Users. Just OS SIDs that don't match the sysxLogin SIDS. What I'm looking for is a "Been there. Done that" from somebody out there.
1. The ‘recommended’ method for remapping SIDs is to follow the general advice rendered by Microsoft in KB 24087, using the MAPSIDS (or similar) tools:
- DROP all the windows accounts and re-add them. Since most of these accounts are ‘dbo’s, I must first change the dbo’ to sa, then change it back again.
- Modify the SIDs in each DB SYSUSERS table to match the new SID.
This procedure seems incredibly invasive, error prone and dangerous. I have hundreds of database roles and we use cross-database chaining. I'd like to think I can test everything but I could easily miss a hidden flaw somewhere that subtly breaks one of these important relationships and makes 5,000 Users really unhappy with me. The whole approach makes me nervous and smacks of hypocricy "Don't modify System tables (unless of course, they are the most important and fundamental tables in the instance!)"
2. The method I would prefer to use is to find a Utility to change the Windows Server 2003 SIDs (modify the Registry) for all of these new server Windows acccounts to match the SIDs from the old server. If I do this, the SQL Server authentications will all work ‘as is' with no additional work. Has anybody used such a a Utility? I Googled and some tools but naturally lots of caveats accompany them all.
Any recommendations would be appreciated. Thanks in advance,
Stuart
"Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid
-
Maybe I should rephrase this: Anybody who has experience with a utility to change SIDs on a standalone Windows Server 2003/SP1 server gets a beer because without that tool, I'm making a whole bunch of RESTORE scripts.
I still can't believe the only way of addressing sysxlogin.dbo.sids that don't match the Windows OS SID is to DROP existing Windows accounts. In a complex environment with lots of DBs you have to manually script out all the associated DB roles and object permissions and reapply them. My little pingies get really tired from all that button pushing
Stuart
"Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid
-
this might help you out:
replace NTDomain** with your real domain name, otherwise domain users will not be fixed, only SQL login users.
the sid for sa /dbo on all machines everywhere in the world is 1. you do not need to try and remap sa.
the sid for Administrator on every machine in the world is also set in stone, so the built in administrator does not need to be mapped either.
CREATE PROCEDURE sp_Fix_Orphaned_Users
AS
BEGIN
declare @username varchar(128)
declare @Musername varchar(128)
declare @IsNtName bit
declare @sql_stmt varchar(500)
--cursor returns with names of each username to be tied to its respective
DECLARE user_cursor CURSOR FOR
SELECT su.name as Name, msu.name as MasterName , su.isntname
FROM sysusers su
left join master.dbo.sysxlogins msu
on upper(su.name) = upper(msu.name)
WHERE su.sid > 0x00
ORDER BY Name
--for each user:
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @username, @Musername, @IsNtName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username NOT IN ('dbo', 'list of names you want to avoid') --
BEGIN
if @Musername is null
begin
if @IsNtName = 1
begin
print 'if not exists (select * from master.dbo.syslogins where loginname = N''NtDomain**\' + @username + ''')'
print ' begin '
print ' exec sp_grantlogin N''NtDomain**\' + @username + ''''
print ' exec sp_defaultdb N''NtDomain**\' + + @username + ''', N'''+ db_name() + ''''
print ' end'
set @sql_stmt = '--Windows account used'
end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action = ''Auto_Fix'',@UserNamePattern = ''' + @username + ''''
end
end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action = ''Update_One'',@UserNamePattern = ''' + @username + ''', @LoginName = ''' + @username + ''''
end
PRINT @sql_stmt
print 'go'
print '--*** Look here: exec stmt in comment !!! ***'
EXECUTE (@sql_stmt)
print Convert(Varchar, @@ROWCOUNT) + 'Users affected'
END
FETCH NEXT FROM user_cursor INTO @username, @Musername, @IsNtName
END --of table-cursor loop
--clean up
CLOSE user_cursor
DEALLOCATE user_cursor
Print '** end User-sync **'
END
GO
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks for the effort, Lowell. I think I'd still get you a beer...
Actually the script doesn't help me because I don't have "orphan users" where the SIDs in sysxlogins are disassocated from the SIDs in each Database SYSUSERS table. Those relationships are 100% perfect because I moved the physical SQL Data and Log files between servers.
My problem is that the SID token for the Windows accounts created on the OS of the new server are different than the SID tokens created for those same accounts on the original server OS. Without a Domain Controller to provide the SAME SID TOKEN as the account created on the original server, the SID in sysxlogins (from the old server) is now different than the SID in the OS and the authentication to SQL Server for that account is denied.
Now I know that moving physical files between servers ONLY WORKS WHEN MOVING BETWEEN SERVERS IN THE SAME DOMAIN, or where you have Domain Controllers that can pass the original SID tokens to the new Domain Controller and thus to the new server in the new Domain.
I think it may be time for ME to have a beer...
Stuart
"Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid
-
Well I'm having a great thread by myself here...
What I am doing in a test environment is deriving the Windows OS SIDs by creating the accounts (sp_addlogin) in SQL using a named SQL instance on the server. The SIDs in that sysxlogins table are captured and the values inserted into the real sysxlogins table in the Default instance (using a varbinary(85) data type).
Now my sysxlogin SIDs match the OS SIDs and I can login to SQL Server with the Windows accounts (couldn't before). Then I fix the orphan SIDs in the user databases using the standard 'fix-em' tools.
Seems to work, and the amount of work is minimal so I guess I get all the beer.
Stuart
"Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid
-
Is there something similar to the Active Directory migration tool that can move windows accounts between servers rather than between domains? We used it at a site once (although it required a lot of setup and didn't end up working properly) - it is supposed to keep the SIDs, etc of the users.
-
I looked prety hard for something like that when I started this project but never found it. Would have been a lifesaver. For awhile I even thought ADMT had a way to go into "standalone" mode but it required a DC. My other requirement for the tool was that it had to pass the passwords as they are active accounts on the old server for which passwords are unknown.
I chose a tool called Ideal Migration that moves accounts and attributes, including passwords. But it also creates new SIDs on the target machine. In an Active Directory environment, this tool can pass the SID History over to the DC for legacy SID lookups. There is a free Trial Version for download at http://www.pointdev.com/us.htm and I think I've seen it floating around at Microsoft.com as well.
On a final note, I chose the "move all files" migration method, including master, because there were some old legacy objects in master I had to move and it seemed like alot of work to identify them all since I was so new to the hosted applications. That and having come from a Domain world where I had done this often I knew it was easy.
I think my great lesson in this case is to start with identifying and removing unneeded objects in Master DB and scripting all remaining ones, then recreate Master on the new server, passing the SQL Authentication logins via sp_rev_logins and recreating the needed objects. Live and learn.
Thanks for you imput, Ian!
Stuart
"Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid
-
I hope I don't have to go through similar pain - sounds very messy
Will save a link to that tool though, sounds useful.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply