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:
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"?