Htydjtk

We discovered a SQL "sa" account being used in a way it should not have been, so we are changing sa passwords on all our SQL instances.

(We have SQL 2005 through 2017 servers running in mixed authentication mode. All users and applications should be using either domain accounts or non-sa SQL accounts to connect. I have been monitoring, but have not found any other apps, users, or non-internal spids using the sa account.)

A few questions:

Q1: Does changing the sa password require a SQL restart?

I found a few references that say a SQL service restart is required after changing the sa account password:

• DBA SE: Changing sa password


• SQLAuthority: Change Password of SA Login Using Management Studio

Is that true? Or only if I'm changing the authentication mode? Or only if I routinely log on as sa?

This SQL Server Central thread even suggests changing it could impact existing SQL agent jobs and other stuff; is that a concern? Or only if someone has hard-coded the SA account into an SSIS package or something?

(In case it matters, we use domain accounts for the SQL service and SQL agent service, and domain proxy accounts for jobs that call SSIS packages or PowerShell scripts.)

Q2: Can I change the sa password the "normal" way?

Can I reset it like I would any other account? Using SSMS, or more likely via:

ALTER LOGIN sa WITH PASSWORD = 'newpass';

Or would I have to enter single-user mode or something that would require planned downtime? (Note that I'd be running this from a domain account, not while connected as "sa".)

Q3: Should we try to do this password rotation on a regular basis? Or only when we find an issue?

Is this a recommended "best practice"?

Q1: Does changing the sa password require a SQL restart?

No, but changing the authentication mode does. Since you're just changing the password and the authentication mode is already set to mixed, you're good to go with just changing the password.

Q2: Can I change the sa password the "normal" way?

Yes, it's just another SQL Login account.

Q3: Should we try to do this password rotation on a regular basis? Or only when we find an issue?

To be quite honest, I would disable and rename the SA login. This way it won't be used, at all and if you need a highly privileged login, then you can make one as needed.

This is a closing the barn door after the horses already ran off question.

You should have renamed and disabled the sa account when you built the instance.

Any time you have a well-known account, like administrator on a Windows system or sa for SQL Server, you should take certain steps to secure it. Let's look at specifically what you should do with sa:

Set a hard to guess password.

Rename sa.

Disable sa.

Ensure that no other accounts exist named sa.

Source

If you are keeping the 'sa' account as an emergency way to get SQL access there are safer ways see: Connect to SQL Server When System Administrators Are Locked Out If you don't have network account access, you have bigger problems then not being able to connect to SQL.