MS SQL Server (DataDirect)

Configure an MS SQL Server DataDirect datasource on your Guardium® system.

Tip: To set a dynamic IP, go to the DB server and set the dynamic port type to 0. Remove TCP/IP and restart the services.

Supported Authentication Methods

Authentication Supported
Local user Yes
LDAP Yes
Kerberos No
SSL Yes
Mutual SSL No

Parameters

Field Description
Host Name/IP Required. The hostname or IP address of the datasource.
Port number Required. Default value: 1433.
Instance name The name of the instance to which you want to connect on the server.
Database The name of the database. The default value is Master.
Connection property Properties that must be included in the JDBC URL to establish a JDBC connection with the datasource. The required format is property1=value;property2=value, where each property and value pair is separated by a semicolon.
For examples, refer to the database vendor's JDBC documentation.
For example,
domain=domain_name;AuthenticationMethod=authentication_method; encryptionMethod=encryption_method;validateServerCertificate=true_or_false;

Where:

  • domain_name is the name of the domain server. If the driver cannot determine the domain name, the connection fails and produces an error.
  • AuthenticationMethod determines the authentication method that the driver uses when a connection is established. If the authentication method is not supported by the database server, the connection fails and produces an error.
    The following values for AuthenticationMethod are valid:
    • ntlm
    • ntlmjava
    • ntlm2java
    For Windows authentication, use the following property:
    domain=domain_name;AuthenticationMethod=ntlmjava
    To use NTLMv2 for Windows authentication, use the following property:
    domain=domain_name;AuthenticationMethod=ntlm2java
    Attention:
    • If you specify AuthenticationMethod=ntlmjava when the LMCompatabilityLevel is restricted to NTLMv2, an error is returned. When the LMCompatabilityLevel is restricted to NTLMv2, AuthenticationMethod must be set to ntlm2java.
    • If you specify AuthenticationMethod=ntlmjava or AuthenticationMethod=ntlm2java, you must also specify the name of the domain server that administers the database. You can specify the domain server by using the domain property. If the domain property is not specified, the driver tries to determine the domain server from the user property. If the driver cannot determine the domain server name, it returns an exception.
  • For nonstandard databases:
    If you are using a nonstandard database Unicode such as Azeri_Cyrillic_100_CI_AS or Chinese_Hong_Kong_Stroke_90_CI_AS, then add the following parameter to the connection property:
    CodePageOverride=UTF-8
  • For SSL authentication:
    To use SSL, add the following property, 
    encryptionMethod=SSL;validateServerCertificate=false
  • Connecting to named instances:
    • Microsoft SQL Server 2000 and higher support multiple instances of a Microsoft SQL Server database that is running concurrently on the same server.
    • An instance is identified by an instance name. To connect to a named instance by using a connection URL, use the following URL format,
      jdbc:datadirect:sqlserver://server_name\\instance_name
      Where:
      • server_name is the IP address or hostname of the server.
      • instance_name is the name of the instance to which you want to connect on the server.
      For example, the following connection URL connects to an instance named instance1 on server1:
      jdbc:datadirect:sqlserver://server1\\instance1;User=test;Password=secret
      Where the first backslash character (\) in \\instance_name is an escape character.
Custom URL The connection string to the datasource. When the custom URL is not provided, the datasource connection is made by using properties such as the hostname and port number.

Adding custom properties for classification

To improve the performance of Guardium classification scans, you can add the following custom properties for Microsoft SQL Server.
Note: This feature requires Guardium patch 11.0p451.
  • LARGE-TEXT-DATA-TYPE-MAX-LENGTH - By default, when scanning large data types like XML or VARCHAR(MAX), the classifier only samples the first 3000 characters. Use the LARGE-TEXT-DATA-TYPE-MAX-LENGTH property to define a custom length in the range of 0 - 2147483647 characters.
  • MAXDOP - The MAXDOP property defines how many processors MS SQL Server uses for parallel plan execution. The default setting of 0 allows MS SQL Server to use all available processors (up to a maximum of 64). Setting MAXDOP to 1 prevents parallel plan generation. Allowed values are 0 - 32,767.

    For more information, see Set the Max Degree of Parallelism Option in the Microsoft SQL Docs.

  • TRANSACTION-ISOLATION-LEVEL - When you add this property with READ-UNCOMMITTED, the classier reads all rows (including uncommitted rows). READ-UNCOMMITTED is the only value currently supported for the TRANSACTION-ISOLATION-LEVEL property, and the value must be uppercase and include the hyphen.

    For more information, see SET TRANSACTION ISOLATION LEVEL in the Microsoft SQL Docs.

You can add or change custom properties from either the Guardium UI or by using GRDAPI commands. After you add the custom properties, the information displays in the Details for datasource output. For more information about adding custom properties from the UI, see Configuring custom properties for your datasources.

You can use GRDAPIs to manage custom properties. For example, use the following to create MAXDOP and TRANSACTON-ISOLATION-LEVEL custom properties:
grdapi create_datasource_custom_property name=MAXDOP values=1
grdapi create_datasource_custom_property name="TRANSACTION-ISOLATION-LEVEL" values="READ-UNCOMMITTED"
Use the following to add the MAXDOP and TRANSACTION-ISOLATION-LEVEL properties to a MS SQL Server datasource:
grdapi add_custom_property_to_datasource_by_name customProps="TRANSACTION-ISOLATION-LEVEL=READ-UNCOMMITTED" name="yourmssqlserverdatabasename"
grdapi add_custom_property_to_datasource_by_name customProps=MAXDOP=1 name="yourmssqlserverdatabasename"
Use the following to add additional MAXDOP values (where 2,4 indicates the list of values added):
grdapi update_datasource_custom_property name=MAXDOP addValues=2,4

For more information about the custom property APIs, see Datasource custom property APIs.

For more information about classification, see Discover Sensitive Data.

CAS (Configuration Auditing System) database instance

If you are a CAS user, configure the CAS database instance.

Field Description
Account The name of the account owner. Required if Windows Authentication is used.
Directory The name of the installation directory.
To use the datasource for Vulnerability Assessments, enter the path to your database instance home directory, for example,
  • MSSQL2014, default instance

    C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL

  • MSSQL2016, Name instance

    C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL

  • Oracle 2019

    C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL

To use the datasource for other tests such as CAS file monitoring, enter the path to the MS SQL Server directory, for example,

  • C:\Program Files (x86)\Microsoft SQL Server
  • C:\Program Files\Microsoft SQL Server