Tip

Securing SQL Server with access control, login monitoring and DDL triggers

Matthew Schroeder, Contributor

Typically, SQL server administrators set up logins and users and ignore what occurs thereafter until a potentially serious breach occurs. Then, we scramble to try to figure out what "might have happened" without all the information and then run around and quickly try to fix the problem so it won't occur again. Wouldn't it be great if we have time to implement these measures prior to a failure? Here are the steps we can take to monitor and control access.

Setting up login auditing
The easiest feature to turn on is "Login Auditing". Right-click over the server, go to properties, security. By default, only failed logins are recorded, but you can record both successful and failed logins.

    Requires Free Membership to View


Click on image for larger version

Advanced login auditing
A common question that comes up in SQL Server security is how to control login activities with great detail, denying user access depending on login applications, properties, etc. One method I have seen is that the database administrator creates a job that loops over the logins and kills the logins that are not allowed. The problem with this approach is that the logins have up to a minute to get into the server and do damage (assuming the job runs at the lowest interval of a minute).

A reliable way to prevent this, is to implement a login trigger. The trigger fires after the authentication has completed, but because the user session has not been established yet, the login is unable to process information. The login trigger will only fire if a login attempt passing the authentication phase. Please review the login trigger below, it allows our old user roleaccess to login only three times.

 CREATE TRIGGER connection_limit_trigger ON ALL SERVER FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'roleaccess' AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'roleaccess') > 3 ROLLBACK; END;

Using information from Sys.dm_exec_sessions to increase security
Sys.dm_exec_sessions contains the following fields, many of which you will find useful in limiting access: session_id, login_time, host_name, program_name, host_process_id, client_version, client_interface_name, security_id, login_name, nt_domain, nt_user_name, status, context_info, cpu_time,memory_usage, total_scheduled_time, total_elapsed_time, endpoint_id, last_request_start_time, last_request_end_time, reads, writes, logical_reads, is_user_process, text_size, language, original_login_name, last_successful_logon, last_unsuccessful_logon, and unsuccessful_logons.

Using the login trigger, you can easily limit user access depending on login attempts, applications used, etc. It can also be used to provide login auditing if the session could be high-risk (e.g., unsuccessful login attempts). You can verify the trigger's successful creation in the server trigger listing pictured here:

DDL activity monitoring
The login trigger is a good example of a Data Definition Language (DDL) trigger. Most of us are used to Data Modification Language (DML) triggers, which are used to work with insert, update and delete activities. DDL triggers are designed to monitor data definition events such as keywords create, alter and drop. You want to use these triggers to:

  1. Prevent and/or record attempted changes to database schemas
  2. Start an activity when a data schema changes occur, such as emailing an administrator or logging to a table

This DDL trigger will disable any drop_table or alter_table operations and must be disabled before an activity can take place:

 CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable Trigger "safety" to drop or alter tables!' ROLLBACK ;

A DDL trigger can record the specific T-SQL logic used by running the EventData function. The EventData function basically returns XML data that contains event data for the specific event that fired the DDL. XQUERY can be used to return the specific XML data that is needed. This returns the T-SQL command running SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') for a specific create_table DDL event.

DDL triggers can fire on many DDL events, review the topic DDL Events for Use with DLL Triggers to determine what events you can monitor and which events are "nice to have" vs "essential."

DDL events with database scope

CREATE_APPLICATION_ROLE
(Applies to CREATE APPLICATION ROLE statement and sp_addapprole. If a new schema is created, this event also triggers a CREATE_SCHEMA event.)
ALTER_APPLICATION_ROLE
(Applies to ALTER APPLICATION ROLE statements and sp_approlepassword)
DROP_APPLICATION_ROLE
(Applies to DROP APPLICATION ROLE statement and sp_dropapprole.)
CREATE_ASSEMBLY ALTER_ASSEMBLY DROP_ASSEMBLY
ALTER_AUTHORIZATION_DATABASE
(Applies to ALTER AUTHORIZATION statement when ON DATABASE is specified, and sp_changedbowner.)
   
CREATE_CERTIFICATE ALTER_CERTIFICATE DROP_CERTIFICATE
CREATE_CONTRACT DROP_CONTRACT  
GRANT_DATABASE DENY_DATABASE REVOKE_DATABASE
CREATE_EVENT_NOTIFICATION DROP_EVENT_NOTIFICATION  
CREATE_FUNCTION ALTER_FUNCTION DROP_FUNCTION
CREATE_INDEX ALTER_INDEX DROP_INDEX
CREATE_MESSAGE_TYPE ALTER_MESSAGE_TYPE DROP_MESSAGE_TYPE
CREATE_PARTITION_FUNCTION ALTER_PARTITION_FUNCTION DROP_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME ALTER_PARTITION_SCHEME DROP_PARTITION_SCHEME
CREATE_PROCEDURE ALTER_PROCEDURE DROP_PROCEDURE
CREATE_QUEUE ALTER_QUEUE DROP_QUEUE
CREATE_REMOTE_SERVICE_BINDING ALTER_REMOTE_SERVICE_BINDING DROP_REMOTE_SERVICE_BINDING
CREATE_ROLE
(Applies to CREATE ROLE statement, sp_addrole, and sp_addgroup.)
ALTER_ROLE DROP_ROLE
(Applies to DROP ROLE statement, sp_droprole, and sp_dropgroup.)
CREATE_ROUTE ALTER_ROUTE DROP_ROUTE
CREATE_SCHEMA
(Applies to CREATE SCHEMA statement, sp_addrole, sp_adduser, sp_addgroup, and sp_grantdbaccess.)
ALTER_SCHEMA
(Applies to ALTER SCHEMA statement and sp_changeobjectowner.)
DROP_SCHEMA
CREATE_SERVICE ALTER_SERVICE DROP_SERVICE
CREATE_STATISTICS DROP_STATISTICS UPDATE_STATISTICS
CREATE_SYNONYM DROP_SYNONYM  
CREATE_TABLE ALTER_TABLE DROP_TABLE
CREATE_TRIGGER ALTER_TRIGGER DROP_TRIGGER
CREATE_TYPE
(Applies to CREATE TYPE statement and sp_addtype.)
DROP_TYPE
(Applies to DROP TYPE statement and sp_droptype.)
 
CREATE_USER
(Applies to CREATE USER statement, sp_adduser, and sp_grantdbaccess.)
ALTER_USER DROP_USER
(Applies to DROP USER statement, sp_dropuser, and sp_revokedbaccess.)
CREATE_VIEW ALTER_VIEW DROP_VIEW
CREATE_XML_SCHEMA_COLLECTION ALTER_XML_SCHEMA_COLLECTION DROP_XML_SCHEMA_COLLECTION

DDL events with server scope

ALTER_AUTHORIZATION_SERVER    
CREATE_DATABASE ALTER_DATABASE DROP_DATABASE
CREATE_ENDPOINT ALTER_ENDPOINT DROP_ENDPOINT
CREATE_LOGIN
(Applies to CREATE LOGIN statement, sp_addlogin, sp_grantlogin, xp_grantlogin, and sp_denylogin when used on a nonexistent login that must be implicitly created.)
ALTER_LOGIN
(Applies to ALTER LOGIN statement, sp_defaultdb, sp_defaultlanguage, sp_password, and sp_change_users_login when Auto_Fix is specified.)
DROP_LOGIN
(Applies to DROP LOGIN statement, sp_droplogin, sp_revokelogin, and xp_revokelogin.)
GRANT_SERVER DENY_SERVER REVOKE_SERVER

We have looked at how to setup DDL login triggers to monitor user logins, record them and deny them if necessary. I have also touched on DDL triggers that can be used to monitor, record and deny schema and/or permission changes as they occur on your server. This should give you a good starting point to exploring this area of functionality and create a more secure environment on your SQL Server.

Was this tip useful? Do you want to get more tips like this, or get more information on this topic? Email the editors and let us know.

ABOUT THE AUTHOR

Matthew Schroeder is a senior software engineer who works on SQL Server database systems ranging in size from 2 GB to 3+ TB, with between 2k and 40+k trans/sec. He specializes in OLTP/OLAP DBMS systems as well as highly scalable processing systems written in .NET. Matthew is a Microsoft certified MCITP, Database Developer, has a master's degree in computer science and more than 12 years of experience in SQL Server/Oracle. He can be reached at cyberstrike@aggressivecoding.com.

This was first published in January 2009

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.