Sql-server – Change SQL Server service account without restarting SQL Server

configuration-managerSecuritysql server

For security reasons, we want to change the password that starts the SQL Server service everyday. Is it possible to do this without restarting the service?

If it is possible will the changes be immediately applied, or will I eventually need to restart the service to get the new password?

We were thinking of doing this change in the services option of the windows OS and not in SQL Server configuration manager.

Best Answer

I would not go through group managed service accounts initially, depending on the issue, environment you are dealing with.

I am not sure what version of sql and the type of security issues you have, but from previous experience permissions to sql server service and agent accounts take affect after a restarting of the service.

I would rather use the sql server configuration manager whenever possible

If you are concerned about security you most definetely need to be able to restart this service, and if that is not possible, then your environment is asking for a good availability group configured, so you are always on, because you need to bear in mind you need patching your servers on a regular basis, and that requires reboots most of the time.

Please review what is preventing your from restarting and address that with management.

Related Solutions

Sql-server – Change SQL Server settings without restarting services

If you want to change the Authentication Mode, you can do this by running the following SQL

USE master
DECLARE @INSTANCEID VARCHAR(30), @STRVERSION VARCHAR(30), @SQLVERSION VARCHAR(30), @CMD VARCHAR(2000)
SET @SQLVERSION = (SELECT CONVERT(VARCHAR(20),(SERVERPROPERTY('productversion'))))
SET @INSTANCEID = ((SELECT CAST(SERVERPROPERTY('InstanceName') AS VARCHAR)))

IF (SELECT REPLACE(LEFT(@SQLVERSION,2),'.','')) = 10 
    SET @STRVERSION = 'MSSQL10'
ELSE
 IF (SELECT REPLACE(LEFT(@SQLVERSION,2),'.','')) = 9
  SET @STRVERSION = 'MSSQL'

IF @INSTANCEID IS NULL AND @STRVERSION = 'MSSQL'
 SET @INSTANCEID = 1
ELSE
 IF @INSTANCEID IS NULL AND @STRVERSION = 'MSSQL10'
  SET @INSTANCEID = 'MSSQLSERVER'

SET @CMD = 'xp_regwrite ' + 'N' + '''HKEY_LOCAL_MACHINE''' + ',' + ' N' + '''Software\Microsoft\Microsoft SQL Server\'+ @STRVERSION +'.'+ @INSTANCEID + '\MSSQLServer'''+','+' N'+'''LoginMode'''+', '+'REG_DWORD'+','+ ' 2' --2 is mixed auth.

EXEC(@CMD)
--PRINT @CMD

You may also have to ensure that the sa login is enabled.

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;
GO

You will however still have to restart the SQL Server service.

Sql-server – Changing the SQL Server service account – any additional considerations

Your best bet before you change the service account would be to manually make sure all the permissions are correct. This will allow you to also document what changes you make so in the future if you need to move the instance to a new server or set up another instance you will all ready have every thing you need.

This is also my preferred method of doing things since I have a slight mistrust of the built in GUI tools for SQL Server and prefer the command line or Windows folder management GUI for security modifications. (too many "Surprises" in the way SSMS does things"

Once you are ready to change the service account, the Sql Server Configuration Manager is the only way to go. There are some other changes that the manager makes to the system besides just changing the account every thing runs under.

Best Answer

Related Solutions

Sql-server – Change SQL Server settings without restarting services

Sql-server – Changing the SQL Server service account – any additional considerations

Related Question