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:
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.
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
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.
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.