• Login
• Become a member
• RSS
• Part of the TechTarget network

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.

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.

Join the conversationComment

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.

All Rights Reserved,Copyright 2005 - 2013, TechTarget