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.
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:
- Prevent and/or record attempted changes to database schemas
- 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)','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
(Applies to CREATE APPLICATION ROLE statement and sp_addapprole. If a new schema is created, this event also triggers a CREATE_SCHEMA event.)
(Applies to ALTER APPLICATION ROLE statements and sp_approlepassword)
(Applies to DROP APPLICATION ROLE statement and sp_dropapprole.)
(Applies to ALTER AUTHORIZATION statement when ON DATABASE is specified, and sp_changedbowner.)
(Applies to CREATE ROLE statement, sp_addrole, and sp_addgroup.)
(Applies to DROP ROLE statement, sp_droprole, and sp_dropgroup.)
(Applies to CREATE SCHEMA statement, sp_addrole, sp_adduser, sp_addgroup, and sp_grantdbaccess.)
(Applies to ALTER SCHEMA statement and sp_changeobjectowner.)
(Applies to CREATE TYPE statement and sp_addtype.)
(Applies to DROP TYPE statement and sp_droptype.)
(Applies to CREATE USER statement, sp_adduser, and sp_grantdbaccess.)
(Applies to DROP USER statement, sp_dropuser, and sp_revokedbaccess.)
DDL events with server scope
(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.)
(Applies to ALTER LOGIN statement, sp_defaultdb, sp_defaultlanguage, sp_password, and sp_change_users_login when Auto_Fix is specified.)
(Applies to DROP LOGIN statement, sp_droplogin, sp_revokelogin, and xp_revokelogin.)
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 email@example.com.