SQL Server

Automate the fulfillment of data subject access, deletion, and identifier retrieval requests in SQL Server.

Configuration

Create a Connection object with a SQLServer ConnectorType to instantiate a connection. Once the object has been created and the credentials have been stored, place the object in the RMAgentConfig connections array.

Example Configuration

{
    "name": "Contact DB",
    "uuid": "6a058f35-c37b-423f-b418-4324725b5ff5",
    "capabilities": ["privacy/access","privacy/delete", "privacy/optout", "privacy/identifiers"],
    "mode": "live",
    "connector_type": "SQLServer",
    "queries": {
        "identifiers": {
            "phone_number": [
                "SELECT phone FROM customers WHERE email=%(email)s"
            ]
        },
        "access": ["SELECT * FROM contacts WHERE email = %(email)s"],
        "delete": ["DELETE FROM contacts WHERE email = %(email)s"],
        "optout": ["UPDATE contacts SET optout = true WHERE email = %(email)s"]
    },
    "credentials_location": "arn:aws:secretsmanager:Region:AccountId:secret:datagrail.snowflake"
}

Credential Creation

The SQL Server connection authenticates with your server using username and password. Create a new secret in you credentials manager with the following key/value pairs:

{
    "user": "<DB username>",
    "password": "<DB password>",
    "server": "<server domain name or IP address>",
    "port": "<port, defaults to 1433>",
    "database": "<DB, e.g. BikeStores>"
}

Query Syntax and Parameter Binding

The Queries object allows query strings to be dynamically formatted with identifiers. Identifiers are passed individually to queries and are bound to the variables in the operation. Variables are specified using the pyformat parameter style, e.g. ...WHERE email=%(email)s.

Best Practices

For ease of maintainability and readability, it is recommended that the various queries be stored procedures. This allows for the underlying queries to be modified in SQL Server without needing to modify the Agent configuration, and for the query lists to be easily readable, especially in the case of complex joins.