Product Documentation
ADC
14.1 - Current Release 13.1 13.0 12.1
View PDF

Use Case 3: Log MSSQL transactions in transparent mode

September 14, 2021
Contributed by: 
S

You can configure the Citrix ADC 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 Citrix ADC 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 Citrix ADC 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 Citrix ADC appliance as the default gateway for both clients and servers.
  • Do one of the following on the Citrix ADC appliance:
    • 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 based on the destination IP address in the client requests.
    • 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.
  • 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.

Configure 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 Citrix ADC 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.

Create a wildcard virtual server by using the CLI

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

add lb vserver <name> <serviceType> <IPAddress> <port>

show lb vserver <name>
<!--NeedCopy-->

Example:

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

 Done
>
<!--NeedCopy-->

Create a wildcard virtual server by using the GUI

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

Enable Use Source IP (USIP) mode globally by using the CLI

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

enable ns mode USIP

show ns mode
<!--NeedCopy-->

Example:

> enable ns mode USIP
 Done
> show ns mode

    Mode                              Acronym                       Status
    -------                           -------                       ------
               . . .
 3) Use Source IP                  USIP                          ON
               . . .
 Done
>
<!--NeedCopy-->

Enable USIP mode globally by using the GUI

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

Configure 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 Citrix ADC 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.

Create an MSSQL service and enable USIP mode on the service by using the CLI

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>
<!--NeedCopy-->

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
>
<!--NeedCopy-->

Create an MSSQL service, with USIP enabled, by using the GUI

  1. Navigate to Traffic Management > Load Balancing > Services, and configure a service.
  2. Specify the protocol as MSSQL and, in Settings, select Use Source IP.
Use Case 3: Log MSSQL transactions in transparent mode
September 14, 2021
Contributed by: 
S
September 14, 2021
Contributed by: 
S

In this article

Site feedback | Your privacy choices footer linkYour Privacy Choices | Privacy and legal terms | Cookie preferences | Consent settings | docs.cloud.com
© 1999- Cloud Software Group, Inc. All rights reserved.