Reset Password and Disable SQL Server SA Account

security

When SQL Server is installed on a server, it creates and enables the SA account by default. While modern security recognizes this is a suboptimal solution, it has always been this way so you need to know how to deal with the issue. As with most questions with anything as complex as a database instance, your specific environment may be unique to your requirements.

Generally speaking your SQL Server instance would be configured to only allow for Windows Authentication. There may be times when mixed mode authentication is necessary, usually because of some vendor application requiring some specific user or even the SA account, at which time you will probably have to address how you will secure your default SA account.

Your auditors for PCI or Sarbanes-Oxley will want all user accounts to have passwords that change at least every 90 days, or the account must be disabled. You should make sure nothing uses the SA account, then change the password and disable the SA account. If you need the SA account, you will need to create a process to rotate the SA password on a regular basis. You want the new password to be random and secure from other accounts, and if you must use the account you want it to meet your network password complexity rules.

password-green

 

If you disable the account and plan to never use the account again, not only do you not want anyone else to know the password, you don’t even want to know it yourself. This is a great reason to use a GUID as a impossible to guess password.

SQL Server has an undocumented system stored procedure named sp_SetAutoSAPasswordAndDisable. This stored procedure will do exactly as the somewhat long name suggests: it will reset the password and then disable the SA account.


The procedure takes no parameters, so the syntax for usage is as follows:

EXEC sp_SetAutoSAPasswordAndDisable
GO

After the stored procedure completes you should see the standard message:

Command(s) completed successfully.

The actual code is as follows:

ALTER procedure [sys].[sp_SetAutoSAPasswordAndDisable]
as
  -- can execute only as SysAdmin
  if (not (is_srvrolemember('sysadmin') = 1))  
  begin
     raiserror(15247,-1,-1)
     return(1)
  end
    
-- Begin a transaction    
BEGIN TRANSACTION
    
  -- Disable Password Policy on the SA Login
  ALTER LOGIN sa WITH CHECK_POLICY = OFF
  IF @@ERROR <> 0
  BEGIN
     ROLLBACK TRANSACTION
     RETURN (1)
  END
  -- Create a New Guid as the random password
  DECLARE @randompwd UNIQUEIDENTIFIER
  DECLARE @stmt nvarchar(4000)
  SET @randompwd = newid()
  SELECT @stmt = 'ALTER LOGIN sa WITH PASSWORD = ' + quotename(@randompwd, '''')
  EXEC(@stmt)
  IF @@ERROR <> 0
     BEGIN
     ROLLBACK TRANSACTION
     RETURN (1)
  END
  -- Now set the policy back
  ALTER LOGIN sa WITH CHECK_POLICY = ON
  IF @@ERROR <> 0
  BEGIN
     ROLLBACK TRANSACTION
     RETURN (1)
  END 
  -- Now set the policy back
  ALTER LOGIN sa DISABLE
  IF @@ERROR <> 0
  BEGIN
     ROLLBACK TRANSACTION
     RETURN (1)
  END 
  -- Commit the transaction
COMMIT TRANSACTION

When you execute this stored procedure the password for the SA account will be reset to a random GUID, and then it will be disabled. Your standard auditors will appreciate this aspect of the change because not only is the password secure, but so is the account itself. You do not know the password, no one knows the password, and the account is disabled. That is as secure as it gets for SQL Server.

If you need to roll your own solution to rotate the password for the SA login, then the sp_SetAutoSAPasswordAndDisable stored procedure may be exactly what you are looking for to begin your customization. Just remember the password must be unique, shouldn’t follow a pattern that can be guessed (server name, MAC Address, etc.), and should be long enough (at least 8 characters long) and complex enough (include uppercase and lowercase letters, numbers, and symbols)  to meet the current network security requirements.

Stolen Passwords

 

Do not use any of the common passwords.

Advertisements

2 thoughts on “Reset Password and Disable SQL Server SA Account”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s