1. Install the Gretel Client along with its AWS Hybrid Dependencies. https://docs.gretel.ai/gretel-basics/getting-started/environment-setup/cli-and-sdk

pip install -U "gretel-client" "gretel-client[aws]"

2. Set your AWS profile env var as needed.

export AWS_PROFILE="your-relevant-profile"

3. Create the MS SQL Connector JSON file. For this example I named it mssql-hybrid.json. https://docs.gretel.ai/create-synthetic-data/workflows-and-connectors/connectors/database/ms-sql-server

{
    "type": "mssql",
    "name": "my-mssql-connection",
    "config": {
        "username": "john",
        "host": "myserver.example.com",
        "port": 1443,
        "database": "mydatabase",
        "schema": "dbo",
        "params": "TrustServerCertificate=True"
    },
    "credentials": {
        "password": "..."
    }
}

4. Configure credentials for the Gretel CLI. This starts a step by step wizard style configuration prompt. Hit enter to keep whatever is shown in square brackets. Example values shown below. https://docs.gretel.ai/gretel-basics/getting-started/environment-setup/cli-and-sdk#authentication

gretel configure

# Keep endpoint as the default
Endpoint [https://api.gretel.cloud]: <enter>
# Set your artifact endpoint to point to the sink bucket in your management AWS account
Artifact Endpoint [s3://your-gretel-hybrid-sink-bucket]: s3://your-gretel-hybrid-sink-bucket
# Enter "hybrid" here
Default Runner (cloud, local, hybrid) [hybrid]: hybrid
# Paste in the API key we discussed
Gretel API Key [grtu7363****]: <paste your API key and hit enter>
# Default project can stay as "none"
Default Project [none]: <enter>

INFO: Configuration written to /Users/benjaminmccown/.gretel/config.json. Done.
{
    "endpoint": "https://api.gretel.cloud",
    "artifact_endpoint": "s3://your-gretel-hybrid-sink-bucket",
    "api_key": "grtu7363****",
    "default_project_name": null,
    "default_runner": "hybrid",
    "preview_features": "disabled"
}

5. Create a Gretel Project which will contain the connector we're going to create. Anyone that you share the project with will have access to use the connector in any of their own existing Gretel Projects. We'll use the --set-default flag so that we don't have to pass the project as an input when creating the connection in the following step. https://docs.gretel.ai/create-synthetic-data/workflows-and-connectors/connectors#permissions-and-connection-sharing

gretel projects create --name "Gretel-Hybrid-Connections" --display-name "Gretel Hybrid Connections" --set-default

6. Create the connection. You have two options here. The easiest is to pass in the KMS key ARN and the Gretel CLI will use the AWS boto3 SDK to encrypt your database credentials before sending them to the Gretel CLI.

Option 1 - Creds are encrypted in memory before being sent to the Gretel API

gretel connections create --from-file mssql-hybrid.json --aws-kms-key-arn "arn:aws:kms:us-west-2:012345678912:key/12345678-726d-4cd9-ab8a-123456789012"

Option 2 - Encrypt the creds yourself

You might use this option if you don't trust that our CLI is encrypting the credentials before sending them to the Gretel API.

First, create a new JSON file that has the plaintext contents of the credentials JSON property from the file we already created.

db-creds-unencrypted.json

{
    "password": "..."
}

Update your connection JSON definition to remove the credentials. I'll call the new file mssql-hybrid-no-creds.json for clarity.

{
    "type": "mssql",
    "name": "my-mssql-connection",
    "config": {
        "username": "john",
        "host": "myserver.example.com",
        "port": 1443,
        "database": "mydatabase",
        "schema": "dbo",
        "params": "TrustServerCertificate=True"
    }
}

Encrypt the credentials json file using the AWS CLI.

# Set some handy environment variables
export CONNECTION_FILE=mssql-hybrid-no-creds.json
export INPUT_CREDS_FILE=db-creds-unencrypted.json
export OUTPUT_CREDS_FILE=db-creds-encrypted.json
export KMS_KEY_ARN="arn:aws:kms:us-west-2:012345678912:key/12345678-726d-4cd9-ab8a-123456789012"
export KMS_KEY_ID="12345678-726d-4cd9-ab8a-123456789012"

# Encrypt the creds
aws kms encrypt \
    --key-id $KMS_KEY_ID \
    --plaintext "fileb://$INPUT_CREDS_FILE" \
    --output text \
    --query CiphertextBlob | base64 \
    --decode > $OUTPUT_CREDS_FILE

You can see if you look at the encrypted creds file that it is now encrypted and not human readable.

cat $OUTPUT_CREDS_FILE

Finally, pass in the manually encrypted creds when creating the connection.

gretel connections create --from-file "$CONNECTION_FILE" --aws-kms-key-arn "$KMS_KEY_ARN" --aws-kms-encrypted-credentials "$OUTPUT_CREDS_FILE"

7. All finished! You can see the created connection with the Gretel CLI.

gretel connections list