Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Secure and disable the SQL Server SA Account

MSSQLTips author Thomas LaRock By:   |   Read Comments (3)   |   Related Tips: More > Security
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:

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))  -- 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


Last Update: 5/14/2010


About the author
MSSQLTips author Thomas LaRock
Thomas LaRock is a seasoned IT professional with over a decade of experience. He is a Senior DBA for Confio Software and SQL Server MVP.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Saturday, May 22, 2010 - 10:28:36 AM - JimJ Read The Tip

I am not sure why you would want to create an sa password that can't be accessed.  I find that I need SQL Server authentication when I am accessing a database (e.g. DSN) and I have not been joined to a network. In that case, the IP address\Instance Name and SQL Server authentication is what I have to use for the short interval to load the application and test appropriately.  What other alternative should I use?


Tuesday, June 26, 2012 - 3:48:45 PM - Tim Lehner Read The Tip

@JimJ, I believe the idea is that you would create a different sysadmin account when installing SQL Server or at the earliest possible time, and use that new account (or other less-privileged accounts as appropriate) to perform admin duties.  This allows you to safely disable sa and essentially defeat attacks against it.


Tuesday, July 10, 2012 - 2:57:21 AM - VAhid Read The Tip

Hello

I have a database server that users are connected through to it but i dont know a user is that drop database on server

i write a trigger and log history but again user can delete my database on server and trigger cant prevent that it

 

 

CREATE

 

Trigger [LogDB] onall

server

For

 

 

DROP_database,ALTER_database,ALTER_TABLE,DROP_TABLE,CREATE_DATABASE,Drop_Trigger

 

as

 

set

 

nocount

on

PRINT

 

 

 

'You must contact a DBA before dropping or altering tables!'

 

 

rollback

 

Declare

 

@CommandText

nvarchar(2000),

@ComputerName

nvarchar(100),

@ApplicationName

nvarchar(100),

@LogiName

nvarchar(100),

@LogDate

DateTime

SELECT

 

@CommandText =EVENTDATA().value

 

(

 

'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'

)

 

 

select

 

@ComputerName

=HostName,

@ApplicationName

=Program_name,

@LogiName

=suser_sname(),

@LogDate

=GetDate()

from

 

sys.sysprocesseswhere spid=

@@Spid

 

 

Insert

 

LogError.dbo.LogEvents

 

(

 

CommandText

,

ComputerName

,

ApplicationName

,

LogiName

,

LogDate

 

)

 

values

 

(

 

@CommandText

,

@ComputerName

,

@ApplicationName

,

@LogiName

,

@LogDate

 

)

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.