Skip to main content
All CollectionsSetup
Technical Datasheet
Technical Datasheet
Updated over 2 months ago

Overview

SQL-Sales is an application installed on a SQL Server. It receives instructions from SQL Server Management Studio (SSMS) stored procedures to read and write to Salesforce. It comprises 3 executables:

SQLSalesConfig.exe

  • Configuration tool (with UI), where connections to Salesforce are defined

  • Connections and data requests are via basic Username+Password+Security token (SOAP or REST) as well as OAuth2.0 (JWT, via REST)

SQLSalesDaemon.exe

  • Constantly running Daemon process, connecting to Salesforce on demand, according to instructions from the end user (in SSMS) via the Handler

SQLSalesHandler.exe

  • Handles requests from SQL Server Management Studio (SSMS), which are passed to the Daemon

The installation is outlined below

SQL-Sales Config

This is where credentials and customisations to how data is processed, per given Salesforce instance, are defined. As shown, the connection methods are via traditional Username+Password+Security token (SOAP or REST) as well as OAuth2.0 (JWT, via REST).

Shown above are the Environments defined for the installation and a visual indicator of whether the Daemon is running/stopped – as well as the ability to start the Daemon (although running a SQL-Sales stored procedure in SSMS for an enabled Database will auto-start the Daemon). Above is an example (in this case for a sandbox called “demo”).

The process for storing configuration credential and attributes securely involves a series of steps designed to protect the data from unauthorised access by encrypting it before saving it to a file. This ensures that even if someone gains access to the storage file (unlikely on a SQL Server), they will not be able to understand or use the stored data without the correct key.

Note, the configuration file storage location can be fully configured by the SQL Server user. Typically this will be saved directly on the SQL Server itself (i.e. with the inherent protection of being on the root drives of the SQL Server). Alternatively, the storage location can be a network drive for example to which only the user of the SQL-Sales Config tool has access to.

OAuth 2.0 Setup

Whilst the traditional Username+Password+Security token is supported and requires no Salesforce setup other than the Managed Package, for enhanced security, an OAuth 2.0 connection option is offered which requires minimal Salesforce configuration. SQL-Sales OAuth 2.0 will intentionally not work without an additional user-created connected app, for the given Salesforce instance and SQL-Sales configuration installation/SQL Server. For maximum security each SQL-Sales config installation (on potentially multiple SQL Servers) connecting to the same Salesforce instance will need their own dedicated Custom Connected App setting up.

The setup process is documented here

In summary, the steps for creating a custom connected app are:

  1. In Salesforce. Create the new Connected App in the given Salesforce instance

    1. Enable OAuth

    2. Use digital signatures

    3. Selected OAuth Scopes

      1. Manage user data via APIs (api)

      2. Perform requests at any time (refresh_token, offline_access)

    4. Issue JSON Web Token (JWT)-based access tokens for named users

    5. OAuth Policies

      1. Admin approved users are pre-authorized

      2. Issue JSON Web Token (JWT)-based access tokens (ticked)

      3. Token Timeout (30 Minutes)

      4. Enforce IP restrictions (Refresh token is valid until revoked)

    6. Manage Profiles

      1. Selected Profile relevant for the nominated integration user

    7. Manage Consumer Details

      1. Copy the Consumer Key

  2. In SQL-Sales Configuration

    1. Enter Integration Username (the nominated integration user)

    2. Enter Custom Connected App (the name of the connected app created in step 1 above)

    3. Enter (paste) the Consumer Key from step 1 above

    4. Enter Expiration (days) – maximum permitted is 365

    5. Click “Create Certificate” this will automatically securely store the private key within the SQL-Sales Config storage mechanism and pass the public key to the clipboard, at no point (ever) does SQL-Sales save or store the public key

    6. User is wholly responsible for the creation and key vault storage (if relevant) of the resultant created .pem file where the key is pasted from the clipboard

  3. In Salesforce. Return to the custom connected app created in Step 1

    1. Use digital signatures

      1. Choose File (of the .pem user created file from Step 2)

  4. In SQL-Sales Configuration

    1. Click “Test Salesforce” – that’s at, OAuth2.0 setup is complete, this is a double-pronged security model where a Manage Package (Managed connected App) is required, alongside a custom instance-installation specific connected app must be setup to work alongside.

Installation Summary

A step by step guide

  1. Install the SQL-Sales Managed Package, see here

  2. Download installation zip SQL-Sales.zip from here. The zip contains:

    1. SQL-Sales read me.txt

    2. SQL-Sales Guide.pdf

    3. SQLSalesInstaller.exe

  3. Extract SQLSalesInstaller.exe from the zip and run, setup instructions here, this installs:

    1. SQLSalesConfig.exe

    2. SQLSalesHandler.exe

    3. SQLSalesDaemon.exe

    4. UninstallSQLSales.exe

    5. ss_EnableDatabase.sql

  4. Enable your required SQL Server Database, instructions here

  5. On the SQL Server, open “SQL-Sales Config”

    1. Create an Environment and configure as required

    2. Start the Daemon (optional, it will self-start on first use)

  6. Create a custom Conncted App if required, see here

  7. On either a client SQL Server Management Studio (SSMS) or from SSMS directly on the SQL Server, run the required stored procedure to read or write to Salesforce, depending on what you want to do, full online user documentation here, or standalone pdf here.

Did this answer your question?