Adding a SQL Server sysadmin through single-user mode

It has been quiet around here for awhile, so I wanted to share a tip that I have found useful in a couple of recent occations.

I always perform a backup of the SQL Server database, before implementing any changes on a customer’s Veeam installation. It is probably a bit excessive, since the built-in Configuration Backup can be used to rollback all changes, but I guess old habits die hard, and better safe than sorry.

When installing Veeam Backup & Replication using the embedded SQL Server Express with LOCAL SYSTEM authentication, only the user who performed the initial installation will be assigned as sysadmin on the instance. When I had the use for this procedure, that person was either no longer with the company, or it was uncertain under which user account the installation had been executed. When it is impossible to access the SQL Server instance, one cannot perform a backup.

In most companies, a dedicated service account is created for Veeam. Following these steps, you can verify that this service account has full access to the embedded SQL Server instance.

Check, if the ‘veeamservice’ account has access to the local SQL Server Express instance

sqlcmd -S .\VEEAMSQL2012

If this step does not fail with an error message, you do already have access, and you can skip straight to performing the database backup. If it does fail, we want to delegate access, so that ‘veeamservice’ can access the local SQL Server instance for performing a SQL backup.

Stop all Veeam services

Before proceeding, we must disable Veeam services to ensure no jobs are running.

Get-Service Veeam* | Stop-Service -Confirm:$false

Stop all SQL Server services.

Next, we must stop the SQL Server instance, so we can temporarily run it in single-user mode from the command line.

Get-Service MSSQL*, SQLBrowser, SQLWriter | Stop-Service -Confirm:$false

Run SQL in single-user mode.

Run these commands in two separate elevated command prompts:

Prompt 1:

The following command spawns the VEEAMSQL2012 instance in single-user mode as specified by the -m parameter. By also specifying SQLCMD, we indicate that no connections from any other application than SQLCMD is allowed at this time. Please note that it is case-sensitive, and you will probably get stuck for a while trying to figure that out.

sqlservr.exe -sVEEAMSQL2012 -mSQLCMD

You now have SQL Server running in the foreground of the command prompt.

Prompt 2:

The next commands are used to connect to the SQL Server instance, and perform the actual login for the service account, and adding it to the sysadmin group. Replace MYDOMAIN and veeamservice to fit your needs.

sqlcmd -S .\VEEAMSQL2012

CREATE LOGIN [MYDOMAIN\veeamservice] FROM WINDOWS;
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [MYDOMAIN\veeamservice];
GO
EXEC sp_helpsrvrolemember 'sysadmin';
GO

EXIT

Close prompt 2 and switch back to prompt 1. Press Ctrl+C to stop SQL Server from running in single-user mode.

Restart services

Now that we have added the appropriate credentials, we can restart services back to their normal state.

Get-Service MSSQL*, SQLBrowser, SQLWriter | Start-Service
Get-Service Veeam* | Start-Service

Perform SQL Server backup

Open a command prompt, impersonating the ‘veeamservice’ account, or whatever user was added in the previous steps.

sqlcmd -S .\VEEAMSQL2012 -E -Q "BACKUP DATABASE [VeeamBackup] TO DISK='D:\VeeamBackup.bak'"

… aaaand you are done!

If you found this blog post to be helpful, I would be happy to see you sharing it with your social networks.