Connect SQL Server to Salesforce: 3 Easy Methods

Various business intelligence, analytics, and transaction processing operations are supported by Microsoft SQL Server. You might need to connect SQL Server to Salesforce to simplify analysis for your data analysts by centralizing the data. You can also use APIs that push data from SQL Server to Salesforce CRM platform directly, making the entire process streamlined.

This article discusses the different methods to Connect SQL Server to Salesforce extensively. In addition to that, it also describes SQL Server and Salesforce briefly.

What is SQL Server?

Developed in 1989, SQL Server is one of the Relational Database Management Systems (RDBMS) developed by Microsoft. SQL Server is based on SQL, a widely-used programming language for working with relational databases. SQL Server has been available on Linux since 2016, but it has previously only been available on Windows operating systems.

The SQL Server consists of a core component called a Database Engine. This Database engine consists of a relational engine to process queries and a storage engine to manage databases, files, pages, indexes, and more. Database objects such as stored procedures, views, and triggers are also executed by the Database engine.

Steps to install SQL Server:

• Install Cumulative Update (when you haven’t used any SQL server slipstream setup)
• If your Windows Server is 2016 and 2019
  • Microsoft SQL server Management Studio
  • Microsoft OLE DB Driver for SQL server

• Install a Standalone Database Engine
• Install SQL Server Management Studio
• Install Microsoft OLE DB Driver for SQL Server. This is for communicating with an SQL Database Server.
• Install Cumulative Update (Carry on with these steps if you didn’t install the cumulative update when you installed Microsoft SQL.)

Key Features of SQL Server

• Analytics Support: Organizations seek insights into stored and managed data to make better decisions. With Microsoft SQL, you can analyze your stored data using integration with Microsoft SQL Server Analysis (SSAS) which is an online analytical processing tool.
• Multiple Integrations: SQL enables users to perform various tasks like Data Extraction, Transformation, Data Migration, Data Loading, and more on their stored data using Microsoft SQL Server Integration Services (SSIS).
• High Performance: SQL Server is a database programming language that allows you to build highly transactional databases. With SQL queries, you can describe your data analytically. 
• High Availability: SQL Server is compatible with MySQL, Oracle database, Microsoft Access, IBM DB2, and more.  
• High Security: Tables, views, stored procedures, and other SQL Server objects can all have permissions assigned to them.

What is Salesforce?

Salesforce is a cloud-based software company that offers sales, customer service, marketing automation, analytics, and application development CRM software and applications.

Salesforce is a well-known cloud-based CRM (Customer Relationship Management) platform. It has over 800 apps to help with things like generating new leads, acquiring new leads, increasing sales, and closing deals. Its purpose is to keep track of the company’s data of customers and sales. It also has options for customizing its data structures and graphical user interface to meet a company’s specific requirements. It has recently begun to connect the CRM platform to the IoT (Internet of Things). Learn about Salesforce Connect.

Key Features of Salesforce

• Contact Management: To see information about customers, such as contact information, activity history, customer communications, and internal account discussions, among other things. In a nutshell, it keeps track of all customer data.
• Opportunity Management: It contains information about the stage of a deal, the products involved in the deal, and the deal’s quotation, among other things. In a nutshell, it keeps track of all the information needed to identify, progress, and close a deal.
• Sales Performance Management: It provides the sales team with metric-based goal setting, as well as continuous feedback, rewards, and recognition. This aids the sales team’s performance.
• Lead Management: This feature starts and tracks the leads that are currently active. It also aids in the continuous optimization of campaigns across all channels.
• Partner Management: This feature aids in the formation of a community among partners. It also facilitates direct communication with channel partners to share goals, objectives, and activities.
• Territory Management: This feature is used to create multiple territory models, preview them before rollout, and optimize and balance territories throughout the year.

Ways to Connect SQL Server to Salesforce

The implementation of SQL Server to Salesforce integration enhances overall efficiency by providing a centralized data hub, allowing businesses to harness the power of both platforms for comprehensive data analysis and reporting. Understanding how to migrate data from SQL Server to Salesforce involves understanding the available integration tools provided by both platforms. 

Here are 3 methods to connect SQL Server to Salesforce.

• Connect SQL Server to Salesforce: Import and Export
• Connect SQL Server to Salesforce: With a Linked Server
• Connect SQL Server to Salesforce: Through SSIS 

Connect SQL Server to Salesforce: Import and Export

• SQL Server comes with a plethora of native and third-party tools, including, of course, tools for importing data from other sources. Start with SQL Server Import and Export Wizards, which are standard SQL Server and Salesforce Tools for importing data to connect SQL Server to Salesforce.
• By default, the SQL Server Import Wizard allows you to import data from other SQL Server databases, Oracle, Flat Files, such as CSV, and Access Data Source, PostgreSQL, MySQL, or Azure Blob Storage. Importing data directly from Salesforce, on the other hand, necessitates the use of a third-party ADO.NET provider or an ODBC driver.
• The SQL Server Import Wizard can also be used to export Salesforce Data to CSV and then import it into SQL Server. Salesforce native tools for exporting Salesforce data to CSV files are available in the cloud, such as Dataloader.io, and locally, such as the Data Loader tool. Salesforce allows you to download the latter directly. It’s in the Platform Tools section of Setup.
• Follow the steps below to Connect SQL Server to Salesforce by Import and Export Wizards:
  • Step 1: Select Data Loader from the Data Management menu.

• Step 2: You must first download and install the appropriate version of Data Loader, then log into Salesforce and export data from the necessary objects. 
• Step 3: Next, you can import them into SQL Server tables with the SQL Server Import Wizard. Please keep in mind that this method of data integration is one-way only. It is not possible to migrate data backwards.

You can find more information about SQL Server Import Wizard in Microsoft documentation.

Limitations: 

• Export scheduling does not guarantee full control over execution time, and delays caused by Salesforce traffic can lead to data loss.
• Lacks essential metadata backups for rebuilding custom settings and relationships.
• Data export options have limitations in data restoration, requiring manual work, deduplication, and the use of spreadsheets, which can be time-consuming. Object relationships need to be preserved to avoid incomplete restores or data corruption.
• No easy way to compare lost or modified data, and restoring records with object relationships can be challenging.
• Backup files are often stored on laptops or PCs, creating potential IT security risks.

Connect SQL Server to Salesforce: With a Linked Server

Set up a linked server for Salesforce Data using the SQL Gateway and the ODBC Driver.

You can use the SQL Gateway to set up a linked server for Salesforce Data and configure a TDS (SQL Server) remoting service. To create the linked server, use the UI in SQL Server Management Studio or call stored procedures after the service has been started. After that, you can work with Salesforce Data as if it were a linked SQL Server instance to connect Salesforce to SQL Server. Follow the steps given below to configure a Linked Server as a Salesforce SQL Server connector:

Step 1: Connect to Salesforce as an ODBC Data Source

• If you haven’t already done so, create an ODBC DSN with Connection Properties (data source name). The driver installation process comes to a close with this step. You can create and configure ODBC DSNs with the Microsoft ODBC Data Source Administrator.
• To connect SQL Server to Salesforce, you can use one of three authentication methods: login, OAuth, or SSO. You’ll need the user’s Username, Password, and Security Token to use the Login method.
• You can use OAuth Authentication if you don’t have access to the Username and Password or don’t want to require them.
• Setting the SSOProperties, SSOLoginUrl, and TokenUrl connection properties allows you to authenticate to an Identity Provider, allowing you to use SSO (single sign-on).

Step 2: Configure the TDS Remoting Service

TDS Remoting Service is a Daemon process that monitors Client TDS Requests.

Follow the steps below to set up remoting services to proxy MySQL and SQL Server commands to an ODBC Driver after installing the SQL Gateway on the machine where your DSN is installed:

Step 2.1 Creating Remoting Services

Because the SQL Gateway connects to ODBC Drivers via System DSNs, you’ll need Administrator Access to create new remoting services. Below are the steps for creating a new service.

• Step 1: Select the Run as a Windows Service Box on the Status tab if you want the services to run as Windows Services.
• Step 2: Select the Services tab, then Add and give the service a Name.

• Step 3: Choose SQL Server or MySQL as your database type.
• Step 4: In the Data Source menu, pick a system DSN to connect to. Specify Authentication and other values in a DSN by consulting the help documentation for your data source.
• Step 5: Allow SSL to be used to secure Client Connections. On the Other tab, you’ll also have to set up a Digital Certificate.
• Step 6: Select the Service’s Local Port. If you want to connect across the firewall using reverse SSH tunneling, select “Expose on Gateway.”
• Step 7: Select a Startup option and any logging options.

Step 2.3 Creating Users

To create and configure a new user, go to the Users tab and click Add. Permissions should be applied to each service that is created. Alternatively, choose the Administrator option to give the user full access to all services.

Step 2.4 Connecting Through an SSH Tunnel

If you want to connect to the SQL Gateway using reverse SSH tunneling, go to the SSH Tunnel tab and configure the SQL Gateway to connect to your hosted SSH Server.

Once the services are up and running, you can use MySQL or TDS remoting to connect to your ODBC Data Sources. Simply point your tool or application to the server that is running the app (or the SSH server if you are using the Gateway).

Step 3: Create a Linked Server for Salesforce Data

Create the Linked Server and connect Salesforce to SQL Server after you’ve configured and started the daemon. You can use SQL Server Management Studio’s user interface or call Stored Procedures.

Step 3.1: Create a Linked Server from the UI

Create a Linked Server using the Object Explorer by following the steps below.

• Step 1: Connect to a SQL Server Instance using SQL Server Management Studio.
• Step 2: Expand the SQL Server Database node in the Object Explorer. Right-click Linked Servers in the Server Objects node and select New Linked Server. The dialogue for creating a New Linked Server appears.
• Step 3: After naming the linked server, click the Other Data Source option in the General section and enter the following information.
  • Provider: Choose the SQL Server Native Client Provider that matches your SQL Server version. SQL Server Native Client 11.0, for example.
  • Data Source: Separate the host and port where the TDS remoting service is running with a comma. Note that a value of “localhost” in this input refers to the machine on which SQL Server is running, so be cautious when creating a linked server in Management Studio if you are not running SQL Server on the same machine.
  • Catalog: CData Salesforce Sys is the DSN for the CData system.

• Step 4: Select the option to “make the connection using this security context” in the Security section, and then enter the Username and Password for a user you created in the SQL Gateway’s Users tab.

Step 3.2: Create a Linked Server Programmatically

You can use stored procedures in addition to the SQL Server Management Studio UI to create a linked server. The following data must be provided:

• server: The name of the linked server.
• provider: For the SQL Server Native Client Provider, type “SQLNCLI.”
• datasrc: A comma separates the host and port on which the service is running.

When creating a linked server in Management Studio, keep in mind that a value of “localhost” in the datasrc input refers to the machine where SQL Server is running, so be careful when not running on the same machine as SQL Server.

• catalog: Enter the service’s system DSN.
• srvproduct: Enter the Data Source’s Product name; this can be anything from “CData SQL Gateway” to an empty string.

To create the linked server and configure SQL Gateway Authentication, follow the steps below:

• Step 1: To create a linked server, use the sp addlinkedserver command:

EXEC sp_addlinkedserver @server='Salesforce',
@provider='SQLNCLI',
@datasrc='< MachineIPAddress >,1434',
@catalog='CData Salesforce Sys',
@srvproduct='';
GO

• Step 2: To allow SQL Server users to connect with the credentials of an authorized user of the service, use the sp addlinkedsrvlogin stored procedure. It’s worth noting that the credentials you use to connect to the service must refer to a user you created on the SQL Gateway’s Users tab.

EXEC sp_addlinkedsrvlogin @rmtsrvname='Salesforce',
@rmtuser='admin',
@rmtpassword='test',
@useself='FALSE',
@locallogin=NULL;
GO

Step 4: Connect from SQL Server Management Studio

• SQL Server Management Studio makes use of the SQL Server Client OLE DB provider, which necessitates the in-process use of the ODBC Driver. 
• To query the linked server from SQL Server Management Studio, you must enable the “Allow inprocess” option in the SQL Server Native Client Provider in Management Studio. 
• To do so, go to Server Objects -> Linked Servers -> Providers and open the properties for the provider you’re using. Save the changes after checking the “Allow inprocess” option.

Step 4.1 Execute Queries

Any tool that can connect SQL Server to Salesforce now can run queries on the Salesforce Linked Server. Assign the following table name:

SELECT * FROM [linked server name].[CData Salesforce Sys].[Salesforce].[Account]

Connect SQL Server to Salesforce: Through SSIS

Using SQL Server as a backup for critical business data is a must-have safeguard against data loss. Furthermore, backing up data to SQL Server makes it easier for business users to set up an SQL Server Salesforce connector that data to features like reporting, full-text search, analytics, and more.

This example shows how to use the CData ADO.NET Data Provider for Salesforce to transfer data directly from Salesforce to a Microsoft SQL Server database using a SQL Server SSIS workflow. The procedure outlined below can be used to connect SQL Server to Salesforce directly to remote data via SSIS with any CData ADO.NET Data Provider. Follow the steps given below to execute Salesforce connect SQL Server through SSIS:

• Step 1: Make a new Integration Services Project in Visual Studio.
• Step 2: In the Control Flow screen, drag a New Data Flow Task from the Toolbox.
• Step 3: Add an OLE DB Destination and an ADO.NET Source from the Toolbox to the Data Flow screen.

• Step 4: Select the CData Provider for Salesforce when creating a New Data Connection.
• Step 5: Fill in your Salesforce Account’s Connection information. For this example, you’re going to use a Salesforce Developer Account. User, Password, and Access Token are all required Connection Properties. Also supported is OAuth.

• Step 6: Set the following information in the ADO.NET Source Editor:
  • Connection Manager: Select the Data Connection you just created from the Connection Managers menu.
  • Data Access Mode: Select ‘SQL command’ from the drop-down menu.
  • SQL Command Text: Open the Component Properties tab in the ADO.NET Source editor and type a SELECT command like this:

SELECT * FROM Account

• Step 7: Close the ADO.NET Source editor and connect the ADO.NET Source to the OLE DB Destination by dragging the arrow below it.
• Step 8: In the Destination Component Editor, open the OLE DB Destination and enter the following information.
  • Connection Manager: Create a new link. Here’s where you’ll enter the details for your server and database. SQLExpress is running on a separate machine in this example.
  • Data Access Mode: Select the table or view to populate in your database and change the data access mode to “table or view.”
• Step 9: On the Mappings screen, you can adjust any properties you want.

• Step 10: Run the project after closing the OLE DB Destination Editor. Your database will be populated with data obtained from Salesforce once the SSIS Task has been completed.

Drawbacks of using Linked Server in SQL Server.

1. Performance Overhead: While the Linked Server feature simplifies the process of joining tables between two or more different servers, it introduces a performance overhead. This is particularly noticeable when joining three to five fairly large remote tables, which might result in locking, blocking, and significantly increased runtime.
2. Challenges with Remote Queries: Running queries against a remote server, especially one that is not healthy, can lead to difficulties in improving query performance. A significant portion of the query execution cost (up to 93% in the example provided) can be attributed to remote queries, making it hard to optimize the query with such an execution plan.

Before wrapping up, let’s cover some basics.

Conclusion

This article explains how to Connect SQL Server to Salesforce using various methods. It also gives an overview of Salesforce and SQL Server.

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations, with a few clicks.

FAQ on SQL Server to Salesforce