November 26, 2003 at 12:03 pm
We are running SQL Server 2000 on a couple servers and our network team is converting NT users to Active Directory. Each of our SQL servers has 100’s of people that have rights to the server and to several databases. The NT account of these folks appears something like XX.YY.ZZ/jdoe, where XX.YY.ZZ is the domain name in which these users belong to. When a person’s account is migrated over (with SID history) to AD, the account name becomes AA/jdoe, where AA is the new domain of the ASD.
When the person is migrated to AD, the person can no longer get to the servers or their databases, however this is a hit or miss issue. Sometimes when an account is migrated over, the person has no problems gaining access to the servers. Other accounts experience Access is Denied when connecting to a server using Windows Authentication. It also appears as though SQL Server is not reading the SID history correctly all the time. SID history appears to be working for other resources such as file shares, printers, etc.
If anyone has experienced something like this, please let me know.
Thanks-
Lee
Edited by - Lee Forst on 11/26/2003 1:30:50 PM
November 26, 2003 at 12:38 pm
I'll give this a shot but I'm not real confident in what I thought was correct, since you are saying that even a few of them are still working. Hopefully someone can correct/confirm what I say.
I would have expected you to have to re-create all of the logins that were based off of NT security. In a normal situation, when an SID changes for any reason, such as an NT account being removed then re-created, the link for the login in SQL to the old SID no longer matches the new SID so it is invalid.
Either I am wrong, or perhaps there is another explanation - such as the people that still work are inheriting default permissions due to the administrator priveleges granted for people that are domain admins?
At least if you are using Roles all you have to do is recreate the logins by individual or group then re-assign them to roles. Otherwise, you have to re-do all of the object permissions also, for each login, when you recreate the login, not just role assignments.
{edit: removed "don't" from last sentence}
Edited by - cmore on 11/26/2003 12:39:50 PM
November 26, 2003 at 12:43 pm
Another thing that may be allowing them in, other than what I mentioned before, would be if they had SQL logins created for them (ones that bypass NT/AD security) and are using those to log in.
November 26, 2003 at 12:46 pm
We feel the issue is that SQL Server doesn't recognize SID History as other resources do. I think there is something that can be installed on SQL Server that makes it AD aware. Does anyone know what this contains?
Edited by - Lee Forst on 11/26/2003 1:31:16 PM
November 26, 2003 at 12:53 pm
Still don't see how any of your old SIDs are valid.
This will be an interesting thread for me since we plan to migrate to AD soon. If it is actually possible to migrate and not re-create the NT(AD?) logins in SQL it'll save me a lot of headache.
November 26, 2003 at 1:27 pm
You are right cmore. We are facing that problem now. Here is the only way we see of getting around this without a lot of manual work.
First, create a script that gives us all users for the server and the databases with all the permissions for that user. This information would be based on the current NT 4 account. Then, we are going to have to go through AD after an account has been migrated and get the new SID for the AD account. Then we are going to have to run some scripts to add the accounts that have been converted to AD to the SQL Server using the new account name and the appropriate SID and grant the proper permissions. To me, this seems crazy and dangerous. I can't help think there is some kind of migration utility someone has written to resolve this issue.
November 26, 2003 at 1:30 pm
One thing I forgot to ask. Can/should we be able to change the SID and name values in the master.sysxlogin and database.sysusers tables? If so, this would not require us to add a new account. I know you have to turn on something to change system catalogs, but I have read tinkering with these tables is a bad idea.
December 1, 2003 at 7:48 am
Maybe this would work:
sp_grantlogin [@loginame =] 'login'
sp_grantlogin
sp_revokelogin
Note that the SQL security account procedures and the NT security account procedures differ.
If you got a snapshot of all of the logins/role membership/permissions for the NT accounts, and their NT account name has not changed, then you could scan through them and use the system procedures to re-create it all.
You are right; there may be a tool out there.
December 1, 2003 at 7:51 am
As for editing the system tables, the only time I have ever done that is when 'copying' diagrams, because there was no other way to do it. At least with security you have the system stored procedures. Plus there are some for role/owner/permission setting.
I'd only attempt it as a last resort, and only if you test it on a development server and have a lot of accounts to fix.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply