How to Manage and Save Running Config on Cisco Devices

Cisco IOS

The Cisco device stack uses the Internetwork operating system (IOS), which controls the device’s performance and behavior. The Cisco IOS defines an interface called the Command Line Interface (CLI), which enables administrators to enter commands into a terminal emulation program. The CLI can be accessed through three methods: the console, Telnet and Secure Shell (SSH).

Cisco Modes

Users can be logged in to a Cisco device using the following modes:

  • Exec mode (user mode) — Allows the user to look around but not change anything. Accessing the CLI by any of the three methods logs the user into Exec
  • Enable mode (privileged mode or privileged exec mode) — Allows the user to execute privileged commands, such as the reload command, which tells the switch to reboot the Cisco IOS. To enter this mode, the user runs the enable command mode.
  • Global configuration mode — Allows users to enter nondisruptive commands and display some information. Unlike exec and enable mode, configuration mode accepts configuration commands — commands that tell the switch the details of what to do and how to do it. Commands entered in configuration mode update the active configuration file, but the actual changes in configuration take place only after the device reboots. To enter configuration mode, a user executes the configure terminal (conf t) command.

Configuration mode contains several sub-modes. One is interface configuration mode, which can be entered by running the interface FastEthernet 0/1 (int fa0/1) configuration command.

Basic CLI Commands

Show

The show command is one of the most helpful commands because you can find the status of almost every feature of the Cisco IOS. It reads the current configuration from the Cisco device’s RAM and lists the requested settings in the CLI. For example, the show version command displays information about the Cisco IOS version currently loaded on a device.

Debug

Like the show command, debug reveals information about the device’s settings. However, instead of just listing the current status, the debug command asks the device to continue monitoring different processes in it and send messages to the user when different events occur, showing the status of settings over time. As a result, the debug command takes more CPU cycles, but it lets you monitor what is happening in a switch in real time. In short, show is for reporting and debug is for monitoring.

Hostname

The hostname command assigns a network name to the Cisco device.

?

Use the ? command to get answers to your questions about other commands, such as their syntax and description.

Where Configuration Files are Stored

A Cisco device needs to use the configuration file to do its work. Cisco devices have random-access memory (RAM) to store data from the configuration file while Cisco IOS is using it, but the RAM loses its contents when the device loses power. In order to load all configuration data back after the device loses power, Cisco use several types of more permanent memory. The following list explains the four main types of memory found in Cisco switches or Cisco routers, as well as the most common use of each type:

  • RAM — RAM is used by a Cisco device for working storage. The running configuration file is stored
  • ROM — Read-only memory (ROM) stores a bootstrap program that is loaded when the switch first powers on. This program finds the full Cisco IOS image and loads it into RAM.
  • Flash memory — This memory can be either inside the device or on a removable memory card. Flash memory stores fully functional Cisco IOS images and is the default location where the switch gets its Cisco IOS at boot time. Flash memory also can be used to store other files, including backup copies of configuration files.
  • NVRAM — Nonvolatile RAM (NVRAM) stores the initial or startup configuration file that is used when the Cisco device is powered on or reloaded.

Copying, Erasing and Saving Running Config on Cisco Devices

To change the configuration of a Cisco device, you need to enter configure terminal mode and then use one or more of the following commands.

Rename a device

Use the command hostname newname to change the name of the device to the string you specify.

Save running config on Cisco device

Use the command copy running-config startup-config (copy run start) to overwrite the current startup config file with what is currently in the running configuration file.

Copy files

The copy command can be used to copy files on a Cisco device, such as a configuration file or a new version of the Cisco IOS. Files can be copied between RAM, NVRAM and a TFTP server. The syntax for the copy commands is as follows:

copy {tftp | running-config | startup-config} {tftp | running-config | startup-config}

The first set of parameters in braces is the “from” location; the next set is the “to” location. When a file is copied into NVRAM or a TFTP server, the copy command always overwrites the existing destination file with the new file. However, when the copy command copies a configuration file into the running config file in RAM, the configuration file in RAM is not replaced; it is merged instead.

Erase the contents of NVRAM

You can use three different commands to erase NVRAM: write erase, erase startup-config and erase nvram. All of them erase the contents of the NVRAM configuration file, so if the device is then reloaded, there is no initial configuration and you have to begin initial device configuration.

Note that Cisco IOS does not have a command that erases the contents of the running configuration file. To clear out the running config file, simply erase the startup config file and then reload the device.

Securing Login to Cisco Devices

Cisco devices authenticate users as they log in, but the default configuration uses only simple password security and the enable password command defines the password for the current login. You can help protect enable mode by using the enable secret command instead. The older enable password command stores the password as clear text in the running configuration, and the only way to encrypt it is to use the weak service password-encryption command. The newer enable secret command automatically encodes the password using a Message Digest 5 (MD5) hash.

Initial Configuration of Cisco Devices

Cisco switches leave the factory with the following default settings:

  • All interfaces are enabled.
  • Auto-negotiation is enabled for ports that can use it (duplex auto and speed auto).
  • All interfaces are a part of VLAN 1.

All you have to do with a new Cisco switch is make all the physical connections — Ethernet cables and a power cord — and it starts working.

To configure the switch:

  1. Enter VLAN 1 configuration mode using the interface vlan 1 global configuration command.
  2. Assign an IP address and mask using the ip address ip-address mask
  3. Enable the VLAN 1 interface using the no shutdown
  4. Add the default gateway with ip default-gateway
  5. Add the DNS server using the ip name-server command to resolve names into IP addresses.

After the initial configuration, you can look at the IP address and mask information using the show interface vlan x command, which shows detailed status information about the VLAN interface. If you use DHCP, use the show dhcp lease command to see the leased IP address.

You can see some of the details of the interface configuration using the show running-config command or the handy show interfaces status command, which lists each interface on a single line that shows the first part of the interface description and the speed and duplex settings.

The show port-security interface command lists the configuration settings for port security on an interface, along with several important facts about the current operation of port security, including information about any security violations. The switch can be configured to take one of three actions when a violation occurs using the following command:  switchport port-security violation {protect | restrict | shutdown}. All three options cause the switch to discard the offending frame, but some of the options make the switch take additional actions, such as sending syslog messages to the console, sending SNMP trap messages to the network management station, or disabling the interface.

Conclusion

As you can see, it is very easy to save the running config, copy it to a tftp server and perform the initial configuration for a Cisco device. Before changing the running config, be sure to make a backup.

IT Operations – Netwrix Blog

Group Policy Management

Group Policy is an Active Directory management technology for Windows that provides centralized management of configuration settings. While it isn’t the only available management solution — PowerShell Desired State Configuration (DSC) and Mobile Device Management (MDM) can also be used — Group Policy is the recommended technology for domain-joined client devices because it provides more granular control than other solutions.

Group Policy Management Console

Group Policy settings are configured in Group Policy objects (GPOs). You can link GPOs to domains, sites and organizational units (OUs). For even more control, GPOs can be applied according to the results of Windows Management Instrumentation (WMI) filters, although WMI filters should be used sparingly because they can significantly increase policy processing time.

The Group Policy Management Console (GPMC) is a built-in Windows administration tool that enables administrators to manage Group Policy in an Active Directory forest and obtain data for troubleshooting Group Policy. You can find the Group Policy Management Console in the Tools menu of Microsoft Windows Server Manager. It is not a best practice to use domain controllers for everyday management tasks, so you should install the Remote Server Administration Tools (RSAT) for your version of Windows.

Installing the Group Policy Management Console

If you are using Windows 10 version 1809 or later, you can install GPMC using the Settings app:

  1. Open the Settings app by pressing WIN+I.
  2. Click Apps under Windows Settings.
  3. Click Manage optional features.
  4. Click + Add a feature.
  5. Click RSAT: Group Policy Management Tools and then click Install.

Group Policy Management Installing the Group Policy Management Console using the Setting app interface

Figure 1. Installing the Group Policy Management Console using the Setting app interface

If you are using an older version of Windows, you’ll need to download the right version of RSAT from Microsoft’s website.

For convenience, you might want to also install Server Manager. But if you choose not to, you can add GPMC to a Microsoft Management Console (MMC) and save the console.

Using the Group Policy Management Console

Every AD domain has two default GPOs:

  • Default Domain Policy, which is linked to the domain
  • Default Domain Controllers Policy, which is linked to the domain controller’s OU

You can see all the GPOs in a domain by clicking the Group Policy Objects container in the left pane of GPMC.

Group Policy Management Interface of the Group Policy Management Console

Figure 2. Interface of the Group Policy Management Console

Create a New Group Policy Object

Don’t change either the Default Domain Controllers Policy or the Default Domain Policy. The best way to add your own settings is to create a new GPO. There are two ways to create a new GPO:

  • Right-click the domain, site or OU to which you want to link the new GPO and select Create a GPO in this domain, and Link it here… When you save the new GPO, it will be linked and enabled immediately.
  • Right-click the Group Policy Objects container and select New from the menu. You will need to manually link the new GPO by right-click a domain, site or OU and selecting Link an Existing GPO. You can do this at any time.

Regardless of how you create a new GPO, in the New GPO dialog you must give the GPO a name, and you can choose to base it on an existing GPO. See the next section for information about the other options.

Edit a Group Policy Object

To edit a GPO, right click it in GPMC and select Edit from the menu. The Active Directory Group Policy Management Editor will open in a separate window.

Group Policy Management Interface of the Group Policy Management Editor

Figure 3. Interface of the Group Policy Management Editor

GPOs are divided into computer and user settings. Computer settings are applied when Windows starts, and user settings are applied when a user logs in. Group Policy background processing applies settings periodically if a change is detected in a GPO.

Policies vs Preferences

User and computer settings are further divided into Policies and Preferences:

  • Policies do not tattoo the registry — when a setting in a GPO is changed or the GPO falls out of scope, the policy setting is removed and the original value is used instead. Policy settings always supersede an application’s configuration settings and will be greyed out so that users cannot modify them.
  • Preferences tattoo the registry by default, but this behavior is configurable for each preference setting. Preferences overwrite an application’s configuration settings but always allow users to change the configuration items. Many of the configurable items in Group Policy Preferences are those that might have been previously configured using a login script, such as drive mappings and printer configuration.

You can expand Policies or Preferences to configure their settings. These settings will then be applied to computer and user objects that fall into the GPO’s scope. For example, if you link your new GPO to the domain controller’s OU, the settings will be applied to computer and user objects located in that OU and any child OUs. You can use the Block Inheritance setting on a site, domain or OU to stop GPOs that are linked to parent objects from being applied to child objects. You can also set the Enforced flag on individual GPOs, which overrides the Block Inheritance setting and any configuration items in GPOs that have higher precedence.

GPO Precedence

Multiple GPOs can be linked to domains, sites and OUs. When you click on one of these objects in GPMC, a list of linked GPOs will appear on the right on the Linked Group Policy Objects tab. If there is more than one linked GPO, GPOs with a higher link order number take priority over settings configured in GPOs with a lower number.

You can change the link order number by clicking on a GPO and using the arrows on the left to move it up or down. The Group Policy Inheritance tab will show all applied GPOs, including those inherited from parent objects.

Group Policy Management Information about all applied GPOs in GPMC

Figure 4. Information about all applied GPOs in GPMC

Advanced Group Policy Management

Advanced Group Policy Management (AGPM) is available as part of the Microsoft Desktop Optimization Pack (MDOP) for Software Assurance customers. Unlike GPMC, AGPM is a client/server application where the server component stores GPOs offline, including a history for each GPO. GPOs managed by AGPM are called controlled GPOs because they are managed by the AGPM service and administrators can check them in and out, much like you might check files or code in and out of GitHub or a document management system.

AGPM provides greater control over GPOs than is possible with GPMC. In addition to providing version control, it enables you to assign roles like Reviewer, Editor and Approver to Group Policy administrators, which helps you implement strict change control throughout the entire GPO lifecycle. AGPM auditing also gives greater insight into Group Policy changes.

IT Operations – Netwrix Blog

Top 5 Management Tools for Group Policy Administration

Group Policy is a configuration management technology that is part of Windows Server Active Directory (AD). This article explores Microsoft’s own Group Policy tools and some of the best third-party Group Policy management tools.

Group Policy Management Console

The Group Policy Management Console (GPMC) is Microsoft’s out-of-the-box Group Policy management software in Windows Server. While there are other Microsoft and third-party management tools, you can’t do without GPMC. It is also included in the Remote Server Administration Tools (RSAT) for Windows client operating systems, so it can be used without logging in to a domain controller, and it includes a PowerShell module that enables you to automate many aspects of Group Policy management.

GPMC lets you create and edit Group Policy objects (GPOs) and link them to AD sites, domains and organizational units (OUs). The Group Policy Object Editor is a separate tool that opens from GPMC; it enables you to edit and import GPO settings and back up and restore GPOs. More advanced features of GPMC allow you to apply Windows Management Instrumentation (WMI) filters to GPOs, block inheritance and enforce GPO links.

System administrators can use GPMC to view which settings have been configured in GPOs without opening the Group Policy Object Editor. In more complex situations where multiple GPOs are applied to AD objects, the Resultant Set of Policy (RSoP) shows which GPOs and settings will apply in practice to users, computers or both. RSoP can be run in Logging or Planning mode: GPMC’s Group Policy Modeling feature is RSoP in Planning mode; Group Policy Results is RSoP in Logging mode, and it generates reports that you can save in HTML format.

Tools from SDM Software

SDM Software makes several tools for Group Policy management, including the following:

  • GPO Migrator is ideal for organizations that need to clean up or consolidate GPOs. It allows you to pick which settings you want to migrate to other Group Policy objects and even migrate settings for use with PowerShell Desired State Configuration (DSC).
  • GPO Policy Reporting Pak is an advanced reporting and analysis tool that lets you quickly search settings, analyze GPOs differences and duplicate or conflicting settings. It can also export GPOs across different Active Directory domains and generate reports in Excel or PDF format. Reporting Pak has a PowerShell module, so you can automate everything from the command line.
  • Group Policy Auditing and Attestation (GPAA) tracks changes in real time and can roll back unwanted changes. Alerts provide before and after values so you can understand what changes were made, including the who, what, when and where information. GPAA automatically backs up GPOs that are changed so that you can easily roll back to the previous state. Role-based management lets organizations delegate which users can manage Group Policy. You can assign owners to GPOs and require them to attest to their GPOs as part of a workflow.
  • Group Policy Compliance Manager checks that the settings you configured in GPOs are successfully applied to objects that fall in to scope of management. The product supports agent or agent-less collection and can centralize reports in a SQL database for multiuser access. There’s also a powerful search feature included for searching GPOs and individual settings, and a PowerShell module for automation.

Netwrix Auditor for Active Directory

Netwrix Auditor for Active Directory is a comprehensive auditing product for Windows Server Active Directory. It provides security intelligence so you can better understand what is happening in AD and Group Policy. Netwrix Auditor provides change and configuration auditing for AD and Group Policy, including who, what, when, and where information and the before and after values for each change. The Enterprise Overview dashboard provides a graphical representation of events over a configurable time period and enables you to drill down to get more specific information and generate reports.

Comprehensive GPO state-in-time reports enable you to document and review current and past Group Policy object settings. Using a series of built-in reports, you can drill down to get detailed information about GPOs. For example, you could run a report to find all current GPO settings that affect password policy in the domain and compare the results with a past point in time to see if any changes were made. Another report shows whether there are duplicate settings in GPOs. Tracking down redundant settings can help improve logon efficiency and simplify operations. All reports offer filters that let you narrow down the results so you can find exactly the information you need.

Netwrix Auditor can help you prove that your organization is compliant with security regulations like GDPR, PCI DSS and HIPAA. It also also integrates with other security systems and can send alerts on AD and GPO changes. The primary advantage Netwrix Auditor has over other policy management tools is that it doesn’t just help you manage Group Policy; it provides complete auditing for Active Directory. Group Policy relies on Active Directory for its security, so it is important to make sure that AD is secure and compliant; otherwise, Group Policy controls could be circumvented by a malicious actor.

Security Compliance Toolkit

Microsoft’s free Security Compliance Toolkit (SCT) contains baseline security templates for all supported versions of Windows and Windows Server that can be used to create Group Policy objects or configure local policy. SCT is updated regularly and includes comprehensive documentation of recommended Group Policy settings, along with spreadsheets that show you the differences between settings in the current and previous releases so that you can quickly understand what has changed.

SCT includes reports that help you navigate the settings in a more user-friendly way. GPOs are provided as backup objects you import using the Group Policy Management Console. You can choose which GPOs to apply according to the role of a device. For example, MSFT Windows Server 2019 – Domain Controller applies to domain controllers and MSFT Windows Server 2019 – Member Server applies to domain-joined servers.

SCT includes two useful tools. Policy Analyzer compares sets or versions of GPOs; it can compare GPOs against current local policy and registry settings and export the results to a spreadsheet. Local Group Policy Object (LGPO) is a command-line tool for automating the management of local policy on systems that aren’t joined to an Active Directory domain.

Advanced Group Policy Management

Advanced Group Policy Management (AGPM) is part of the Microsoft Desktop Optimization Pack (MDOP), which is available to Software Assurance customers only. It extends the features of the Group Policy Management Console with change control and better GPO management capabilities. AGPM 4.0 SP3 supports Windows 10 and it is based on a client/server architecture. The AGPM Service manages an archive, which is a central store of controlled GPOs and their history. Users connect to the AGPM Service using a Microsoft Management Console (MMC) snap-in.

AGPM users can check controlled GPOs in and out, much like you might check documents in and out of a document management system. Administrators can control who has permissions to check GPOs in and out of the archive, providing a robust change control solution for Active Directory Group Policy. To prevent users from circumventing AGPM, organizations must follow security best practices to ensure that IT staff cannot use their privileges to modify Group Policy objects in the domain without using AGPM.

IT Operations – Netwrix Blog

6 Group Policy Settings You Need to Get Right

Group Policy is a configuration management technology that is part of Windows Server Active Directory. It can be used to configure settings in Windows client and server operating systems to make sure you have a consistent and secure setup across devices. There are literally hundreds of settings available to use as you configure your Group Policy objects (GPOs), but in this blog post, I’ll show you six critical Group Policy security settings that you need to get right to ensure basic security in your environment.

If you want to configure Group Policy to Microsoft’s recommended settings, download the Security Compliance Toolkit. It contains security baselines for all supported versions of Windows, which you can use as the basis for your own Group Policy objects, and spreadsheets that list and explain all the recommended settings. If you have devices that are not members of a domain, use local policy to configure settings. The toolkit contains a specific application that makes it easier to manage local policy settings on standalone devices.

Application Control (AppLocker)

Failure to keep unauthorized software off your machines is one of the key ways malware takes hold of systems. While it is important to remove local administrator privileges from end users to prevent system-wide changes, that restriction alone is not enough to prevent users (or processes running in the context of logged-in user accounts) from running code that could do serious damage.

To address this, Microsoft Windows 7 introduced AppLocker, which enables system administrators to quickly apply application control policies to systems. AppLocker works by establishing a whitelist of processes, scripts and installers that can run. You’ll find AppLocker settings in Group Policy under Computer Configuration > Windows Settings > Security Settings > Application Control Policies > AppLocker.

Finding AppLocker Settings in Group Policy

Figure 1. Where to find AppLocker settings in Group Policy

To create rules for each category listed under AppLocker, right-click the category (for example, Executable rules) and select one of the three options in the top half of the menu. Selecting Automatically Generate Rules… scans a reference system and creates rules based on the executables installed in trusted locations. If you decide to create rules manually, make sure that you Create Default Rules; otherwise you risk disabling critical functionality in Windows that could render systems unusable.

Once you’ve set up some rules, right-click AppLocker and select Properties from the menu. On the Enforcement tab, click the rule categories you want to enable and select Audit only from the menu. Let your rules run in audit mode for some time and check the Windows Event log for any issues. If you are sure the rules don’t block any important apps or Windows features, change the setting to Enforce rules.

The Application Identity service must be running on devices before AppLocker will enforce policies. In Windows 10, AppLocker can also be configured through the Local Group Policy editor. However, with Windows 10, Microsoft introduced Windows Defender Application Control (previously Device Guard), which is a more robust application control technology that is difficult for local administrators to circumvent.

Windows Update

Windows Update is a critical component of Windows that makes sure the operating system and other software stays up to date. If your organization is using Windows 10, think about using Windows Update for Business (WUfB) to keep devices patched. Unlike Windows Server Update Services (WSUS), WUfB doesn’t require any on-premise infrastructure but does give you some control over how Windows 10 feature and quality updates are applied. You can find Windows Update and Windows Update for

Business Group Policy settings under Computer Configuration > Administrative Templates > Windows Components > Windows Update.

If you are using an earlier version of Windows, use Group Policy to point devices at an internal WSUS or System Center Configuration Manager Software Update Point (SUP) using the Configure Automatic Updates and Specify intranet Microsoft update service location settings. There are lots of other settings too, like Do not include drivers with Windows Updates and Specify active hours range for auto-restarts, that might be useful.

Disable SMBv1 Client and Server

Some components of SMBv1 lack proper security. If you remember back to 2017, flaws in SMBv1 were one of the ways that the NotPetya virus was able to spread so quickly. Although Microsoft had already issued patches for SMBv1, many organizations had not applied them.

Later versions of Windows 10 already have the insecure SMBv1 components removed by default. But if you are using Windows 7, Windows 8 or an earlier version of Windows 10, you will need to make sure to either remove the SMBv1 component from your system or use Group Policy to disable it on all servers and clients that don’t need it.

To disable the SMBv1 server, create a REG_DWORD value called SMB1 under the following key path and set its value to 0:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters

To disable the SMBv1 client, create two registry values. The first is a REG_DWORD value called Start, which should be set to 4 under the following key path:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\mrxsmb10

The second is REG_MULTI_SZ value called DependOnService, which should be set to “Bowser”,”MRxSmb20?,”NSI” under the following path:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstation

To configure new registry items in Group Policy, go to Computer Configuration > Preferences > Windows Settings and right-click Registry. Select New > Registry Item from the menu and then add the required key path and value. Make sure that the Action field is set to Update.

Disable Guest Account and Local Administrator Accounts

The built-in guest and local administrator accounts are disabled by default in Windows 10. But if you want to make sure it stays that way, set the accounts in Group Policy to be always disabled. This is especially important to ensure strong access control on critical servers, such as domain controllers. You can find the settings under Computer Configuration > Windows Settings > Security Settings > Local Policies > Security Options. Set both Accounts: Guest account status and Accounts: Administrator account status to Disabled.

Deny Execute Access on Removable Disks

You can allow users to read and write to and from removable media but block them from running any executables. If you have AppLocker set up, this setting might be moot, but many organizations don’t use application control. In any case, blocking executables on removable media can help protect systems from malicious code.

You’ll find the Removable Disks: Deny execute access setting under Computer Configuration > Administrative Templates > System > Removable Storage Access.

Prevent Changes to Proxy Settings

Regardless of whether your organization uses a proxy server, it’s wise to prevent users from changing proxy settings. At worst, malicious proxy settings could divert all internet traffic in your network through an unauthorized middleman; at best, they could stop users from accessing internet resources. To stop users from changing proxy settings for Internet Explorer and Microsoft Edge, set Prevent changing proxy settings under User Configuration> Administrative Templates > Windows Components > Internet Explorer to Enabled.

Conclusion

Those are the six Group Policy settings you need to be certain to configure properly. Remember, before you apply Group Policy settings in your production environment, test them to be sure that they won’t have any adverse effects. For a full list of Microsoft’s recommended settings, download the baseline templates in the Security Compliance Toolkit.

IT Operations – Netwrix Blog

How to Enable SQL Server Audit and Review the Audit Log

Auditing Microsoft SQL Server is critical to identifying security issues and breaches. In addition, auditing SQL Server is a requirement for compliance with regulations like PCI DSS and HIPAA.

The first step is to define what to audit. For example, you might audit user logins, server configuration, schema changes and audit data modifications. Next, you have choose which security auditing features to use. Useful features include the following:

  • C2 Auditing
  • Common Compliance Criteria
  • Login Auditing
  • SQL Server Auditing
  • SQL Trace
  • Extended Events
  • Change Data Capture
  • DML, DDL, and Logon Triggers

This article is for database administrators (DBAs) who are looking at using C2 auditing, Common Compliance Criteria and SQL Server Auditing. We will not be looking at any third-party auditing tools, though they can be of great help, especially for larger environments and in regulated industries.

Enabling C2 Auditing and Common Criteria Compliance

If you aren’t currently auditing your SQL Server, the easiest place to start is by enabling C2 auditing. C2 auditing is an internationally accepted standard that can be turned on in SQL Server. It audits events like user logins, stored procedures, and the creation and removal of objects. But it is all or nothing — you can’t choose what it audits, and it can generate a lot of data. Furthermore, C2 auditing is in maintenance mode, so it will likely be removed in a future version of SQL Server.

Common Criteria Compliance is a newer standard that supersedes C2 auditing. It was developed by the European Union and can be enabled in Enterprise and Datacenter editions of SQL Server 2008 R2 and later. But it can cause performance issues if your server isn’t sufficiently spec’d to cope with the extra overhead.

Here’s how to enable C2 auditing in SQL Server 2017:

1. Open the SQL Server Management Studio.

2. Connect to the database engine for which you want to enable C2 auditing. In the Connect to Server dialog, make sure that Server type is set to Database Engine and then click Connect.

3. In the Object Explorer panel on the left, right-click your SQL Server instance at the top and select Properties from the menu.

4. In the Server Properties window, click Security under Select a page.

5. On the Security page, you can configure login monitoring. By default, only failed logins are recorded. Alternatively, you can audit just successful logins, or both failed and successful logins.

SQL Server Audit Configuring Access Auditing

Figure 1. Configuring access auditing

6. Check Enable C2 audit tracing under Options.

7. If you want to enable C2 Common Criteria Compliance auditing, check Enable Common Criteria compliance.

Common Criteria (CC) Compliance is a flexible standard that can be implemented with different Evaluation Assurance Levels (EALs), from 1 to 7. Higher EALs have a more demanding verification process. When you check Enable Common Criteria compliance in SQL Server, you are enabling CC Compliance EAL1. It is possible to configure SQL Server manually for EAL4+.

Enabling CC Compliance changes SQL Server behavior. For example, table-level DENY permissions will take precedence over column-level GRANTs, and both successful and failed logins will be audited. In addition, Residual Information Protection (RIP) is enabled, which over-writes memory allocations with a pattern of bits before they are used by a new resource.

8. Click OK.

9. Based on the selected options, you might be prompted to restart SQL Server. If you get this message, click OK in the warning dialog. If you enabled C2 Common Criteria Compliance, reboot the server. Otherwise, right-click your SQL Server instance in Object Explorer again and select Restart from the menu. In the warning dialog, click Yes to confirm that you want to restart SQL Server.

Enabling SQL Server Audit

SQL Server auditing can be enabled instead of C2 auditing; you can also choose to enable both. SQL Server Audit objects can be configured to collect events at the server level or the SQL Server database level.

Create Server Audit Object

Let’s create a server-level SQL Server audit object:

1. In the Object Explorer panel on the left, expand Security.

2. Right-click Audits and select New Audit… from the menu. This will create a new SQL Server Audit object for server-level auditing.

3. In the Create Audit window, give the audit settings a name in the Audit name

4. Specify what should happen if SQL Server auditing fails using the On Audit Log Failure You can choose Continue or choose to shut down the server or stop database operations that are audited. If you select Fail operation, database operations that are not audited will continue to work.

SQL Server Audit Creating a server-level SQL Server audit object

Figure 2. Creating a server-level SQL Server audit object

5. In the Audit destination dropdown menu, you can choose to write the SQL audit trail to a file or to audit events in the Windows Security log or Application event log. If you choose a file, you must specify a path for the file.

Note that if you want to write to the Windows Security event log, SQL Server will need to be given permission. For the sake of simplicity, select the Application event log. Additionally, you can include a filter as part of the audit object to provide a narrow set of results; filters must be written in Transact-SQL (T-SQL).

6. Click OK.

7. You will now find the new audit configuration in Object Explorer below Audits. Right-click the new audit configuration and select Enable Audit from the menu.

8. Click Close in the Enable Audit dialog.

Create Database Audit Object

To create a SQL Server audit object for database-level auditing, the process is a little different and you need to create at least one server-level audit object first.

1. Expand Databases in Object Explorer and expand the database on which you want to configure auditing.

2. Expand the Security folder, right click Database Audit Specifications and select New Database Audit Specification… from the menu.

SQL Server Audit Creating a server audit specification for database-level auditing

Figure 3. Creating a server audit specification for database-level auditing

3. In the Properties window under Actions, use the dropdown menus to configure one or more audit action types, selecting the statements you want to audit (such as DELETE or INSERT), the object class on which the action is performed, and so on.

4. When you’re done, click OK and then enable the audit object by right-clicking it and selecting Enable Database Audit Specification.

Viewing SQL Server Audit Logs

C2 Audit SQL Server audit logs are stored in the default data directory of the SQL Server instance. Each log file can be a maximum of 200 megabytes. A new file is automatically created when the limit is reached.

A native solution that is recommended to view SQL Server audit logs called Log File Viewer. To use it, take the following steps:

1. In SQL Server Management Studio, in the Object Explorer panel, expand Security and

2. Right-click the audit object that you want to view and select View Audit Logs from the menu.

3. In the Log File Viewer, the logs will be displayed on the right side. Regardless of whether the logs are written to a file or to the Windows Event Log, Log File Viewer will display the logs.

4. At the top of Log File Viewer, you can click Filter to customize which log entries are displayed. SQL Server file logs are saved in .sqlaudit format and are not readable, so Log File Explorer allows you to click Export to save logs to a comma-delimited .log file format.

SQL Server Audit Reviewing SQL Server audit logging in the Log File Viewer

Figure 4. Reviewing SQL Server audit logging in the Log File Viewer

IT Operations – Netwrix Blog