通过

Changing Domains on a SQL Server

  • 项目

Someone recently asked me what issues might arise when changing a server's domain and the server is running SQL Server. Here are the possible issues I'm aware of as far as SQL Server is concerned:

1. SQL Server SysAdmin (SA) Access. The most important issue is if you only have SQL Server sysadmin via domain accounts/groups, which is normal if STIG'd. In this case you should temporarily enable the sa account and ensure you know the password, until you get the new domain sysadmin accounts/groups working.

2. Service Account. The new domain service account(s) will need the same privileges as the old domain service accounts. If there are no custom permissions, just use the SQL Server Configuration Manager to change the service account while being logged in as a domain administrator. If it's in a very high-security environment, you might have to make sure that your domain admin account has all the standard permissions.

3. SQL Server Domain-based Logins. Logins that are based on domain accounts or domain groups will need to be recreated. The old ones can be scripted out to recreate all their permissions.

4. Changing IP Addresses. If required by the domain change, and if it's clustered, then the virtual server IP address must change in addition to the individual nodes.

5. Service Principal Names (SPNs). These are always used by clustered instances, and sometimes used by stand alone instances. If the IP addresses are changing, the old SPNs need to be dropped and new ones created. If the SQL Server service account is used to manage its own SPNs (not recommended) then the new domain service account will need to be granted the "Write servicePrincipalName" privilege.

If the servers are STIG'd then you should have test servers, and you should change their domain first, to see if any other problems arise.

Comments

  • Anonymous
    January 01, 2003
    This is good content. Great job! Have you thought of dropping some content on TechNet Wiki for collaboration purposes?

  • Anonymous
    January 01, 2003
    Thank, Ed. I wasn't familiar with TechNet Wiki, but I'm checking it out now...

  • Anonymous
    June 27, 2013
    Can you suggest how to script login and their permission ? Can this be done by Alter command.

  • Anonymous
    July 01, 2015
    John - You may check this.
    https://support.microsoft.com/en-us/kb/918992