Troubleshooting
Problem
Guardium External S-TAP Traffic on Windows MS SQL Server is missing DB User Name and Source Program.
Cause
Regardless of whether the database is configured for encryption or not, the login packets are always encrypted for native clients such as SQL Server Management Studio or sqlcmd. When using External S-TAP and MS SQL Server, you must complete configuration steps for encrypted traffic in order to see all the traffic. If you do not configure for encryption, you may see SQL statements but the DB User Name and Source Program will be missing.
Environment
External S-TAP and Microsoft (MS) SQL Server traffic for cloud database services.
Resolving The Problem
You need to configure the database connection for encryption. See the screen shot below for an example in SQL Server Management Studio. Depending on the client software program, you may or may not have the "Trust Certificate" checkbox option.
For java connection make sure you have the driver properties highlighted:
jdbc:sqlserver://<your_dbhost>:1433;database=<db_name>;user=<user>;password={your_password_here};encrypt=true;trustServerCertificate=false;
1. Obtain an intermediate signing key and certificate pair from your certificate authority. Please note that Guardium does not provide certificate authority services.
2. Use the following CLI command to store the signing key and signing certificate on the Guardium appliance as an intermediate certificate:
store certificate_external_stap_signing
Please paste your new certificate in PEM format. This command generates a token. The token is the certificate secret, which should be recorded to deploy External S-TAP.
The private key is encrypted with a passphrase Guardium requires a private key without one. If you know the passphrase, we can decrypt it now. If you do not know it, please obtain the key without a passphrase and redo this operation
Do you want to decrypt now? [y/n]:* y*
Enter pass phrase for <private key in PEM format>:
Key and Certificate verified OK.
SUCCESS: Intermediate signing certificate stored successfully!
Alias: external_stap_signingcert <Token>
Token: <Token>
Ok
Do you want to decrypt now? [y/n]:* y*
Enter pass phrase for <private key in PEM format>:
Key and Certificate verified OK.
SUCCESS: Intermediate signing certificate stored successfully!
Alias: external_stap_signingcert <Token>
Token: <Token>
Ok
3. Token can be also found by running the following command:
show certificate external_stap_signing
4. On the Docker host machine, run the External S-TAP deployment script container_mgmt.sh in interactive mode to set the correct options for your site as shown below. The deployment and load balancing scripts are available on GitHub at https://github.com/IBM/Guardium_External_S-TAP. Before you run or modify the scripts, be sure to read the CONTRIBUTOR.md and README.md files. See the example below.
Load-balancer script integration not specified, some functionality may be limited
Would you like to
(c)reate a new cluster
(p)rint env vars without creating cluster
(u)pgrade an existing cluster
(d)elete a cluster
remove (z)ombies
? c
Would you like to
(c)reate a new cluster
(p)rint env vars without creating cluster
(u)pgrade an existing cluster
(d)elete a cluster
remove (z)ombies
? c
Creating service containers for Guardium External S-TAP
What host do you want to use to host the service containers? [localhost]
Non-interactive parameter: --svc-host localhost
What is the port range for the exported service port? (0 means the ephemeral range on the service host) [0]
Non-interactive parameter: --svc-port-range 0
What user will be logging in to the host to start the service containers? [root]
Non-interactive parameter: --svc-host-user root
Enter the hash or tag for the service container image: docker.io/store/ibmcorp/guardium_external_s-tap:v11.2.0.137
Non-interactive parameter: --svc-image docker.io/store/ibmcorp/guardium_external_s-tap:v11.2.0.137
What is the username to be used if login is required to pull the service container image? (optional) gdmrepts
Non-interactive parameter: --repo-user gdmrepts
What is the password for gdmrepts?
Non-interactive parameter: --repo-pass XXXXXXXXXX
How many service containers would you like to create? [1]
Non-interactive parameter: --svc-container-num 1
Please enter a UUID for this group: [d1a49eb5-bc60-4745-8d05-897142dec76b]
Non-interactive parameter: --uuid d1a49eb5-bc60-4745-8d05-897142dec76b
Enter the number of workers for each service container of Guardium External S-TAP: [1]
Non-interactive parameter: --proxy-num-workers 1
Enter the hostname or IP to which the DB the Guardium External S-TAP group will be relaying traffic: (optional) <DB Server IP>
Non-interactive parameter: --db-host <DB Server IP>
Valid DB types are "oracle", "mssql", "sybase", "mongodb", "db2", "mysql", "memsql", "mariadb", "pgsql", "greenplumdb", "ver ticadb", "redis", "dynamodb", "el_search", "amazons3", "netezza"
Enter the type of database for the DB host: mssql
Non-interactive parameter: --db-type mssql
Enter the port for the DB to which the Guardium External S-TAP group will be relaying traffic: 1433
Non-interactive parameter: --db-port 1433
Enter an IP to override and force the server IP to be reported as (optional and uncommon, leave blank if not needed): [NULL]
If proxy protocol version 1 is enabled for the DB traffic, enter 1, otherwise enter 0: [0]
Non-interactive parameter: --proxy-protocol 0
Do you wish to disconnect the clients if the DB server certificate cannot be verified? (y/n) [N]
Do you wish to log an error message if the DB server certificate cannot be verified? (y/n) [N] Y
*If traffic is encrypted and you are generating CSRs on the collector and signing them separately, enter the secret token which will be used to retrieve the key and signed certificate from the Guardium Collector*: <Token>
Non-interactive parameter: --proxy-secret *4836f58e-2842-11eb-85df-c420bceef737*
Enter the hostname or IP of the Guardium Collector: <Collector hostname or IP>
Non-interactive parameter: --sqlguard-ip <Collector hostname or IP>
Participate in load balancing or failover? 0: failover/no lb, 1) split, 2) redundancy, 3) not allowed, 4) threaded: [0]
Non-interactive parameter: --participate-in-load-balancing 0
Enter the CN to match when verifying the Guardium Collector's Certificate (blank to disable verification):
What host do you want to use to host the service containers? [localhost]
Non-interactive parameter: --svc-host localhost
What is the port range for the exported service port? (0 means the ephemeral range on the service host) [0]
Non-interactive parameter: --svc-port-range 0
What user will be logging in to the host to start the service containers? [root]
Non-interactive parameter: --svc-host-user root
Enter the hash or tag for the service container image: docker.io/store/ibmcorp/guardium_external_s-tap:v11.2.0.137
Non-interactive parameter: --svc-image docker.io/store/ibmcorp/guardium_external_s-tap:v11.2.0.137
What is the username to be used if login is required to pull the service container image? (optional) gdmrepts
Non-interactive parameter: --repo-user gdmrepts
What is the password for gdmrepts?
Non-interactive parameter: --repo-pass XXXXXXXXXX
How many service containers would you like to create? [1]
Non-interactive parameter: --svc-container-num 1
Please enter a UUID for this group: [d1a49eb5-bc60-4745-8d05-897142dec76b]
Non-interactive parameter: --uuid d1a49eb5-bc60-4745-8d05-897142dec76b
Enter the number of workers for each service container of Guardium External S-TAP: [1]
Non-interactive parameter: --proxy-num-workers 1
Enter the hostname or IP to which the DB the Guardium External S-TAP group will be relaying traffic: (optional) <DB Server IP>
Non-interactive parameter: --db-host <DB Server IP>
Valid DB types are "oracle", "mssql", "sybase", "mongodb", "db2", "mysql", "memsql", "mariadb", "pgsql", "greenplumdb", "ver ticadb", "redis", "dynamodb", "el_search", "amazons3", "netezza"
Enter the type of database for the DB host: mssql
Non-interactive parameter: --db-type mssql
Enter the port for the DB to which the Guardium External S-TAP group will be relaying traffic: 1433
Non-interactive parameter: --db-port 1433
Enter an IP to override and force the server IP to be reported as (optional and uncommon, leave blank if not needed): [NULL]
If proxy protocol version 1 is enabled for the DB traffic, enter 1, otherwise enter 0: [0]
Non-interactive parameter: --proxy-protocol 0
Do you wish to disconnect the clients if the DB server certificate cannot be verified? (y/n) [N]
Do you wish to log an error message if the DB server certificate cannot be verified? (y/n) [N] Y
*If traffic is encrypted and you are generating CSRs on the collector and signing them separately, enter the secret token which will be used to retrieve the key and signed certificate from the Guardium Collector*: <Token>
Non-interactive parameter: --proxy-secret *4836f58e-2842-11eb-85df-c420bceef737*
Enter the hostname or IP of the Guardium Collector: <Collector hostname or IP>
Non-interactive parameter: --sqlguard-ip <Collector hostname or IP>
Participate in load balancing or failover? 0: failover/no lb, 1) split, 2) redundancy, 3) not allowed, 4) threaded: [0]
Non-interactive parameter: --participate-in-load-balancing 0
Enter the CN to match when verifying the Guardium Collector's Certificate (blank to disable verification):
*************************************************************************************
Login to localhost successful
Creating new cluster, description will be stored in cluster_state
*******************************************************************************
Login to localhost successful
Creating new cluster, description will be stored in cluster_state
*******************************************************************************
/proc/sys/kernel/core_pattern on localhost is "/var/core/core.%e.%p"
Recommended setting is "/tmp/core.%t.%e.%p" for automatic collection of core files in diagnostics
*******************************************************************************
Trying to pull repository docker.io/store/ibmcorp/guardium_external_s-tap ...
v11.2.0.137: Pulling from docker.io/store/ibmcorp/guardium_external_s-tap
Digest: sha256:e7830499e41927483d7ded188517d3852d701df32f7b07dc92acbaa61d8c0370
Status: Image is up to date for docker.io/store/ibmcorp/guardium_external_s-tap:v11.2.0.137
Creating service container <container_id> on localhost
*******************************************************************************
=================================================
Started service container : 961c751cebbe7664c4445176eef2310b82abfbf14cadcd58ec99d382f40c3943 (CONTAINER_IP <Container IP>, HOST localhost, EXTERNAL PORT 32768)
=================================================
Recommended setting is "/tmp/core.%t.%e.%p" for automatic collection of core files in diagnostics
*******************************************************************************
Trying to pull repository docker.io/store/ibmcorp/guardium_external_s-tap ...
v11.2.0.137: Pulling from docker.io/store/ibmcorp/guardium_external_s-tap
Digest: sha256:e7830499e41927483d7ded188517d3852d701df32f7b07dc92acbaa61d8c0370
Status: Image is up to date for docker.io/store/ibmcorp/guardium_external_s-tap:v11.2.0.137
Creating service container <container_id> on localhost
*******************************************************************************
=================================================
Started service container : 961c751cebbe7664c4445176eef2310b82abfbf14cadcd58ec99d382f40c3943 (CONTAINER_IP <Container IP>, HOST localhost, EXTERNAL PORT 32768)
=================================================
5. Make sure that docker container started:
/home/gdmrepts/Guardium_External_S-TAdocker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
961c751cebbe docker.io/store/ibmcorp/guardium_external_s-tap:v11.2.0.137 "/etc/entrypoint.s..." 7 seconds ago Up 6 seconds 0.0.0.0:32768->8888/tcp <container_id> /home/gdmrepts/Guardium_External_S-TAP
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
961c751cebbe docker.io/store/ibmcorp/guardium_external_s-tap:v11.2.0.137 "/etc/entrypoint.s..." 7 seconds ago Up 6 seconds 0.0.0.0:32768->8888/tcp <container_id> /home/gdmrepts/Guardium_External_S-TAP
6. Run this command to ensure that docker container will be auto-restarted after docker host restarted:
docker update --restart unless-stopped <container_id>
Related Information
Document Location
Worldwide
[{"Line of Business":{"code":"LOB24","label":"Security Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSMPHH","label":"IBM Security Guardium"},"ARM Category":[{"code":"a8m0z000000Gp0IAAS","label":"STAP"}],"ARM Case Number":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"11.2.0"}]
Product Synonym
STAP
Was this topic helpful?
Document Information
Modified date:
01 December 2020
UID
ibm16373442