Skip to main content

Technical Datasheet

Updated over 2 weeks 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 external client 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 External Client App setting up.

The setup process is documented here

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

  1. In SQL-Sales Configuration

    1. Enter Integration Username (the nominated integration user)

    2. Enter Custom External client App Name

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

    4. 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

  2. Save the certificate (which is not held in SQL-Sales)

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

  3. Create the new External Client app in the given Salesforce instance

    1. Name; Email; Distribution State "Local"

    2. Callback URL

    3. Selected OAuth Scopes

      1. Manage user data via APIs (api)

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

    4. Enable JWT Bearer Flow

      1. Upload Certificate

    5. Save

  4. Edit OAuth Policies

    1. Admin approved users are pre-authorized

      1. Select Profile or Permission Set with permissions to External Client Apps

    2. Refresh Token Policy

      1. Refresh token is valid until revoked

    3. Save

  5. Edit Settings - OAuth Settings

    1. Copy Consumer Key

  6. Paste to Consumer Key in Configuration Manager

    1. Test

    2. Save & Close

  7. Save Environment Definition

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?