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

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. Matt currently works for the gaming vendor, IGT, providing services to gaming companies. He also works as an independent consultant, specializing in SQL Server, Oracle and .NET for industries such as gaming, automotive, e-commerce, entertainment, banking and non-profit. Matt specializes in OLTP/OLAP DBMS systems as well as highly scalable processing systems written in .NET. He 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.

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. Digg This!     StumbleUpon     Del.icio.us    RELATED CONTENT SQL Server Security Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server SQL Server security made simple and sensible Blog: Protect your databases from the internal threat Setting up SQL Server Service Broker for secure communication The keys to database backup protection for SQL Server Understanding transparent data encryption in SQL Server 2008 The fine line between not encrypting your databases and breach notification SQL Server security: Controlling access via database roles SQL/Transact SQL (T-SQL) Using DELETE and TRUNCATE TABLE statements to delete data in SQL Server SQL language crash course (just enough to be dangerous) Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server New GROUP BY option provides better data control in SQL Server 2008 Using the OPENROWSET function in SQL Server Loading data files with SQL Server's BULK INSERT statement Importing and exporting bulk data with SQL Server's bcp utility SQL/Transact SQL (T-SQL) Research Database Management and Administration Using traces in SQL Server Profiler Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server How SQL Server 2008 components impact SharePoint implementations Troubleshooting Distributed Transaction Coordinator errors in SQL Server Achieving high availability and disaster recovery with SharePoint databases Clearing the Windows page file and its effect on server performance Deploying a SQL Server virtual appliance for Microsoft Hyper-V How to create SQL Server virtual appliances for Hyper-V RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary data corruption  (SearchSQLServer.com) data hiding  (SearchSQLServer.com) RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary 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.