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














































Different ways to secure the SQL Server SA Login

MSSQLTips author Jugal Shah By:   |   Read Comments (5)   |   Related Tips: More > Security
Problem

What are the different ways to secure the SA login? Everyone is aware of the SA login and its administrator rights, so it is very risky if some unwanted users try to use the SA account or hack the SA account. It is also not advisable to use the SA account in any application or by users. In this tip we cover a few ways to reduce the risk.

Solution

The SA account is created during the installation process and the SA account has full rights in the SQL Server environment. The SA account is well known and often targeted by malicious users, so it is advisable to disable the sa account unless your application requires it.

You can use the following ways to secure the SA login.


Use Windows Authentication Mode

One way of doing this is to use "Window Authentication mode" from the Server Properties dialog which will allow access to only Windows logins and not SQL logins. Microsoft also recommends Windows authentication mode only. One thing to note is that even though you are using Windows authentication, you must use a strong password for the SA account, because anyone can change the authentication mode by updating the registry value and restarting the SQL Services.

To check your current setting you can use these techniques outlined in this tip.

To change this setting in SQL Server Management Studio, right-click the server, click Properties and go to Security page as shown below.

ways to secure the SA login in ssms


Disable the SA Login

Disabling the SA account is a good option to prevent its use. When it is disabled no one can use it in any circumstance until it is enabled. The only disadvantage is that we can't use the SA account in an emergency.

You can use the below T-SQL to disable SA account.

--Query to disable the SA account.
ALTER LOGIN sa DISABLE;

This query will check the status of the SA account. A value of 1 indicates the account is disabled and 0 indicates the account is enabled.

--Query to check account status
SELECT name,is_disabled from sys.server_principals where name='sa'

Rename the SA Login

You can also rename the SA account which will prevent hackers/users to some extent. The one disadvantage is that it does not change the SID for the SA account which by default is 0x01, so someone could find the new name by looking up the SID.

--Query to check account status
ALTER LOGIN sa WITH NAME = [mssqltip];

This query could be used to lookup the name for the SA account based on the SID.

SELECT * FROM sys.syslogins WHERE sid = 0x01


Next Steps


Last Update: 1/17/2011


About the author
MSSQLTips author Jugal Shah
Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

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:
Monday, January 17, 2011 - 10:43:42 AM - Matt Cherwin Read The Tip

If you're in the happy position of not needing the sa account enabled, my preference for security is to use the undocumented* sproc: sp_SetAutoSAPasswordAndDisable

Which does what it says on the tin, as it were. It changes the sa password to a random GUID and disables the account. The real beauty of it is you can easily slot it into password change process in cases where policies, regulations, or auditors require the sa password to be periodically rotated.

*The usual caveats about undocumented features apply, of course.


Monday, January 17, 2011 - 11:32:39 AM - Greg Robidoux Read The Tip

You can refer to this tip: http://www.mssqltips.com/tip.asp?tip=2006

This explains the sproc: sp_SetAutoSAPasswordAndDisable


Tuesday, January 18, 2011 - 5:10:10 AM - JoeK Read The Tip

Really useful article. Thanks. JK


Thursday, July 07, 2011 - 10:24:38 AM - Very long passwords Read The Tip

Note also that modern graphics card setups can crack SQL Server passwords from sys.syslogins at a rate of billions per second; 8 character passwords are discovered in mere days.  Set the SA password to something very long (>14 characters) and completely random.


Tuesday, July 10, 2012 - 2:55:51 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.