Product Documentation

Use Case 3: Logging MSSQL Transactions in Transparent Mode

Jun 09, 2015

You can configure the NetScaler appliance to operate transparently between MSSQL clients and servers, and to only log or analyze details of all client-server transactions. Transparent mode is designed so that the NetScaler appliance only forwards MSSQL requests to the server, and then relays the server's responses to the clients. As the requests and responses pass through the appliance, the appliance logs information gathered from them, as specified by the audit logging or AppFlow configuration, or collects statistics, as specified by the Action Analytics configuration. You do not have to add database users to the appliance.

When operating in transparent mode, the NetScaler appliance does not perform load balancing, content switching, or connection multiplexing for the requests. However, it responds to a client's pre-login packet on behalf of the server so that it can prevent encryption from being agreed upon during the pre-login handshake. The login packet and subsequent packets are forwarded to the server.

Summary of Configuration Tasks

For logging or analyzing MSSQL requests in transparent mode, you have to do the following:

  • Configure the NetScaler appliance as the default gateway for both clients and servers.
  • Do one of the following on the NetScaler appliance:
    • If you can configure the use source IP address (USIP) option globally, create a load balancing virtual server with a wildcard IP address and the port number on which the MSSQL servers listen for requests (a port-specific wildcard virtual server). Then, enable the USIP option globally. If you configure a port-specific wildcard virtual server, you do not have to create MSSQL services on the appliance. The appliance discovers the services on the basis of the destination IP address in the client requests. For instructions, see Configuring Transparent Mode by Using a Wildcard Virtual Server.
    • If you do not want to configure the USIP option globally, create MSSQL services with the USIP option enabled on each of them. If you configure services, you do not have to create a port-specific wildcard virtual server. For instructions, see Configuring Transparent Mode by Using MSSQL Services.
  • Configure audit logging, AppFlow, or Action Analytics to log or collect statistics about the requests. If you configure a virtual server, you can bind your policies either to the virtual server or to the global bind point. If you do not configure a virtual server, you can bind your policies to only the global bind point.

Configuring Transparent Mode by Using a Wildcard Virtual Server

You can configure transparent mode by configuring a port-specific wildcard virtual server and enabling Use Source IP (USIP) mode globally. When a client sends its default gateway (the NetScaler appliance) a request with the IP address of an MSSQL server in the destination IP address header, the appliance checks whether the destination IP address is available. If the IP address is available, the virtual server forwards the request to the server. Otherwise, it drops the request.

To create a wildcard virtual server by using the command line

At the command prompt, type the following commands to create a wildcard virtual server and verify the configuration:

  1. add lb vserver <name> <serviceType> <IPAddress> <port>
  2. show lb vserver <name>

Example

> add lb vserver wildcardLbVs MSSQL * 1433 
 Done 
> show lb vserver wildcardLbVs 
	wildcardLbVs (*:1433) - MSSQL	Type: ADDRESS  
	State: UP 
	. . . 
 
 Done 
> 

To create a wildcard virtual server by using the NetScaler configuration utility

Navigate to Traffic Management > Load Balancing > Virtual Servers, and create a virtual server. Specify MSSQL as the protocol and * as the IP address.

To enable Use Source IP (USIP) mode globally by using the command line

At the command prompt, type the following commands to enable USIP mode globally and verify the configuration:

  • enable ns mode USIP
  • show ns mode

Example

> enable ns mode USIP 
 Done 
> show ns mode  
 
	Mode                              Acronym                       Status 
	-------                           -------                       ------ 
               . . . 
 3)	Use Source IP                  USIP                          ON 
               . . . 
 Done 
> 

To enable USIP mode globally by using the NetScaler configuration utility

  1. Navigate to System > Settings and, in Modes and Features, select Configure Modes.
  2. Select Use Source IP.

Configuring Transparent Mode by Using MSSQL Services

You can configure transparent mode by configuring MSSQL services and enabling USIP on each service. When a client sends its default gateway (the NetScaler appliance) a request with the IP address of an MSSQL server in the destination IP address header, the appliance forwards the request to the destination server.

To create an MSSQL service and enable USIP mode on the service by using the command line interface

At the command prompt, type the following commands to create an MSSQL service, with USIP enabled, and verify the configuration:

  • add service <name> (<IP> | <serverName>) <serviceType> <port> -usip YES
  • show service <name>

Example

> add service myDBservice 192.0.2.0 MSSQL 1433 -usip YES 
 Done 
> show service myDBservice 
	myDBservice (192.0.2.0:1433) - MSSQL 
	State: UP 
               . . .  
	Use Source IP: YES		Use Proxy Port: YES	 
               . . . 
 Done 
>

To create an MSSQL service, with USIP enabled, by using the NetScaler configuration utility

  1. Navigate to Traffic Management > Load Balancing > Services, and configure a service.
  2. Specify protocol as MSSQL and, in Settings, select Use Source IP.