Problem
Ideally your SQL instance would be configured to only allow for Windows Authentication. There may be times when mixed mode authentication is necessary at which point you will should configure a method to rotate the ‘sa’ password on a regular basis. You want the new password to be random and secure from others. Not only do you not want anyone else to know the password, you don’t even want to know it yourself.
Solution
SQL Server has an undocumented system stored procedure named sp_SetAutoSAPasswordAndDisable. This procedure will do exactly as the name suggests: it will reset the password and then disable the ‘sa’ login.
The procedure takes no parameters, so the syntax for usage is as follows:
EXEC sp_SetAutoSAPasswordAndDisable
GO
After completion you should see the standard message:
The actual code is as follows:
ALTER procedure [sys].[sp_SetAutoSAPasswordAndDisable]
as
-- can execute only as SysAdmin
if (not (is_srvrolemember('sysadmin') = 1)) -- Make sure that it is the SA executing this.
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’ login will be reset to a random GUID, and then be disabled. Auditors love this aspect because not only is the password secure, but so is the account itself.
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.
Next Steps
- Execute the stored procedure against an instance where you want to have the password for the ‘sa’ login set to a random GUID and then disabled.
- Read more SQL Server security tips
Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and former Microsoft Certified Trainer. He has over 20 years’ experience in the IT industry in roles including programmer, developer, analyst, and database administrator.
LaRock has spent much of his career focused on data and database administration, which led to his being chosen as a Technical Evangelist for Confio Software in 2010. While at Confio, his research and experience helped to create the initial versions of the software now known as SolarWinds Database Performance Analyzer. LaRock joined the SolarWinds family through the acquisition of Confio in 2013.
LaRock is also the Immediate Past President of the Professional Association for SQL Server (PASS) and is an avid blogger, author, and technical reviewer for numerous books about SQL Server management. He now focuses his time working with customers to help resolve problems and answer questions regarding database performance tuning and virtualization for SQL Server, Oracle, MySQL, SAP, and DB2, making it his mission to give IT and data professionals longer weekends.
