14

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

Improve this question
15

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.

Improve this answer
9
  • Don't rename it but disabling it is a really good idea. – Joshua Feb 20 '19 at 22:40
  • 2
    @Joshua Renaming it is a good idea, especially if you're being audited or as part of other security practices that may be required as part of a compliance effort. – Sean Gallardy - Retired User Feb 21 '19 at 0:27
  • 2
    I've spent too much time tracking down broken stuff because somebody renamed a built-in account after it got used. – Joshua Feb 21 '19 at 1:19
  • @Joshua if you renamed and disabled it when you built it that would not be a problem. Of course this is only a going forward solution. Renaming months or years later can be problematic. – James Jenkins Feb 21 '19 at 16:40
  • @JamesJenkins: Ah good you're catching on. – Joshua Feb 21 '19 at 16:41
7

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.

Improve this answer
4
  • 1
    I can see renaming sa, but wouldn't disabling it entirely prevent me from connecting if domain authentication became unavailable in an emergency? (Not that I can recall ever needing to do so, but I'm trying to anticipate all contingencies.) – BradC Feb 20 '19 at 18:22
  • @BradC edited in update addressing your comment – James Jenkins Feb 20 '19 at 18:50
  • Thanks, we'll consider that for a long-term solution. – BradC Feb 20 '19 at 19:08
  • 1
    It's difficult, if not impossible, to have Integrated Authentication completely stop working since the SQL Server service won't run unless it can log in. You don't need to have an Active Directory domain present to use Windows Integrated Authentication; SQL Server can authenticate you against the local security authority. – Hannah Vernon♦ Feb 21 '19 at 15:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.