Master database in single user mode after restore

Recently I had to restore master database on a SQL instance. One way to restore master database is

1. Restore the master database as user database on same version of SQL.
2. Detach the database from the instance.
3. Copy the files to original instance.
4. Restart the original instance.

Make sure the logical and physical file names are same as original when the database was restored in step 1.

Anyways, after I did the above steps, I noticed the master database was coming up in “single user” mode on the original instance ( Step 4).

I, eventually, realized that it was due to how I was detaching the database. So, when I was detaching the database, I selected the option to “drop connections” in the UI and SQL accomplishes this by putting the DB in single user mode before it detaches.

That’s the reason why it was coming up in single user mode on the original instance when the files were copied over.

Advertisement

Custom SQL Job Failure Notifications

Job failure notifications is a common way to send notification when a SQL job fails. Setting up job notifications is fairly straight forward. However, sometimes there may be a need to do custom logic on when these notifications need to be sent. Since the job history is logged in the msdb database, we can query the tables and build out of the box solutions. One solution I had to do recently was to send notification email only if the job failed 5 successive times.

In order to do this, whenever the job fails, it should execute a step( part of the job) to check the last 4 run’s job final output (step id = 0) and if they all failed, send an email notification.

Below code shows how this can be done.

Declare @JobID uniqueidentifier
SET @jobID = CONVERT(uniqueidentifier,$(ESCAPE_SQUOTE(JOBID)))

IF ( SELECT MAX(run_status)
FROM (SELECT A.run_status
FROM Msdb.dbo.sysjobhistory A 
INNER JOIN Msdb.dbo.sysjobs B ON A. Job_id=b.job_id
WHERE step_id=0 AND B.Job_ID = @jobID
ORDER BY A.instance_id DESC OFFSET 0 ROWS  FETCH First 4 ROWS ONLY) A)= 0

BEGIN
EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = '<<SQLMailProfile>>', 
    @recipients = 'youremail@email.com', 
    @body = '<<email body>>', 
    @subject = '<<email subject>>' ; 
END

 

Registering SPN for SQL Server Service Accounts

When setting up a new SQL Server, one of the important step is to register the SPN of the service account.This registration is not required if the service account is domain administrator or if you give explicit permissions to self register the SPN for the service account. Both options are not wise, so anytime a new SQL Server is set up or service account is changed – we have to manually register the SPN on the domain. This task needs to be done by someone who has domain admin rights.

Registering SPN’s enables kerberos authentication for delegation and for double hop scenarios such as linked server, you can impersonate the actual user other wise you have to specify SQL Account and this can become security loophole in your system.

Below are the steps to enable kerberos delegation:

1. Register SPN for serviceaccount with all possible combinations
SetSPN -A MssqlSvc\ComputerA.domainname.com domain\serviceaccount
SetSPN -A MssqlSvc\ComputerA domain\serviceaccount
SetSPN -A MssqlSvc\ComputerA.domainname.com:1433 domain\serviceaccount
SetSPN -A MssqlSvc\ComputerA:1433 domain\serviceaccount

In case of a clustered instance, specify the Virtual SQL Cluster Name(without the instance name). You have to mention the TCP port the SQL is running on.

2. Enable the service account to trust for delegation. This is a setting in the AD.You can choose either to trust for all delegation or if you want, you can specify which service to delegate.

3. Make sure TCP/IP protocol is enabled and named pipes is disabled.

If you have any other SQL components such as Analysis service or Reporting service, you can register them as well to use Kerberos.

Example of SSRS SPN registration:
http/computername.domainname.com:80 domainname\serviceaccount
http/computername.domainname.com domainname\serviceaccount
http/computername domainname\serviceaccount
http/computername:80 domainname\serviceaccount

If you use performance dashboard reports, you need to have kerberos authentication for SSRS. These dashboards are very useful and you can download from github.

Also, If you want to list all the SPN’s registered for a service account, you can use
SetSPN -L domainname\serviceaccount

If you want to delete a spn, you can use
SetSPN -D MssqlSvc\ComputerA.domainname.com domain\serviceaccount
SetSPN -D MssqlSvc\ComputerA domain\serviceaccount
SetSPN -D MssqlSvc\ComputerA.domainname.com:1433 domain\serviceaccount
SetSPN -D MssqlSvc\ComputerA:1433 domain\serviceaccount