Learn how to a build a cloud-first strategyRegister Now


SQL Server domain migration (& renaming)

Posted on 2010-01-04
Medium Priority
Last Modified: 2012-05-08

I have to do an SQL Server 2005 domain migration & renaming

Is my checklist complete?
Is there an impact if the "Database Owner" is from the old domain?

--Domain migration
security tab of the sql server:
check old domain groups/add new domain groups

sql services::
deactivate all sql services
change the service accounts to the new domain accounts

change the domain of the server

Reboot and activate the deactivated services.

--Rename SQL Server with default instance
Pre-Step Delete all your maintenance plans and recreate them after you changed the name
sp_dropserver <old_name>
sp_addserver <new_name>, local

Question by:ALSO_DB
LVL 11

Accepted Solution

Jon Winterburn earned 2000 total points
ID: 26171141
That looks about right. As long as you know the SA password, do not worry about database ownership. If you run into issues with db ownership, you simply need to connect to the db under the sa account and then run the following stored procedure:

exec sp_changedbowner 'sa'

This will change the owner of the db to sa and then you can leave it as that or if you want a different owner on the new domain, simply change it to that. In fact you could even change the db owner to SA before the domain migration (obviously make a note of the current db owner so you can change it back later). That way there is definitely no problem with ownership.

To put your mind at ease, what I have mentioned is also documented at the following link under section "Step 3: How to resolve orphaned users" at: http://support.microsoft.com/kb/314546

I recently had to remove an old NT4 domain from a trust with our AD domain and a user from the old domain was an owner of a db. I did not notice this till after the old domain was removed, but as I am a domain admin and db admin I could still access the db and simply changed the owner to the correct one in the new domain with no problems.
LVL 13

Expert Comment

ID: 26171265
For Renaming: http://msdn.microsoft.com/en-us/library/ms143799.aspx some additional info and troubleshooting tips.

For Domain Migration --> the key is to add the new service accounts. If you use mixed mode, make sure you know a SQL authenticated account just in case. Also look at who owns the jobs and databases. Is it a domain user? Are these users on the right domain? verify after the change also.

Do you have a development instance you can try this on first to prove out your checklist and any gotchas that you bump into in your own environment?
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 26173898
I would also be sure to remember that you should change the Service Accounts with the SQL Configuration Manager instead of Services, or other ways so that the Encryption Keys will be correctly handled.  If you do not do this then if you have encryption keys (Symmetric, Database Master, Assymetric keys) then they will break.  Always a good practice to change them with the SQL Config Manager.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question