x

Windows user login move to different domain

We recently did a domain migration for several windows users and they have existing logins with the old domain. After the migration, they are able to login via the new domain windows account. When I look in syslogins and server_principals I do not see the new domain user login. I only see the old domain user login. When I run SElect suser_sid('domain\user') I get back a different SID. Am I missing something? Any idea what I can do to resolve that and is this going to cause any issues later.
more ▼

asked Jul 27 '11 at 01:27 PM in Default

LogixDBA gravatar image

LogixDBA
11 2 2 2

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

If the old domain is still up and a trusted relationship was created, they will still be able to login with their old domain accounts into the instance of SQL Server. The only issue that will probably occur is when the old domain is brought down, SQL access will stop.

You will need to issue an ALTER LOGIN [old domain\current login] WITH NAME=[new domain\login] in order to change them. Someone else that has actually gone through a migration may have more details, truthfully I have not.

Quick read from [here][1] might help as well.

[1]: http://blogs.technet.com/b/mdegre/archive/2011/06/27/can-i-move-sql-server-to-another-domain.aspx
more ▼

answered Jul 27 '11 at 01:48 PM

Shawn_Melton gravatar image

Shawn_Melton
5.3k 17 21 29

Thanks for the reply. Would the Alter Login mapped the new domain login to the new SID? What I am doing now is created a security group for the new domain logins and grant them similar access. As you point out when the old domain goes away it could be a problem and i don't know exactly what they did on migrating the users over.
Jul 28 '11 at 09:49 AM LogixDBA
It should hit AD and apply the new SID. Creating the security groups is a good way to do it versus trying to remap all the logins.
Jul 29 '11 at 08:32 AM Shawn_Melton
(comments are locked)
10|1200 characters needed characters left

When you moved users to a new domain an the servers is on the original domain and users have still account in original domain, then even the two domains are not trusted, users is able to automatically connect to the SQL server under the new domain account using the old domain credentials if those credentials are stored on his machine.

I mean in the user account settings on a desktop you can manage stored passwords and you can specify, that your account will automatically use different credentials for other domains.

We use this approach to connect to QA and DEV servers which are located in their own domains.
more ▼

answered Jul 27 '11 at 11:47 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

I forgot about the cached login/password.
Jul 28 '11 at 07:24 AM Shawn_Melton
(comments are locked)
10|1200 characters needed characters left

If you haven't explicitly added the new domain accounts to the sql server, you won't see them in server_principals. If the users can still log in to the server using their new domain accounts, this means they have access via a domain group (which you will see in server_principals).

If this domain group has the appropriate permissions for the users, you won't need to do anything else. If you need to manage their security on a per-user basis, add their new accounts as logins and assign the appropriate permissions to each account.

more ▼

answered Jul 27 '11 at 01:48 PM

KenJ gravatar image

KenJ
19.1k 1 3 11

I was told they mapped the existing domain users on the old domain to the new one. I am guessing it must be something they did on AD. Regardless, it's not a well plan out migration and being the DBA means I need to fix it now.
Jul 28 '11 at 10:00 AM LogixDBA
(comments are locked)
10|1200 characters needed characters left

They probably did a SID migration from the old domain to the new one. At the authentication level it is all done by Windows SID - the login name is just for human readability.

SQL Server doesn't know anything besides what you tell it. Previously you told it that you would accept authentication from SID XXXX and you will represent that by the login AA\BBBB.

The same authenticated SID can still access SQL Server but they now go by a new name of CC\DDDD.

You can most likely choose to do nothing or will have to manually ALTER LOGIN to correct the discrepancies. The bottom line is that SQL Server doesn't really care about the displayed name.
more ▼

answered Jul 29 '11 at 08:54 AM

Blackhawk-17 gravatar image

Blackhawk-17
11.8k 28 30 35

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x83

asked: Jul 27 '11 at 01:27 PM

Seen: 2531 times

Last Updated: Jul 27 '11 at 01:27 PM