SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Protecting the SA Account

The sa account is a well, known, built in account for SQL Server. Years ago, in previous

versions, I’d see people often use the “sa” account for development, usually with a blank password. Even those speakers and experts often showed demo code with a blank “sa” password.

Ugh, a horrible example that was repeated over and over until the SQL Server setup program stopped allowing blank password. However people then had “12345” or other simple things to type on stage. I guess some habits never change.

In any case, I saw someone ask recently about changing the “sa” password in response to auditors’ requests. This person asked about how to randomly set this on a regular basis.

It’s easy. Even if you have Windows Auth only, you can use this script.

DECLARE @pwd UNIQUEIDENTIFIER = NEWID()
, @new VARCHAR(50);

SELECT @new = CAST(@pwd AS VARCHAR(50))

EXEC sp_password @new = @new, @loginame = ‘sa’

That seems to work fine in testing. I get a new sa password each time, whether I have Windows Auth or Mixed mode set. If I change the password with only Windows Auth, the last password set works if I change to mixed mode.

This is a great little script to set up in a job and run it monthly. This will protect your sa account in case someone ever enables mixed mode.

If you need the account, just change the password then, for the job/application that needs to run. Then run your job again to reset it.


Filed under: Blog Tagged: security, sql server, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...