Chat now with support
Chat with Support

One Identity Safeguard for Privileged Sessions 7.0.5.1 LTS - REST API Reference Guide

Introduction Using the SPS REST API Basic settings User management and access control Managing SPS General connection settings HTTP connections Citrix ICA connections MSSQL connections RDP connections SSH connections Telnet connections VNC connections Search, download, and index sessions Reporting Health and maintenance Advanced authentication and authorization Completing the Welcome Wizard using REST Enable and configure analytics using REST

MSSQL connections

Detailed information about this topic

Limitations in handling MSSQL connections

The current version of One Identity Safeguard for Privileged Sessions (SPS) has the following limitations:

  • TDS protocol version 7.3 or later is required.

  • Due to the TDS protocol version requirement, Microsoft® SQL Server® 2008, or later, is recommended.

  • The Require Gateway Authentication on the SPS Web Interface option in MSSQL Control > Connections does not work in case of MSSQL connections.

  • MSSQL server with TCP dynamic port settings is not supported.

    You must specify a static TCP port for every instance in the SQL Server Configuration Manager you want to audit. By doing so, you can configure the access to multiple MSSQL instances with multiple connection policies and specify the instances with inband or fixed targets and ports. You can also create and assign different Credential Store policies to check out SQL users' passwords of the instances.

    In the MSSQL client program, always specify the address with the port number of the SPS connection policy you want to connect to.

MSSQL connections

List of endpoints for configuring the policies, options and connection rules of MSSQL connections.

URL
GET https://<IP-address-of-SPS>/api/configuration/mssql
Sample request

The following command lists the available settings for configuring for MSSQL connections.

curl --cookie cookies https://<IP-address-of-SPS>/api/configuration/mssql
Response

The following is a sample response received when listing the configuration settings.

{
  "items": [
    {
      "key": "authentication_policies",
      "meta": {
        "href": "/api/configuration/mssql/authentication_policies"
      }
    },
    {
      "key": "channel_policies",
      "meta": {
        "href": "/api/configuration/mssql/channel_policies"
      }
    },
    {
      "key": "connections",
      "meta": {
        "href": "/api/configuration/mssql/connections"
      }
    },
    {
      "key": "options",
      "meta": {
        "href": "/api/configuration/mssql/options"
      }
    },
    {
      "key": "settings_policies",
      "meta": {
        "href": "/api/configuration/mssql/settings_policies"
      }
    }
  ],
  "meta": {
    "first": "/api/configuration/aaa",
    "href": "/api/configuration/mssql",
    "last": "/api/configuration/x509",
    "next": "/api/configuration/network",
    "parent": "/api/configuration",
    "previous": "/api/configuration/management",
    "remaining_seconds": 600,
    "transaction": "/api/transaction"
  }
}
Item Description
connections List of connection policies.
authentication_policies List of the default and custom authentication policies.
channel_policies List of the default and custom channel policies.
options List of global MSSQL options that affect all connections.
settings_policies List of protocol-level settings (idle and session timeout). You can create multiple variations, and choose the appropriate one for each connection policy.
Code Description Notes
401 Unauthenticated The requested resource cannot be retrieved because the client is not authenticated and the resource requires authorization to access it. The details section contains the path that was attempted to be accessed, but could not be retrieved.
403 Unauthorized The requested resource cannot be retrieved because the client is not authorized to access it. The details section contains the path that was attempted to be accessed, but could not be retrieved.
404 NotFound The requested object does not exist.

MSSQL connection policies

Connection policies determine if a server can be accessed from a particular client. Connection policies reference other resources (policies, usergroups, keys) that must be configured and available before creating a connection policy.

URL
GET https://<IP-address-of-SPS>/api/configuration/mssql/connections/
Sample request

The following command lists MSSQL connection policies.

curl --cookie cookies https://<IP-address-of-SPS>/api/configuration/mssql/connections/

The following command retrieves the properties of a specific policy.

curl --cookie cookies https://<IP-address-of-SPS>/api/configuration/mssql/connections/<connection-key>
Response

The following is a sample response received when listing MSSQL connection policies.

{
  "items": [
    {
      "key": "19443158785dee0621437ad",
      "meta": {
        "href": "/api/configuration/mssql/connections/19443158785dee0621437ad"
      }
    }
  ],
  "meta": {
    "first": "/api/configuration/mssql/channel_policies",
    "href": "/api/configuration/mssql/connections",
    "last": "/api/configuration/mssql/options",
    "next": "/api/configuration/mssql/options",
    "order": "/api/configuration/mssql/connections/@order",
    "parent": "/api/configuration/mssql",
    "previous": "/api/configuration/mssql/channel_policies",
    "remaining_seconds": 600,
    "transaction": "/api/transaction"
  }
}

When retrieving the endpoint of a specific MSSQL Connection Policy, the response is the following.

{
  "body": {
    "access_control": [],
    "active": true,
    "channel_database_cleanup": {
      "enabled": false
    },
    "indexing": {
      "enabled": true,
      "policy": {
        "key": "-50000",
        "meta": {
          "href": "/api/configuration/policies/indexing/-50000"
        }
      },
      "priority": 3
    },
    "log_audit_trail_downloads": true,
    "name": "demo_mssql",
    "network": {
      "clients": [
        "0.0.0.0/0"
      ],
      "ports": [
        1433
      ],
      "targets": [
        "192.168.1.1/24"
      ]
    },
    "override_log_level": {
      "enabled": false
    },
    "policies": {
      "aa_plugin": null,
      "analytics_policy": {
        "key": "20509709385cd578654cdab",
        "meta": {
          "href": "/api/configuration/policies/analytics/20509709385cd578654cdab"
        }
      },
      "archive_cleanup_policy": null,
      "audit_policy": {
        "key": "78101850949e47437dd91d",
        "meta": {
          "href": "/api/configuration/policies/audit_policies/78101850949e47437dd91d"
        }
      },
      "authentication_policy": {
        "key": "-30700201",
        "meta": {
          "href": "/api/configuration/mssql/authentication_policies/-30700201"
        }
      },
      "backup_policy": null,
      "channel_policy": {
        "key": "-30700102",
        "meta": {
          "href": "/api/configuration/mssql/channel_policies/-30700102"
        }
      },
      "credential_store": null,
      "ldap_server": null,
      "settings": {
        "key": "-30700301",
        "meta": {
          "href": "/api/configuration/mssql/settings_policies/-30700301"
        }
      },
      "usermapping_policy": null
    },
    "rate_limit": {
      "enabled": false
    },
    "server_address": {
      "custom_dns": {
        "enabled": false
      },
      "selection": "original"
    },
    "source_address": {
      "selection": "box_address"
    },
    "transport_security": {
      "selection": "disabled"
    },
    "web_gateway_authentication": {
      "enabled": false
    }
  },
  "key": "19443158785dee0621437ad",
  "meta": {
    "first": "/api/configuration/mssql/connections/19443158785dee0621437ad",
    "href": "/api/configuration/mssql/connections/19443158785dee0621437ad",
    "last": "/api/configuration/mssql/connections/19443158785dee0621437ad",
    "next": null,
    "parent": "/api/configuration/mssql/connections",
    "previous": null,
    "remaining_seconds": 600,
    "transaction": "/api/transaction"
  }
}
Element Type Description
key string Top level element, contains the ID of the connection policy.
body Top level element (string) The elements of the connection policy.
access_control Top level list

Collection of access policies. Access policies define who can authorize and audit a connection.

active boolean Set to false to suspend the connection policy. Connection settings are preserved.
channel_database_cleanup Top level item Configures cleanup of the connection metadata on the connection policy's level.
days int

Retention time, in days. Must not exceed the retention time of the archive_cleanup_policy, and the retention time configured in the global settings of the protocol.

The global settings of the MSSQL protocol are available at the api/configuration/mssql/options endpoint.

enabled boolean Set to true to enable periodical cleanup of the connection metadata.
indexing Top level item Configures indexing for the connection policy.
enabled boolean Set to true to enable indexing the connections.
policy string

References the identifier of the indexing policy. You can configure indexing policies at the /api/configuration/policies/indexing/ endpoint.

To modify or add an indexing policy, use the value of the returned key as the value of the policy element, and remove any child elements (including the key).

priority int

Specifies the indexing priority for the connection. Possible values are:

  • 5

    Very low priority.

  • 4

    Low priority.

  • 3

    Normal (default) priority.

  • 2

    High priority.

  • 1

    Very high priority.

  • 0

    Near real-time priority.

log_audit_trail_downloads boolean

Set to true to log audit trail downloads.

name string The name of the connection policy.
network
clients list, string List of client ("from") IP addresses.
ports list, integers List of target ports.
targets list, string List of target IP addresses.
override_log_level Top level item
policies Top level item List of policies referenced by the connection policy.
aa_plugin string

References the identifier of the AA plug-in. You can configure AA plug-ins at the /api/configuration/plugins/aa/ endpoint.

To modify or add an AA plug-in, use the value of the returned key as the value of the aa_plugin element, and remove any child elements (including the key).

analytics_policy string

References the identifier of the analytics policy. You can configure analytics policies at the /api/configuration/analytics/ endpoint.

To add or modify an analytics policy, use the value of the returned key as the value of the analytics element, and remove any child elements (including the key).

archive_cleanup_policy string

References the identifier of the archive/cleanup policy. You can configure archive and cleanup policies at the /api/configuration/policies/archive_cleanup_policies/ endpoint.

To modify or add an archive/cleanup policy, use the value of the returned key as the value of the archive_cleanup_policy element, and remove any child elements (including the key).

audit_policy string

Cannot be null.

References the identifier of the audit policy. You can configure audit policies at the /api/configuration/policies/audit_policies/ endpoint.

To modify or add an audit policy, use the value of the returned key as the value of the audit_policy element, and remove any child elements (including the key).

authentication_policy string

Cannot be null.

References the identifier of the authentication policy. Note that currently you cannot create or modify MSSQL Authentication Policies using the REST API. Use the web UI instead.

To modify or add an authentication policy, use the value of the returned key as the value of the authentication_policy element, and remove any child elements (including the key).

backup_policy string

References the identifier of the backup policy. You can configure backup policies at the /api/configuration/policies/backup_policies/ endpoint.

To modify or add a backup policy, use the value of the returned key as the value of the backup_policy element, and remove any child elements (including the key).

channel_policy string

You can configure MSSQL channel policies at the /api/configuration/mssql/channel_policies/ endpoint.

credential_store string

References the identifier of the credential store.

You can configure credential stores at the /api/configuration/policies/credentialstores/ endpoint.

To modify or add a credential store, use the value of the returned key as the value of the credential_store element, and remove any child elements (including the key).

ldap_server string

References the identifier of the LDAP server. You can configure LDAP servers at the /api/configuration/policies/ldap_servers/ endpoint.

To modify or add an LDAP server, use the value of the returned key as the value of the ldap_server element, and remove any child elements (including the key).

settings string
usermapping_policy string

References the identifier of a Usermapping Policy. You can configure Usermapping Policies at the /api/configuration/policies/usermapping_policies/ endpoint.

To modify or add a Usermapping Policy, use the value of the returned key as the value of the usermapping_policies element, and remove any child elements (including the key).

rate_limit Top level element Connection rate limit.
enabled boolean Set to true to provide a connection rate limit.
value int The number of connections (per minute) that are allowed in the connection policy.
server_address Top level item Defines the address where the clients connect to.
source_address Top level element Allows you to configure Source Network Address Translation (SNAT) on the server side of SPS. SNAT determines the IP address SPS uses in the server-side connection. The target server will see the connection coming from this address.
selection string

Configures Source Network Address Translation. Possible values are:

  • box_address

    Default. Uses the network address of the logical interface of SPS.

  • original

    Uses the IP address of the client, as seen by SPS.

  • fix

    Uses a fixed address when connecting to the remote server.

    Must be used with the address element.

address string

Must be used if the value of the selection element is set to fix.

The IP address to use as the source address in server-side connections.

transport_security Top level element

Configures the encryption used in the sessions.

certificate JSON object

Selects the certificate to show to the peers. You have the following options:

  • Use the same certificate for each client:

    Select this option if you want to use the same certificate for every peer. Note that you must reference a certificate that includes its private key that you have already uploaded to SPS. For details, see Certificates stored on SPS.

    "certificate": {
    "selection": "fix",
    "x509_identity": "893b7eb7-8c6f-403a-ba3a-1d09dc4b4c7a"
}
  • Generate a certificate for each client:

    Select this option if you want to generate a certificate for each client. Note that you must reference a Signing CA that you have already configured on SPS. For details, see Signing CA policies.

    "certificate": {
    "selection": "generate",
    "signing_ca": "1904188625a843f11d30a5"
},
selection disabled | tls

Configures the encryption used in the sessions.

  • disabled: Disables TLS encryption for MSSQL connections completely.

    "transport_security": {
    "selection": "disabled"
},

  • tls: Enables TLS-encryption. Note that you must also set the certificate and server_certificate_check options.

    "transport_security": {
    "certificate": {
        "selection": "generate",
        "signing_ca": "19605948865d07511f09eca"
    },
    "selection": "tls",
    "server_certificate_check": {
        "enabled": true,
        "trusted_ca": "1241814345d074efd1ded7"
    }
}
server_certificate_check JSON object

By default, SPS accepts any certificate shown by the server.

"server_certificate_check": {
    "enabled": false
},

To verify the certificate of the destination server, configure and reference a Trusted CA list.

"server_certificate_check": {
    "enabled": true,
    "trusted_ca": "9106862955a844051d7bf6"
},
web_gateway_authentication Top level item When gateway authentication is required for a connection, the user must authenticate on SPS as well. This additional authentication can be performed out-of-band on the SPS web interface for every protocol.
enabled boolean Set to true to enable additional gateway authentication on the SPS web interface.
groups list, string

By default, any user can perform gateway authentication for the connections. You can restrict authentication to members of specific usergroups. Define the usergroups at the /api/configuration/aaa/local_database/groups/ endpoint, and list the name of each group here.

require_same_ip boolean Set to true to only accept web gateway authentication from the same host that initiated the connection.
Elements of access_control Type Description
authorizer string

The usergroup (local or LDAP) who can authorize or audit the connection.

Local usergroups can be added or modified at the /api/configuration/aaa/local_database/groups/ endpoint.

permission string

Defines the permissions of the authorizer usergroup. Possible values are:

  • audit

    The usergroup with the audit permission can monitor ongoing connections, and download the audit trails of a closed and indexed connection.

  • authorize

    The usergroup with the authorize permission can authorize connection requests.

  • audit_and_authorize

    The usergroup with the audit_and_authorize permission can authorize connection requests, monitor connections, and download the audit trail of closed and indexed connections.

require_different_ip boolean Set to true to require the authorizing user and its subject to have different IP addresses.
require_different_username boolean Set to true to require the authorizing user and its subject to have different usernames.
subject Top level item Defines the subjects of the access control policy.
group string

The usergroup (local or LDAP) that is subject to the access control policy.

Local usergroups can be added or modified at the /api/configuration/aaa/local_database/groups/ endpoint.

selection string

Possible values:

  • everybody

    Every user is subject to the access control policy.

  • only

    Requires the group element.

    Members of the usergroup specified in the group element are subject to the access control policy.

Elements of server_address
Elements of server_address Type Description
custom_dns string
selection string

Configures the address where the clients connect to. Possible values are:

  • original

    Connect to the same address specified by the client.

  • nat

    Perform a network address translation on the target address.

    Must be used with the network element.

  • fix

    Must be used with the address and port elements.

  • inband

    Extract the address of the server from the username.

    Must be used with the domains element.

    Optional elements: exception_domains, dns_server, and dns_suffixes.

network string

Must be used if selection is set to nat.

The target address in IP/prefix format. Example: "10.20.30.40/24".

address string

Must be used if selection is set to fix.

The IP address of the target server.

port int

Must be used if selection is set to fix.

The port of the target server.

domains Top level list

Must be used if selection is set to inband.

domain Top level item Lists the address ranges that are included in the connection policy.
selection string

Specifies if the target address range is provided as a domain or as an IP range. Possible values are:

  • address

    The value of the target address is an IP range.

  • domain

    The value of the target address is a domain.

value string

The address range of the target server(s).

Use the selection element to specify if the address is an IP range, or a domain.

port int The port of the targer server(s).
exception_domains Top level list

Can only be used if selection is set to inband.

Lists the address ranges that are excluded from the connection policy.

domain Top level item Contains the excluded address range.
selection string

Specifies if the excluded address(es) are provided as a domain or as an IP range. Possible values are:

  • address

    The value of the excluded address is an IP range.

  • domain

    The value of the excluded address is a domain.

value string

The excluded address(es).

Use the selection element to specify if the address is an IP range, or a domain.

port int The excluded port.
dns_server string

Can only be used if selection is set to inband.

IP address or the hostname of the domain name server used to resolve the address of the target server.

dns_suffixes list, string

Can only be used if selection is set to inband.

If the clients do not include the domain name when addressing the server (for example they use username@server instead of username@server.example.com), SPS can automatically add domain information (for example example.com).

You can add multiple domain names. SPS attempts to resolve the target address by appending the domain names in the provided order, and uses the first successfully resolved address to establish the connection.

Add a MSSQL connection policy

To add a MSSQL connection policy, you have to:

  1. Create the JSON object for the new MSSQL connection policy.

    POST the JSON object to the https://<IP-address-of-SPS>/api/configuration/mssql/connections/ endpoint. You can find a detailed description of the available parameters listed in Element .

    If the POST request is successful, the response includes the key of the new MSSQL connection policy. For example:

    {
  "key": "a99be49b-b0a2-4cf9-b70d-fea1f9ea188f",
  "meta": {
    "href": "/api/configuration/mssql/connections/a99be49b-b0a2-4cf9-b70d-fea1f9ea188f",
    "parent": "/api/configuration/mssql/connections",
    "transaction": "/api/transaction"
  }
}
Modify a MSSQL connection policy

To modify a MSSQL connection policy, you have to:

  1. Modify the JSON object of the connection policy.

    PUT the modified JSON object to the https://<IP-address-of-SPS>/api/configuration/mssql/connections/<key-of-the-object> endpoint. You can find a detailed description of the available parameters listed in Element .

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating