Understanding the Basics of
SQL COMPLIANCE MANAGER
INTRODUCTION
SQL Compliance Manager is a robust auditing and reporting tool for Microsoft SQL Server.
Most of the customers of SQL Compliance Manager use it to help them meet strict auditing and reporting guidelines from regulations such as CIS, DISA STIG, FERPA, GDPR, HIPAA, NERC, PCI DSS, and SOX.
Customers also find a lot of value in reviewing the activities that have occurred on their systems and ensuring that the activity coincides with their change management process.
Customers can use SQL Compliance Manager to review information forensically to paint a picture of user activity over a specified period. This is very useful when trying to identify what information users accessed with a data breach or disgruntled employee activity.
WHY USE SQL COMPLIANCE MANAGER?
As companies collect more and more data, the number of breached records will continue to rise. Estimates show that the average cost of a data breach is $3.8 million per breach. SQL Compliance Manager helps you audit your databases to see when information is being accessed. It also allows you to identify how many records a breach affected.
In addition, there are multiple regulations that are mandating how you should audit who is accessing your data. SQL Compliance Manager audits your databases in greater detail than any other tool on the market to meet these regulation guidelines.
COMPLIANCE REGULATIONS SUPPORTED BY SQL COMPLIANCE MANAGER
SQL Compliance Manager allows you to audit all the information that you may need to capture for any regulation that requires you to audit database activity. SQL Compliance Manager receives updates to incorporate changes in auditing standards across a variety of regulations.
SQL Compliance Manager can meet the audit and reporting needs of most regulatory guidelines. We have incorporated a regulation guideline wizard that provides a good template meeting the needs of these regulations:
- Center for Internet Security (CIS)
- Defense Information Security Agency (DISA STIG)
- Family Educational Rights and Privacy Act (FERPA)
- General Data Protection Regulation (GDPR)
- Health Insurance Portability and Accountability Act (HIPAA)
- North American Electric Reliability Corporation (NERC)
- Payment Card Data Security Standard (PCI DSS)
- Sarbanes-Oxley Act (SOX)
SQL Compliance Manager also supports custom regulation templates that allow you to apply the regulatory standards defined by your organization.
As each organization is different, meet with your legal counsel and data protection officers to define the needs of your organization and how it plans to meet the challenges of database audit and reporting. Then adjust SQL Compliance Manager to meet those needs.
WHAT DOES SQL COMPLIANCE MANAGER AUDIT?
SQL Compliance Manager audits a variety of elements at the server level and the database level. For each setting you should decide if you want to track that information across all databases, for only specific users, for only specific databases, or for only specific users on specific databases.
Trusted users versus privileged users
Trusted users are users where you have decided that collecting their activity is just noise. This refers to your service accounts or other accounts that already have security safeguards in place. Identifying a user, domain group or service group as a trusted user turns off all auditing activity for that entity.
Privileged users are users who have an elevated level of access to your system. This includes your database administrators, system administrators, and developers. They are your human users and not your systems. Identifying a user, domain group or service group as a privileged user allows you to further refine the activity that you want to track for this entity.
Logins, logouts, and failed logins
SQL Compliance Manager captures login, logout, and failed login events to gain insight into who accesses or tries to access your systems.
Logins and logouts
Capturing login and logout events is a great way to identify who has been accessing your system and when they had access to it.
The login and logout events may not always be a one-to-one relationship with your SQL Server logins, depending on how you define your settings and applications. For example, if you are tracking these events at the server or database level, then you may capture a great abundance of activity from an application that logs in and out of the server for every transaction. For this reason, we suggest you may only want to capture logins and logouts for specified privileged users at the server level.
Failed logins
Failed logins could be a powerful indicator that someone is trying to access data they should not. SQL Compliance Manager allows you to audit all of your failed login attempts and take action if you see an abnormal amount of activity.
Security changes
Security changes may be one of the major reasons you want to audit your database administrators. This setting allows you to track who has been adding new accounts and who has been adding, updating, and revoking privileges.
Database activities
SQL Compliance Manager captures database activities including DDL, DML, SELECT operations, sensitive columns, and before-after data.
Database definition language language (DDL)
SQL Compliance Manager allows you to capture information about your changing schema. It can track when you create a new index, a new database, or a new table. You can choose whether you want to capture the actual SQL statements associated with these changes or not.
Database modification language (DML)
SQL Compliance Manager allows you to capture information about modifications that occur on your database. It can track inserts, updates, and deletes. You can choose whether you want to capture the actual SQL statements associated with the changes or not. You can also choose whether you want to track the transaction activity including begin, rollback, and commit.
While you can set this value at the database level, because this setting can create a lot of volume, we recommend setting it at the privileged user level. You can also further refine the data that is captured by only enabling specified tables.
Database SELECT operations
SQL Compliance Manager allows you to capture SELECT operations. Because of the volume that this might create, we recommend you set it at the privileged user level.
Sensitive columns and before-after data
Many regulations require you to audit how users are interacting with personally identifiable information (PII). SQL Compliance Manager allows you to track this information via its sensitive column and before-after data (BAD) activity functionality.
The sensitive columns functionality allows you to capture audit information every time there is a select that is registered on the specified table or column. BAD allows you to capture audit information every time there is a modification (that is, insert, update, or delete) registered on the specified table or column. Detection can happen at the table level, the column level, or for a combination of tables and columns.
Administrative actions
Administrative actions are maintenance activities, such as backing up a database. For auditing reasons, most auditors do not care to capture this information. If you need to confirm that maintenance has taken place or you have had some abnormal behavior reported, then capturing and reviewing administrative actions can be very helpful in troubleshooting that activity.
User defined events
SQL Compliance Manager can also track events that are created by users using Transact-SQL (T-SQL) code.
IDERA’s default configuration settings
When you are first implementing SQL Compliance Manager, the amount of data that you collect can overwhelm you if you decide you want to track that information for all your servers and databases. We encourage you to adjust settings to accommodate the needs of your organization. As a starting point, IDERA recommends you start with these default settings:
Server level:
Server level - privileged users:
- Logins
- Security changes
- Database definition language (DDL)
Database level (on the databases you want to monitor):
- Security changes
- Database modification language (DML)
Database level - privileged users:
- Database SELECT operations
You can find additional information on SQL Compliance Manager settings at “https://www.idera.com/resource-center/videos/sql-compliance-manager-audit-settings”.
REPORTING
Out of the box, SQL Compliance Manager allows you to report on:
- Agent activity
- Alert details
- Application activities
- Audit updates
- Backup activity
- BAD and sensitive column activity
- Configuration settings
- Host activity
- Integrity checks
- Login and permissions history
- Regulatory configuration settings
- Rowcount activity
- Schema changes
- User activity
Any information that you capture with SQL Compliance Manager, you can also send out to an SQL Server Reporting Services (SSRS) report.
DATA ARCHIVING
If the data you have collected has grown too large, SQL Compliance Manager allows you to groom off data that you no longer find necessary. It also can archive off information into a separate repository.
WHAT VERSIONS OF SQL SERVER DOES SQL COMPLIANCE MANAGER SUPPORT?
As of SQL Compliance Manager version 5.8, it supports:
- SQL Server 2005 (SQL Compliance Manager’s agent only)
- SQL Server 2008 (SP1 and R2)
- SQL Server 2012 (SP1)
- SQL Server 2014
- SQL Server 2016
- SQL Server 2017
- SQL Server 2019
SQL Compliance Manager also supports clustered server configurations and availability groups.
For cloud implementations, SQL Compliance Manager supports the above SQL Servers on cloud virtual machines such as Azure Virtual Machine (VM) and Amazon Elastic Compute Cloud (EC2). We plan to add support for Azure SQL Database and Amazon Relational Database Service (RDS) with an upcoming release.
SYSTEM INFRASTRUCTURE OF SQL COMPLIANCE MANAGER
SQL Compliance Manager does not consume a large footprint on your infrastructure. It’s easy to install. The lightweight data collection agent minimizes the server impact.
These are the major aspects of SQL Compliance Manager:
- SQL Compliance Manager’s console (Windows desktop and web-based via IDERA’s Dashboard) allows you to adjust settings and view the data that was collected.
- SQL Compliance Manager’s repository stores the data that is used by SQL Compliance Manager.
- SQL Compliance Manager’s agent sits on audited servers and collects trace files it sends over to the collection service
- SQL Compliance Manager’s collection service takes in the information from the agent and then converts it into audited data you can review via the console.
- IDERA Dashboard’s repository stores information for the web console. This does not apply when you did not install the web console via IDERA Dashboard.
ADDITIONAL RESOURCES
For additional information about SQL Compliance Manager, you can access the following resources:
Download product installation files
Existing customers can access the latest version of IDERA’s products via IDERA’s customer portal at “https://idera.secure.force.com/”.
New customers can download a trial version of SQL Compliance Manager at
“https://www.idera.com/productssolutions/sqlserver/sqlcompliancemanager#getStartedForm”.
Attend product demonstration
New users can request a personalized product demonstration at
“https://www.idera.com/products/sql-compliance-manager/requestademo/”.
New users can also register for a regularly schedule live demonstration on IDERA’s events web page at “https://www.idera.com/events”.
Product documentation
The product documentation is available via the documentation wiki at “http://wiki.idera.com/display/SQLCM/”.
The release notes for SQL Compliance Manager are available at
“http://wiki.idera.com/display/SQLCM/SQL+Compliance+Manager+Release+Notes”.
The list of known issues for SQL Compliance Manager is available at
“http://wiki.idera.com/display/SQLCM/Known+issues”.
User community
The user community forum for SQL Compliance Manager is available at
“https://community.idera.com/database-tools/database-management/security--compliance”
General information
The product page for SQL Compliance Manager is available at
“https://www.idera.com/productssolutions/sqlserver/sqlcompliancemanager”.
The datasheet for SQL Compliance Manager is available at
“https://www.idera.com/~/media/corporate/files/datasheets/idera-datasheet-sql-compliance-manager.pdf“.
New users can access the getting started guide for SQL Compliance Manager at
“https://www.idera.com/~/media/corporate/files/solution-briefs/idera-solution-brief-getting-started-guide-for-sql-compliance-manager.pdf”.
Audit for regulatory compliance standards
For more information on how SQL Compliance Manager can help with FERPA, refer to the solution brief at “https://www.idera.com/~/media/corporate/files/solution-briefs/idera-solution-brief-security-and-compliance-solutions-for-hipaa.pdf”.
For more information on how SQL Compliance Manager can help with GDPR, refer to the solution brief at “https://www.idera.com/~/media/corporate/files/solution-briefs/idera-solution-brief-are-you-ready-for-gdpr.pdf”.
For more information on how SQL Compliance Manager can help with HIPAA, refer to the solution brief at “https://www.idera.com/~/media/corporate/files/solution-briefs/idera-solution-brief-security-and-compliance-solutions-for-hipaa.pdf”.
For more information on how SQL Compliance Manager can help with PCI DSS, refer to the solution brief at “https://www.idera.com/~/media/corporate/files/solution-briefs/idera-solution-brief-security-and-compliance-solutions-for-pci-dss.pdf”.
For more information on how SQL Compliance Manager can help with SOX and COBIT, refer to the solution brief at “https://www.idera.com/~/media/corporate/files/solution-briefs/idera-solution-brief-security-and-compliance-solutions-for-sox-and-cobit.pdf”.
Case study
To see how a medium healthcare enterprise in the USA ensured compliance to make their auditors happy with SQL Compliance Manager, read the case study at “https://www.idera.com/~/media/corporate/files/casestudies/IDERA-Case-Study-Hanger-Orthopedic-Group-And-Their-Auditors-Ensure-Compliance.pdf”.
