Defending Your SQL Server: Unraveling Vulnerabilities and Hacker Strategies: Question and Answer

Ans: Creating a database audit specification recorded in the event log can be achieved using the clause WITH (STATE=ON). The CREATE DATABASE AUDIT SPECIFICATION and ALTER DATABASE AUDIT SPECIFICATION statements function similarly to their counterparts for server audit specifications, as you've already learned.

Around 24 action groups at the database level, such as changes in ownership or permissions, can be monitored for auditing purposes. The comprehensive list is available in the SQL Server Audit Action Groups and Actions article, accessible at http://technet.microsoft.com/en-us/library/cc280663(v=sql.110).aspx. Additionally, specific actions on database objects like schemas, tables, views, stored procedures, etc., can be tracked. The seven database-level audit actions are SELECT, INSERT, UPDATE, DELETE, EXECUTE, RECEIVE, and REFERENCES.

-- Create an event log audit
USE master
GO
CREATE SERVER AUDIT MyEventLogAudit TO APPLICATION_LOG ALTER SERVER AUDIT MyEventLogAudit WITH (STATE-ON)
-- Create a new database
IF EXISTS(SELECT name FROM sys.databases WHERE name = "MyDB") DROP DATABASE MyDB
GO
CREATE DATABASE MYDB
GO
USE MyDB
GO
-- Monitor database for all DML actions by all users to the event log CREATE DATABASE AUDIT SPECIFICATION CaptureDbActionsToEventLog
FOR SERVER AUDIT MyEventLogAudit
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SELECT, INSERT, UPDATE, DELETE
ON SCHEMA::dbo
BY public)
WITH (STATE-ON)

The FOR SERVER AUDIT clause directs the monitored events to the server object MyEventLogAudit, established earlier to record audits in the application event log. The first ADD clause defines the DATABASE OBJECT CHANGE GROUP, monitoring database objects for DDL changes. This effectively audits CREATE, ALTER, or DROP statements targeting any database object (e.g., table, view). The second ADD clause audits DML actions (SELECT, INSERT, UPDATE, DELETE) executed by any public user on objects in the dbo schema (applying to all users).Database audit specifications offer precise control. The ON clause in the previous statement audits all objects in the dbo schema, but it can be tailored to audit DML operations on specific tables if needed. Similarly, instead of auditing all users using the public role in the BY clause, individual users and roles can be specified to audit DML actions solely by those users. To delve deeper, consider pursuing Online SQL certifications.

Ans. The Event Viewer is employed to review audits recorded in the event log (accessible through Administrative Tools in Control Panel). Demonstrates the event properties of an audit from a database audit, capturing a DELETE statement against the TestTable table using the Event Viewer.

Using Event Viewer to display a recorded audit in the Application event log.

Audits stored in the file system are not simple plain text files for viewing in Notepad; they're binary files. Viewing can be done through SQL Server Management Studio. Right-click the desired audit object in Object Explorer under the Security node at the server instance level (not the database level), and select View Audit Logs. This opens the Log File Viewer window.

Displaying file system-recorded audits using the Log File Viewer in SSMS.

Each audit entry provides detailed information about the captured event: timestamp, server instance, action, object type, success or failure, permissions, principal name and ID (user performing the action), database and schema names, object name, executed statement, and more.

Ans. The table-valued function (TVF) sys.fn_get_audit_file can be used to query audit files. This function accepts parameters specifying one or more audit files using wildcard patterns. You can also provide an initial file and a starting offset location for reading audit records. While optional, these parameters must be specified using the keyword "default." The function reads binary data from the file(s) and formats audit entries into a table.

SELECT
event time, database_name, object_name, statement
FROM
sys.fn_get_audit_file('C:\Demo\Sql Audits\*.sqlaudit', default, default)

Querying audit files.

The query produces condensed results. This data can be filtered, sorted, or inserted elsewhere using WHERE, ORDER BY, and INSERT statements. Unlike event log audits, querying and manipulating file system-recorded audits are more versatile. For deeper insights, explore various Online SQL certifications.

Ans: SQL Server offers catalog views that provide information about running audits and audit specifications on any server instance. Lists these audit catalog views along with brief descriptions.

Audit Catalog Views

Ans: Partially Contained Databases and Their Significance Moving or restoring databases to other servers becomes problematic due to the reliance of database-specific users on server-based logins. SQL Server 2012 introduces "partially contained" databases to address these dependency issues and enhance database portability. "Partially contained" implies SQL Server allows but doesn't enforce containment. Special contained users can be created within such databases, having their passwords stored directly within the database. Unlike standard SQL Server authentication, these contained users are authenticated within the contained database. The connection string should include the Initial Catalog keyword to specify the contained database name. Online SQL certifications can provide further insights.

• Creating a Partially Contained Database Creating a partially contained database involves enabling contained database authentication using sp_configure and using the CONTAINMENT=PARTIAL clause in the CREATE DATABASE statement.
• Creating a Contained User Contained users are created using the CREATE USER statement with the WITH PASSWORD option within contained databases.
• Uncontained Entities Objects like linked servers, SQL CLR, service broker objects, etc., that exist outside the database are considered uncontained entities. They pose portability challenges when databases are moved due to instance-level dependencies.
• Discovering Threats to Database Portability The sys.dm_db_uncontained_entities DMV identifies potential threats to database portability. It reports uncontained entity references within objects like stored procedures, views, functions, and triggers, assisting in identifying dependencies that might cause portability issues.
• Achieving Collation Independence from tempdb Databases on the same instance share tempdb, which can lead to collation conflicts. Partially contained databases can achieve collation independence by adjusting the collation of the temporary table in tempdb to match the contained database's collation.

Ans. Security Considerations for Connecting SQL Server to the Internet Directly exposing any OS or application to the Internet without a firewall is risky. Such exposure makes systems vulnerable to attacks. Microsoft invests in default security measures, but misconfigurations can still occur. Protect systems using firewalls and isolation techniques to mitigate risks.

• SQL Server Browser Service and Enumeration SQL Server instances' names and port numbers are returned using UDP port 1434. SQL Server Browser service, introduced in SQL Server 2005, handles this enumeration. It can be turned on/off without affecting SQL Server. To enhance security, block UDP port 1434 on your firewall if using the Browser service.
• Understanding SQL Injection SQL injection occurs when malicious input is substituted for valid SQL statements. This can compromise data and system integrity. Parameterizing SQL statements can prevent this vulnerability.
• Hackers and Search Engines Hackers exploit search engines by retrieving error information from web servers. Error messages can reveal system vulnerabilities. Never hard-code passwords in script files, avoid accepting unchecked user input, and protect against SQL injection.

Understanding and addressing SQL Server vulnerabilities and hacker tactics is paramount in maintaining the security and integrity of your database system. By following best practices, configuring your environment securely, and staying informed about potential risks, you can effectively defend your SQL Server against malicious attacks.Remember to regularly update your system, implement strong authentication and access controls, and conduct thorough security audits.