Skip to main content

SQL Server Connection

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

Setup Instructions​

In the setup process below, you will create and configure a Connection object with a SQLServer ConnectorType that is used to instantiate a connection. The agent configuration will be updated by placing the connection object into the DataGrailAgentConfig connections array.

Example Connection Object
{
  "name": "Contacts DB",
  "uuid": "6f4f35e0-8fdc-4729-b52a-2b659eb6d7c6",
  "capabilities": ["privacy/access", "privacy/deletion", "privacy/optout", "privacy/identifiers"],
  "mode": "live",
  "connector_type": "SQLServer",
  "queries": {
    "access": ["EXEC sp_dsr_access %(email)s;"],
    "delete": ["EXEC sp_dsr_delete %(email)s;"],
    "optout": ["EXEC sp_dsr_optout %(email)s;"],
    "identifiers": {
      "phone_number": ["EXEC sp_dsr_get_phone %(email)s;"]
    }
  },
  "credentials_location": "arn:aws:secretsmanager:Region:AccountId:secret:datagrail-rm-agent-sqlserver"
}
Prerequisites

Before performing the steps below, ensure the following:

  • Request Manager Agent is deployed and connected in DataGrail.
  • Network is configured to allow the Agent to connect with the SQL Server instance.

Create Request Logic​

In this step, create the request logic to be executed in your system for a specific request type.

When a Data Subject requests to access the personal information that your company maintains on them, they will submit an Access request. Your request logic should return all applicable information found in this system.

Example Access Query
SELECT * FROM contacts WHERE email = 'subject@domain.com';
Using Stored Procedures

For ease of maintainability and readability, using stored procedures is recommended instead of individual queries. When you need to modify the underlying queries, there will be no need to modify the Agent configuration nor restart the Agent.

Example Stored Procedure
EXEC sp_dsr_access 'subject@domain.com';

Create and Store Credentials​

  1. In SQL Server, create a new user for the agent. Only grant the minimum necessary permissions for executing the request.

  2. Configure the following JSON key-value pairs:

    {
      "user": "<agent user>",
      "password": "<agent password>",
      "server": "<server domain name or IP address>",
      "port": "<port, e.g. 1433>",
      "database": "<database name>"
    }

  3. Store the JSON value in your vault with an entry name like datagrail-rm-agent-sqlserver.

  4. Ensure that the agent is configured to retrieve the value of this vault entry.

Create Connection Object​

To define the interactions between the agent and the internal system, configure the Connection object.

  1. Using the empty connection object below, enter a connection name and uuid (v4).

    {
      "name": "",
      "uuid": "",
      "capabilities": [],
      "mode": "",
      "connector_type": "SQLServer",
      "queries": {
        "access": [],
        "delete": [],
        "optout": [],
        "identifiers": {}
      },
      "credentials_location": ""
    }

  2. Set the capabilities that correspond to your request logic.

  3. Under queries, add your request logic as a string to the corresponding request type.

  4. 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. Replace any static identifier in the request logic using pyformat parameter formatting (e.g., ...WHERE email=%(email)s).

    Parameter Binding Example
    -- BEFORE
    EXEC sp_dsr_access 'subject@domain.com';

    -- AFTER
    EXEC sp_dsr_access %(email)s;

  5. Set the credentials_location value to the location of the secret created above (e.g., AWS ARN).

Example Connection Objects

After following the steps above, the resulting connection object should look similar to these examples.

Example Connection Object with Access Capability
{
  "name": "Contacts DB",
  "uuid": "6f4f35e0-8fdc-4729-b52a-2b659eb6d7c6",
  "capabilities": ["privacy/access"],
  "mode": "live",
  "connector_type": "SQLServer",
  "queries": {
    "access": ["EXEC sp_dsr_access %(email)s;"],
    "delete": [],
    "optout": [],
    "identifiers": {}
  },
  "credentials_location": "arn:aws:secretsmanager:Region:AccountId:secret:datagrail-rm-agent-sqlserver"
}

Update and Restart Agent​

In this section, you will integrate the internal system with DataGrail by updating the agent environment variable and restarting the agent to apply the changes.

Already Processing Requests?

If your DataGrail account is already processing Data Subject Requests, new agent connections may be automatically processed with all new requests. If you are not sure, check with your DataGrail admin or dedicated Account Manager before starting this section.

  1. In the DATAGRAIL_AGENT_CONFIG environment variable, add the new connection object to the connections array.
  2. Save the environment variable, and restart the agent service to apply the changes.
  3. After the restart, confirm that the agent service is healthy, and the logs contain no errors.
  4. In DataGrail, update the integration by navigating to Integrations and select the agent integration.
  5. Select Edit Integration, enter the Client Secret, and then select Save Changes.
  6. To view the new connection, navigate back to Integrations and locate it in the list.
Processing Your First Request

Now that you've successfully added this connection to DataGrail, try it out by submitting and processing a request.

 

Need help?
If you have any questions, please reach out to your dedicated Account Manager or contact us at support@datagrail.io.

Disclaimer: The information contained in this message does not constitute as legal advice. We would advise seeking professional counsel before acting on or interpreting any material.