Tag: SA

SQL Server Tip of the Week – Domain Migration

I thought I would try a new feature, and hopefully get a schedule of tips I want to share with the world.

In the last week or so the subject of Domain Migration has come up for the millionth time in my career so far (slight exaggeration)

Switching domains is easy, log in on one domain, go to the system settings in Windows and change to the other domain, with a reboot committing the change fully. This is great, but what about the applications?

SQL is possibly the most popular DB platform out there, and up until the last couple of months has been restricted to the MS Windows platform. As a Microsoft product it ties in heavily to the operating system and Active Directory. Typically most DBAs will configure there SQL installation with dual authentication, Windows for binding Windows Accounts and SQL Authentication for local based security. The majority of applications these days will utilise Windows Authentication as this make the management of security policies such as password expiration and removal of users a little simpler, and generally management is from one place, i.e Active Directory. The only issue here is if you’re doing a manual Domain Migration (i.e no ADMT style tools) then as soon as you change the domain the SQL server sits on, the applications will no longer work and the users will not be able to connect. This is where the SA user is god, as it is local to the SQL installation not the domain/Windows element.

My advice for a manual SQL Server domain migration is simple:

  1. Ensure you have dual auth enabled
  2. Ensure you know your SA password (if not, create a new SA style user and note the password you use)
  3. Change SQL to use builtin service accounts (not a domain service account) before the move – can be changed afterwards to domain users under new domain.
  4. Change ownership of all DBs to SA
  5. Change ownership of all Task Agent jobs to SA

These should be done before switching domain!

For steps 4 and 5 I have the following scripts which I have used a few times now, and they do exactly as described, changing the ownership:

Change DBs ownership to sa:

EXEC sp_MSforeachdb 'EXEC [?]..sp_changedbowner ''sa'''

Change Job ownership to sa:

DECLARE @name_holder VARCHAR(1000)

DECLARE My_Cursor CURSOR

FOR

SELECT [name]  FROM msdb..sysjobs

OPEN My_Cursor

FETCH NEXT FROM My_Cursor INTO @name_holder

WHILE (@@FETCH_STATUS <> -1)

BEGIN

exec msdb..sp_update_job

        @job_name = @name_holder,

        @owner_login_name = 'sa'

FETCH NEXT FROM My_Cursor INTO @name_holder

END

CLOSE My_Cursor

DEALLOCATE My_Cursor

 

One additional step once the migration is done and the server is back up on the new domain, would be to login as sa, and add in the security groups/users from the new domain that need access to the server. I typically add the Domain Admins as SQL sysadmin users. To achieve this for the current domain you can run:

USE [master]
GO
declare @DomainName nvarchar(32)
set @DomainName = (SELECT DEFAULT_DOMAIN()[DomainName])
declare @DomainAdmin nvarchar(64)
set @DomainAdmin = @DomainName + '\Domain Admins'
print @DomainAdmin
EXEC master..sp_addsrvrolemember @loginame = N'NT AUTHORITY\SYSTEM', @rolename = N'sysadmin'
EXEC master..sp_addsrvrolemember @loginame = N'NT AUTHORITY\NETWORK SERVICE', @rolename = N'sysadmin'
EXEC master..sp_addsrvrolemember @loginame = @DomainAdmin, @rolename = N'sysadmin'
print @DomainAdmin + ' Has been Added as sysadmin'
GO

Incidentally this was one of my early attempts at writing SQL scripts without googling around! – not this also adds SYSTEM and NETWORK SERVICE (local builtin users) as sysadmins.