Configuration LEPIDE SOFTWARE Guide for SQL Server This document explains the steps to configure LepideAuditor Suite to add and audit SQL Server. LepideAuditor Suite LepideAuditor Suite SQL Server Auditing Configuration Guide Copyright LepideAuditor Suite, LepideAuditor App, LepideAuditor App Server, LepideAuditor Suite (Web Console), LepideAuditor Logon/Logoff Audit Module, any and all components, any and all accompanying software, files, data and materials, this Configuration Guide, and other documentation are copyright of Lepide Software Private Limited, with all rights reserved under the copyright laws. This user guide cannot be reproduced in any form without the prior written permission of Lepide Software Private Limited. No Patent Liability is assumed, however, with respect to the use of the information contained herein. © Lepide Software Private Limited, All Rights Reserved. Warranty Disclaimers and Liability Limitation LepideAuditor Suite, LepideAuditor App, LepideAuditor App Server, LepideAuditor Suite (Web Console), LepideAuditor Logon/Logoff Audit Module, any and all components, any and all accompanying software, files, data and materials, are distributed and provided AS IS and with no warranties of any kind, whether expressed or implied. In particular, there is no warranty for any harm, destruction, impairment caused to the system where these are installed. You acknowledge that good data processing procedure dictates that any program, listed above, must be thoroughly tested with non-critical data before there is any reliance on it, and you hereby assume the entire risk of all use of the copies of LepideAuditor Suite and the above listed accompanying programs covered by this License. This disclaimer of warranty constitutes an essential part of this License. In addition, in no event does Lepide Software Private Limited authorize you or anyone else to use LepideAuditor Suite and the above listed accompanying programs in applications or systems where LepideAuditor Suite and the above listed accompanying programs’ failure to perform can reasonably be expected to result in a significant physical injury, or in loss of life. Any such use is entirely at your own risk, and you agree to hold Lepide Software Private Limited harmless from any and all claims or losses relating to such unauthorized use. Trademarks LepideAuditor Suite, LAS, LepideAuditor App, LepideAuditor App Server, LepideAuditor Suite (Web Console), LepideAuditor Logon/Logoff Audit Module, LepideAuditor for Active Directory, LAAD, LepideAuditor for Group Policy Object, LAGPO, LepideAuditor for Exchange Server, LAES, LepideAuditor for SQL Server, LASS, LepideAuditor SharePoint, LASP, Lepide Object Restore Wizard, Lepide Active Directory Cleaner, LADC, Lepide User Password Expiration Reminder, LUPER, and LiveFeed are registered trademarks of Lepide Software Pvt Ltd. © 2016 Lepide Software Pvt. Ltd. Page | 2 LepideAuditor Suite SQL Server Auditing Configuration Guide All other brand names, product names, logos, registered marks, service marks and trademarks (except above of Lepide Software Pvt. Ltd.) appearing in this document are the sole property of their respective owners. These are purely used for informational purposes only. We have compiled a list of such trademarks but it may be possible that few of them are not listed here. Windows XP®, Windows 7®, Windows 8®, Windows 2000 Server®, Windows 2000 Advanced Server®, Windows Server 2003®, Windows Server 2003 R2®, Windows Server 2008®, Windows Server 2008 R2®, Windows Server 2012®, Exchange Server 2003®, Exchange Server 2007®, Exchange Server 2010®, Exchange Server 2013®, SharePoint Server®, SharePoint Server 2010®, SharePoint Foundation 2010®, SharePoint Server 2013® SharePoint Foundation 2013®, SQL Server 2005®, SQL Server 2008®, SQL Server 2008 R2®, SQL Server 2012®, SQL Server 2014®, SQL Server 2005 Express Edition®, SQL Server 2008 Express® SQL Server 2008 R2 Express®, SQL Server 2012 Express®, SQL Server 2014 Express® .NET Framework 4.0, .NET Framework 2.0, Windows PowerShell® are registered trademarks of Microsoft Corporation. Intel and Pentium are registered trademarks of Intel Corporation. Contact Information Email: [email protected] Website: http://www.lepide.com © 2016 Lepide Software Pvt. Ltd. Page | 3 LepideAuditor Suite SQL Server Auditing Configuration Guide Table of Contents 1. Introduction ............................................................................................................................................ 5 2. Adding SQL Server ................................................................................................................................ 5 2.1 Add SQL Server with Express Configuration .............................................................................. 7 2.1.1 SQL Server Details .................................................................................................................... 8 2.1.2 Database Settings .................................................................................................................... 12 2.1.3 Install SQL Auditing Agent ................................................................................................... 14 2.2 Add SQL Server with Advanced Configuration ....................................................................... 16 2.2.1 SQL Server Details .................................................................................................................. 17 2.2.2 SQL Health Monitoring .......................................................................................................... 18 2.2.3 Audit Settings .......................................................................................................................... 20 2.2.4 Object Settings ......................................................................................................................... 22 2.2.5 User Settings ............................................................................................................................ 24 2.2.6 Database Settings .................................................................................................................... 25 2.2.7 Archive Database Settings ..................................................................................................... 26 2.2.8 Install SQL Auditing Agent ................................................................................................... 28 3. Remove SQL Server ............................................................................................................................. 31 4. Modify SQL Server Properties ........................................................................................................... 35 5. Manage SQL Auditing Agent ............................................................................................................. 42 5.1 Switching SQL Auditing Mode .................................................................................................... 42 5.2 Uninstall SQL Agent ...................................................................................................................... 44 5.3 Uninstall Agent from not added SQL Server ............................................................................. 48 5.4 Install SQL Agent ........................................................................................................................... 54 6. Manage SQL Health Monitoring ....................................................................................................... 60 6.1 Disable Health Monitoring ........................................................................................................... 60 6.2 Enable SQL Server Health Monitoring ....................................................................................... 63 7. Conclusion ............................................................................................................................................ 66 © 2016 Lepide Software Pvt. Ltd. Page | 4 LepideAuditor Suite SQL Server Auditing Configuration Guide 1. Introduction Welcome to the Configuration Guide of LepideAuditor Suite. This software is a comprehensive solution providing all round audit information and control regarding Active Directory, Group Policy Objects, Exchange Servers, SharePoint Servers, and SQL Servers. In this configuration guide, we have covered the steps to add and audit SQL Server. 2. Adding SQL Server LepideAuditor Suite allows you to add and audit multiple SQL Servers in the network from a central platform. You can use any of the following procedures to add a SQL Server. 1. Agent-based Auditing: An agent will be installed on SQL Server to collect the logs, which will pass it to LepideAuditor Suite for parsing and display. This agent will run continuously and consume the server resources as per requirement. 2. Agentless Auditing: No agent will be installed for auditing SQL Server and hence no system resources of SQL Server will be used. Go to "Settings" tab → "Component Management" and use any of the following methods to start with Click "SQL Server" button under "Add Component" section. Figure 1: SQL Server button You can also right click on "Component Management" node, go to "Add" and select "Active Directory, Group Policy and Exchange Server" to add a domain. Figure 2: Right click on “Component Management” Clicking it shows the following wizard. © 2016 Lepide Software Pvt. Ltd. Page | 5 LepideAuditor Suite SQL Server Auditing Configuration Guide Figure 3: Add SQL Server wizard Here, software offers two different ways to add SQL Server. 1. Express Configuration: Add SQL Server with minimum recommended settings. 2. Advanced Configuration: Add SQL Server with the advanced settings to customize the auditing. © 2016 Lepide Software Pvt. Ltd. Page | 6 LepideAuditor Suite SQL Server Auditing Configuration Guide 2.1 Add SQL Server with Express Configuration Perform the below steps at the “Add SQL Server” wizard, 1. Select "Express Configuration" at the following wizard. Figure 4: Adding SQL Server with Express Configuration © 2016 Lepide Software Pvt. Ltd. Page | 7 LepideAuditor Suite SQL Server Auditing Configuration Guide 2.1.1 SQL Server Details 2. Click "Next". It asks you to provide the details of SQL Server to be added Figure 5: Asking for SQL Server details 3. The software lets you add a local or networked SQL Server. You can either enter the name of SQL Server manually in the text box. 4. Alternatively, you can click to enumerate all SQL Servers in a list. © 2016 Lepide Software Pvt. Ltd. Page | 8 LepideAuditor Suite SQL Server Auditing Configuration Guide NOTE: User should be assigned the role of sysadmin in SQL Server. If you are using a local system administrator or domain administrator to run LepideAuditor Suite Service, then its login with Windows Authentication and sysAdmin role should exist in SQL Server. If the user login does not exist already, then follow the steps below to create it. a. Open “SQL Server Management Studio”. b. Select SQL or Windows Authentication. c. Enter username and password of a SQL Server Administrator. d. Click “Connect”. e. In the left tree panel, go to “Security”  “Logins”. f. Right click on “Logins” and select “New Login”. g. “Login – New” wizard appears onscreen. h. Enter the same login name as that of local system administrator or domain administrator, with which you are running LepideAuditor Suite Server. i. Switch to “Server Roles” and select “sysAdmin”. j. Click “OK”. If the user exists but no such rights are assigned, then follow these steps to assign the required rights: a. Open “SQL Server Management Studio”. b. Select SQL or Windows Authentication. c. Enter username and password of a SQL Server Administrator. d. Click “Connect”. e. In the left tree panel, go to “Security”  “Logins”. f. Expand “Logins” and select the required user. g. Right click on the user and select “Properties”. h. Switch to “Server Roles” and select “sysAdmin”. i. Click “OK”. j. Go to Status page, select Grant and Enabled. k. Click “OK”. © 2016 Lepide Software Pvt. Ltd. Page | 9 LepideAuditor Suite SQL Server Auditing Configuration Guide Figure 6: Enumerating all SQL Servers in the network 5. Click icon to expand the listings for local and network servers. You can click icon to collapse the list. 6. Select a server and click "OK". It takes you back the "SQL Server Settings" box, which will now show the selected SQL Server. 7. You have to select either Windows Authentication or SQL Server Authentication. Selecting latter is recommended. 8. Enter the username and password of a SQL Server user. 9. Select any of the following radio buttons for "Auditing Method". a) With Agent: Click this button to install an agent for auditing SQL Server. b) Without Agent: Click this button to add the SQL Server without installing any agent. 10. Please view the following snapshot displaying the details of a sample server. © 2016 Lepide Software Pvt. Ltd. Page | 10