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.
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.
Do not use any of the common passwords.