July 1, 2012 at 2:52 pm
Our users will be migrating to a new domain. The sql servers will not ( for now ). My research so far indicates either 1) nothing will break 2) I may have to run alter login to changing existing logins to the new domain -- along with making sure SA owns all of my jobs and maintenance plans. 3) I might be able to create new windows groups in the new domain ( e.g. readonlyUsers, readWriteUsers, SqlAdmins ) which contain the appropriate people and just add the new groups to sql server with specific permissions.
#3 would just leave the old domain logins in sql for now and we could remove them gradually, making sure nothing breaks. I've seen references to assigning specific SIDs, but not sure if that is a good approach.
July 1, 2012 at 5:43 pm
Indianrock (7/1/2012)
Our users will be migrating to a new domain. The sql servers will not ( for now ). My research so far indicates either 1) nothing will break 2) I may have to run alter login to changing existing logins to the new domain -- along with making sure SA owns all of my jobs and maintenance plans. 3) I might be able to create new windows groups in the new domain ( e.g. readonlyUsers, readWriteUsers, SqlAdmins ) which contain the appropriate people and just add the new groups to sql server with specific permissions.#3 would just leave the old domain logins in sql for now and we could remove them gradually, making sure nothing breaks. I've seen references to assigning specific SIDs, but not sure if that is a good approach.
Has a trust been setup between the two domains?
Re: item 2, I am not sure you can use ALTER LOGIN. I think instead you will need to create a new Server Login for each same user or group in the new domain, and then run ALTER USER for each Database User to switch the Server Login it is mapped to from the Server Login of the old domain to this new Server Login of the new domain. Please let us know how this one pans out.
In addition to checking your jobs and MPs I would also recommend checking your database owners as well.
One other reminder to check, do you issue any server level permissions? If you end up creating new Logins as stated above do not forget to audit server level perms (and role membership) and add them to the new logins.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 1, 2012 at 6:57 pm
Not sure if the domains have a trust, but would guess so based on the network admins confidence about things -- I'll ask.
As far as alter login, I was going off of this:
FOR SQL SERVER 2005:
-- Execute the following command in SQL server
ALTER LOGIN [login name] with name=[new name]
-- The logins for the users automatically changes to the new login
For Ex: ALTER LOGIN [AIB\32582] with name=[AD\32582]
July 2, 2012 at 4:57 am
From ALTER LOGIN
The new name of the login that is being renamed. If this is a Windows login, the SID of the Windows principal corresponding to the new name must match the SID associated with the login in SQL Server. The new name of a SQL Server login cannot contain a backslash character (\).
I have used ALTER LOGIN to rename a SQL Login, but never a Windows Login. I used the method I loosely outlined in my last post where I would create a second Windows Login in SQL Server and then using ALTER USER at the database level remapped each user to the new login.
I tried ALTER LOGIN to rename a Windows Login and as expected per the documentation I received this:
Msg 15098, Level 16, State 1, Line 1
The name change cannot be performed because the SID of the new name does not match the old SID of the principal.
I have heard others on this forum talk about forcing an AD login to have a specific SID, which is something you will have control over when creating users in the new domain, however I have heard recommendations against it. Unfortunately I do not recall what those reasons were. edit: was crossing my wires on this line of thought with a different SID issue
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 3, 2012 at 2:29 am
We've found that as long as the user accounts are actually migrated to the new domain rather than recreated then SID history takes care of everything and even though the SQL login shows as OLDDOMAIN\username the user can connect as NEWDOMAIN\username. If it helps our AD team used the Microsoft Active Directory Migration Tool to migrate the accounts.
July 3, 2012 at 5:55 am
Well we have more testing to do. So far it seems that as people are migrated their old domain login is automatically disabled. One co-worker who has already been migrated tried to connect to sql with both his new and old domain logins and couldn't get in either way. I checked the log for the specific error but don't have that in front of me.
We did manage to change all job/maint plan owners to SA on all sql servers and I set something up to report on that regularly.
July 3, 2012 at 9:28 am
Indianrock (7/3/2012)
Well we have more testing to do. So far it seems that as people are migrated their old domain login is automatically disabled. One co-worker who has already been migrated tried to connect to sql with both his new and old domain logins and couldn't get in either way. I checked the log for the specific error but don't have that in front of me.We did manage to change all job/maint plan owners to SA on all sql servers and I set something up to report on that regularly.
What is your sys team using to migrate the users, are they using the AD migration tool?
Also, have you confirmed the SID from OLDDOMAIN is in the SID history in the object in NEWDOMAIN?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 4, 2012 at 3:14 am
We went through this process, gradually migrating three thousand users. It was a pain but life is a lot simpler if you use only groups for access to sql servers not individual logins.
We ended up adding the new groups and having the old and new running concurrently as there were people in both until everyone was migrated and we could remove the old groups - making sure everything was owned by sa or dbo first.
July 4, 2012 at 6:19 am
Our Systems guys finally got most if not all of the domain trust issues worked out so now a migrated user can connect even if no new sql login has been created in the new domain. So I presume that means the SID is the same in both domains.
As to groups, I've fought that for a long time for two reasons. Our Systems team is understaffed so keeping the right people in each group as time goes on is a real problem and the DBAs have no control over that.
Secondly, with numerous environments I'm not sure just 3 groups ( read only, read/write and sysadmins) is going to do it. We may want different people with read/write on different sql servers. So that means more groups and more opportunities for the wrong people to be in each group. Theoretically I could be given the ability to add/remove from domain groups, but there is the separation of duties concern.
If an inexperienced user is mistakenly moved into a read/write group -- which has happened - I'm concerned about the risk to the databases.
Individual logins looks untidy, but it gives the DBA absolute granular control on the permissions of each person.
July 4, 2012 at 7:42 am
Indianrock (7/4/2012)
Our Systems guys finally got most if not all of the domain trust issues worked out so now a migrated user can connect even if no new sql login has been created in the new domain. So I presume that means the SID is the same in both domains.As to groups, I've fought that for a long time for two reasons. Our Systems team is understaffed so keeping the right people in each group as time goes on is a real problem and the DBAs have no control over that.
Secondly, with numerous environments I'm not sure just 3 groups ( read only, read/write and sysadmins) is going to do it. We may want different people with read/write on different sql servers. So that means more groups and more opportunities for the wrong people to be in each group. Theoretically I could be given the ability to add/remove from domain groups, but there is the separation of duties concern.
If an inexperienced user is mistakenly moved into a read/write group -- which has happened - I'm concerned about the risk to the databases.
Individual logins looks untidy, but it gives the DBA absolute granular control on the permissions of each person.
I don't think they'll be the same, but rather the SID from the account in OLDDOMAIN is likely in the SID History of the account in NEWDOMAIN. Have you tried ALTER LOGIN WITH NAME yet? Curious to know if that works as advertised. Thanks for continuing to post, learning a lot on this one.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 11, 2012 at 1:32 pm
How are things going?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 11, 2012 at 5:39 pm
Fine so far. The trusts between domains are working so we havent had to change any logins
July 11, 2012 at 5:43 pm
That's great! I still kind of want to know if Joe Wu's post on ALTER LOGIN checks out or not 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply