Configure DB MSSQLServer 200x Monitoring in Windows InfoSphere Guardium for Distributed Systems

IBM Software
Information Management
Configure DB MSSQLServer 200x Monitoring in
Windows
Technical Document
InfoSphere Guardium for Distributed Systems
InfoSphere Optim & Guardium Technology Ecosystem
[email protected]
IBM Software
Information Management
Table of Contents
1
Introduction ....................................................................................................................................................................................... 3
2
Tech Document Objectives .............................................................................................................................................................. 3
3
Configuration..................................................................................................................................................................................... 4
3.1
Review & Configure Windows OS user environment...................................................................................................................................... 4
3.1.1
3.1.2
3.2
Verify current user belongs to Administrators group ....................................................................................................................... 4
Verify DB MS SQL Server parameters ........................................................................................................................................... 4
Configure Guardium Collector and S-TAP Agent ........................................................................................................................................... 7
3.2.1
Configure Collector for DB MS SQL Server traffic .......................................................................................................................... 7
InfoSphere Optim Solutions for Distributed Systems Bootcamp
© Copyright IBM Corp. 2012. All rights reserved
Page 2 of 12
IBM Software
Information Management
1 Introduction
This technical document will help the Guardium Administrator to establish monitoring methods for TCP, Shared Memory and
Named Pipes protocols with Windows (local) and SQL (remote) connections to a MS SQL Server database in a Windows
operating system.
Attention: In this Tech Document we assume that OS user is an administrator and the MS SQL Server DBMS is already installed,
including the SQL Management Studio tools.
Attention: In this Tech Document we assume that the S-TAP agent is already installed.
2 Tech Document Objectives
This guide is divided in these main sections:
•
Review DB Instance required parameters and perform connectivity tests
•
Configure Collector to start required monitoring.
•
Perform final configurations on both ends to capture all combinations of DB traffic.
InfoSphere Optim Solutions for Distributed Systems Bootcamp
© Copyright IBM Corp. 2012. All rights reserved
Page 3 of 12
3 Configuration
3.1
Review & Configure Windows OS user environment
3.1.1
Verify current user belongs to Administrators group
Figure 1 - Windows Users and Groups
3.1.2
1.
Verify DB MS SQL Server parameters
Find the correct name of the SQL Server Instance:
Figure 2 - MS SQL Server Instance Name
1.
Validate that all the required connection protocols are enabled (Named Pipes and TCP/IP):
Figure 3 - MS SQL Server Instance Protocols
IBM Software
Information Management
2.
Verify the SQL service properties for each protocol:
Figure 4 - Shared Memory Instance Protocol
Figure 5 - Named Pipes Instance Protocol
Figure 6 - TCP/IP Instance Protocol
2.
Verify the SQL service TCP/IP ports configuration:
Figure 7 - TCP/IP Instance Protocol Properties
InfoSphere Optim Solutions for Distributed Systems Bootcamp
© Copyright IBM Corp. 2012. All rights reserved
Page 5 of 12
IBM Software
Information Management
3.
Verify the TCP/IP ports configuration in the Native Client configuration:
Figure 8 - TCP/IP Protocol of Native Client
4.
Verify that you can connect to the MS SQL Instance using the 2 types of authentication (Windows and SQL):
Figure 9 - SQL Instance Connection Authentication
InfoSphere Optim Solutions for Distributed Systems Bootcamp
© Copyright IBM Corp. 2012. All rights reserved
Page 6 of 12
IBM Software
Information Management
5.
Verify that using both methods of authentication, you can connect using the 3 types of protocols
(A total of 6 combinations should be performed):
Figure 10 - SQL Instance Connection Protocols
3.2
Configure Guardium Collector and S-TAP Agent
3.2.1
1.
Configure Collector for DB MS SQL Server traffic
As Admin user, verify that the STAP is active:
Figure 11 - S-TAP Status Monitor
2.
Create a SELECTIVE_AUDIT Policy in the Collector:
Figure 12 - Creation of Policy (Selective Audit Type)
InfoSphere Optim Solutions for Distributed Systems Bootcamp
© Copyright IBM Corp. 2012. All rights reserved
Page 7 of 12
IBM Software
Information Management
3.
Add one rule to the new policy to capture traffic:
Figure 13 - Creation of Rule (Log Full Details)
4.
Configure the Inspection Engines Core, verify selected values:
Figure 14 - Inspection Engines Core
InfoSphere Optim Solutions for Distributed Systems Bootcamp
© Copyright IBM Corp. 2012. All rights reserved
Page 8 of 12
IBM Software
Information Management
5.
Configure the S-TAP Host, verify the Shared Memory/Named Pipes parameters and also configure the MS SQL
Inspection Engine values:
Figure 15 - S-TAP Host Control for Windows and MS SQL Server Inspection Engine
InfoSphere Optim Solutions for Distributed Systems Bootcamp
© Copyright IBM Corp. 2012. All rights reserved
Page 9 of 12
IBM Software
Information Management
6.
Create a Query and Trace Report to catch and identify TCP, Shared Memory and Named Pipes DB MS SQL Server
traffic:
Figure 16 - Full SQL Trace Query/Report
7.
Install the new Policy (Install & Override):
Figure 17 - Policy Installation
8.
Restart the Inspection Engine Core and click Apply:
Figure 18 - Inspection Engines Core
9.
Restart the S-TAP Service remotely (from the Collector):
Figure 19 - S-TAP Control restart
InfoSphere Optim Solutions for Distributed Systems Bootcamp
© Copyright IBM Corp. 2012. All rights reserved
Page 10 of 12
IBM Software
Information Management
10. In the Database server, restart the MS SQL Service, so S-TAP Agent can start monitoring the DB:
Figure 20 - SQL Instance restart
Attention: A complete reboot of the DB Server should be performed to ensure traffic capture.
11. In the Database server, repeat steps 7 & 8 (connect to the DB using all connection/protocol combinations) and execute
some SQLs to generate sample traffic in the Collector.
12. Verify in the FULL SQL Trace Report that all the types of connections/authentications (SQL/Windows) using all the
protocols (Named Pipes / TCP/IP) combinations are captured:
Figure 21 - SQL Trace Report
InfoSphere Optim Solutions for Distributed Systems Bootcamp
© Copyright IBM Corp. 2012. All rights reserved
Page 11 of 12
IBM Software
Information Management
© Copyright IBM Corporation 2012
All Rights Reserved.
IBM Canada
8200 Warden Avenue
Markham, ON
L6G 1C7
Canada
IBM, the IBM logo, ibm.com and Tivoli are trademarks or registered
trademarks of International Business Machines Corporation in the
United States, other countries, or both. If these and other
IBM trademarked terms are marked on their first occurrence in this
information with a trademark symbol (® or ™), these symbols indicate
U.S. registered or common law trademarks owned by IBM at the time
this information was published. Such trademarks may also be
registered or common law trademarks in other countries. A current list
of IBM trademarks is available on the Web at “Copyright and
trademark information” at ibm.com/legal/copytrade.shtml
Other company, product and service names may be trademarks or
service marks of others.
References in this publication to IBM products and services do not
imply that IBM intends to make them available in all countries in which
IBM operates.
No part of this document may be reproduced or transmitted in any form
without written permission from IBM Corporation.
Product data has been reviewed for accuracy as of the date of initial
publication. Product data is subject to change without notice. Any
statements regarding IBM’s future direction and intent are subject to
change or withdrawal without notice, and represent goals and
objectives only.
THE INFORMATION PROVIDED IN THIS DOCUMENT IS
DISTRIBUTED “AS IS” WITHOUT ANY WARRANTY, EITHER
EXPRESS OR IMPLIED. IBM EXPRESSLY DISCLAIMS ANY
WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
PARTICULAR PURPOSE OR NON-INFRINGEMENT.
IBM products are warranted according to the terms and conditions of
the agreements (e.g. IBM Customer Agreement, Statement of Limited
Warranty, International Program License Agreement, etc.) under which
they are provided.
InfoSphere Optim Solutions for Distributed Systems Bootcamp
© Copyright IBM Corp. 2012. All rights reserved
Page 12 of 12