Connect SQL Server to Salesforce: 3 Easy Methods

As data continues to flood industries, one finds Microsoft SQL Server placed right at the center of it. The tool supports various applications, including business intelligence, analytics, and transaction processing operations. However, most analysts believe there are tons of other advanced applications waiting to be explored. One such interesting conjunction is connecting SQL Server with the Salesforce platform.

This article discusses the various methods to connect SQL servers to Salesforce in detail. Additionally, it provides brief descriptions of SQL Server and Salesforce. But first, here are the prerequisites for connecting SQL Server to Salesforce.

Prerequisites for Connecting SQL Server to Salesforce

In order to establish a connection between a SQL server and the Salesforce platform, organizations must meet a set of technical, security, and operational requirements. These requirements remain consistent across relational databases, cloud warehouses, and even NoSQL systems, although the mechanics vary with each technology. 

Here’s what you need to connect a SQL Server to Salesforce: 

1. Database Access & Connectivity Requirements

Regardless of whether the database is hosted on-premise or in the cloud, the very basic requirement is access to the source database itself. Once integrated, platforms must be able to establish a secure connection. In order to achieve this, administrators must clearly define the following:

• Hostname/IP Address
• Port Number
• Database Name
• User Permissions
• Authentication Credentials

2. Salesforce Access & API Configuration

Most Salesforce integrations rely heavily on APIs, pertaining to which your Salesforce must be configured properly before any data movement can occur.

Typical prerequisites include the following:

• API access enabled for the Salesforce organization
• A dedicated integration user
• Object and field-level permissions
• Authentication configurations

💡Note: Managing API authentication, OAuth configuration, schema mapping, and sync reliability manually can quickly become difficult as integration complexity grows. Platforms like Hevo Data simplify this process with pre-built Salesforce connectors, automated pipeline management, and minimal setup requirements. Try Hevo’s Salesforce integration for free!

3. Integration Platform or Middleware Layer

Most databases can’t connect directly with Salesforce. Instead, they seek an intermediary integration layer that transforms data, manages authentication, orchestrates workflows, handles retries, and maintains sync consistency. Popular tools that serve as integration platforms include names such as Fivetran, Hevo, and Dataddo. 

These integration platforms play a significant role for 

• Realtime integrations that require webhook or event-based processing
• Warehouse-driven setups that rely on reverse ETL capabilities
• Enterprise integrations that often require orchestration and governance layers

And hence are hard to miss. 

4. Network & Security Configuration

One of the key aspects of connecting a SQL database with Salesforce is secure communication between the system and the Salesforce platform. For that to happen, the database environment, integration platform, and Salesforce instance must all be able to communicate securely and reliably.

Typical requirements include the following:

• firewall configuration,
• IP allowlisting,
• SSL/TLS encryption,
• VPN or private network access,
• proxy configuration,
• and secure credential storage.

Meanwhile, organizations with simpler infrastructure requirements may prefer cloud-hosted databases that already provide enterprise-grade security controls and managed connectivity features. 

5. Data Mapping & Schema Planning

Before synchronization begins, organizations must define how data structures in the database correspond to Salesforce objects and fields. This step is critical because databases and Salesforce often use very different schemas.

Typical mapping scenarios involve:

• customer tables mapping to Accounts,
• user records mapping to Leads or Contacts,
• and transaction records mapping to opportunities.

Data mapping usually includes field mappings, primary and foreign key identification, external IDs, and validation rules, among others.

6. Synchronization Strategy & Data Flow Design

For a successful integration to take place, administrators need to have a sound synchronization strategy and a strong data flow design in place. It is here where organizations need to determine whether synchronization is one-way or bidirectional, whether updates should occur in real time or in scheduled batches, and how conflicts should be resolved when systems contain inconsistent data.

For larger environments, incremental sync or CDC-based architectures are typically preferred because they reduce API consumption and improve scalability.

Some organizations also implement event-driven pipelines or message queues to support near real-time synchronization.

It is about time administrators realize managing real-time synchronization, schema evolution, retries, and large-scale data movement manually can quickly become operationally complex. Platforms like Hevo Data simplify Salesforce data synchronization with automated pipelines, built-in monitoring, and near-real-time data replication capabilities. 

See Hevo in action right now! Schedule a Demo

7. API Limits, Scalability & Performance Planning

Similar to most modern data tools, Salesforce enforces API usage and processing limits, which makes performance planning an important part of any database integration project. Without proper optimization, integrations can encounter issues like throttling, failed sync jobs, delayed updates, and incomplete data transfers.

However, organizations can avoid such problems by accounting for several technical considerations during implementation. These commonly include:

• daily API request quotas,
• batch size limitations,
• retry handling mechanisms,
• asynchronous execution,
• and support for large-volume data processing.

This is where performance planning becomes critical. Integrating enterprise-scale databases containing millions of records is not as easy as it may sound. If ignored, inefficient synchronization logic can create bottlenecks, increase infrastructure overhead, and negatively impact long-term integration stability.

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:

• Method 1: Connect SQL Server to Salesforce with an Import and Export Wizard
• Method 2: Connect SQL Server to Salesforce with a ODBC driver
• Method 3: Connect SQL Server to Salesforce through SSIS 

Method 1: Connect SQL Server to Salesforce with an Import and Export Wizard

• 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 enables you to import data from other SQL Server databases, Oracle databases, flat files (such as CSV), Access data sources, PostgreSQL databases, MySQL databases, 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 is located in the “Platform Tools” section of the setup. Follow the steps below to connect SQL Server to Salesforce using the import and export wizards:

Follow the steps below to connect SQL Server to Salesforce using the import and export wizards:

Step 1: Select “Data Loader” from the Data Management menu.

• Step 2: First, download and install the appropriate version of Data Loader. Then, log in to 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 note that this method of data integration is one-way only. It is not possible to migrate data backwards.

For more information about the SQL Server Import Wizard, refer to the Microsoft documentation.

Limitations: 

• Export scheduling does not guarantee complete 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 must be preserved to prevent incomplete restores or data corruption.
• There is no easy way to compare lost or modified data, and restoring records with object relationships can be particularly challenging.
• Backup files are often stored on laptops or PCs, creating potential IT security risks.

Method 2: Connect SQL Server to Salesforce with a Linked Server

To connect SQL Server to Salesforce using ODBC drivers, you can set up a linked server in SQL Server Management Studio (SSMS).

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, enabling 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 in-process” 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]

Method 3: 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.

Finding the Right SQL Server to Salesforce Integration Method for You

Choosing the right SQL Server to Salesforce integration method can be challenging at times. In most cases it usually boils down to technical expertise, scalability, budget, and long-term data strategy requirements that can make or break your project. 

For instance, while some businesses prioritize real-time synchronization and customization, others may prefer simplicity, analytics, or even faster deployment rates. Here’s a table to make things easier for you.

Criteria	Native/API-Based Integration	Third-Party ETL Tool	SSIS-Based Integration
Best For	Real-time application sync and custom integrations	Fast, low-code data replication and analytics	Enterprises already using Microsoft SQL Server ecosystem
Technical Expertise Required	High	Low to Medium	Medium to High
Setup Complexity	High	Low	Medium
Coding Requirement	Usually required	Minimal	Minimal to moderate
Real-Time Sync Support	Strong	Depends on the tool.	Limited
Scalability	High	High	Moderate to High
Maintenance Effort	High	Low	Medium
Customization Flexibility	Very high	Moderate	High
Infrastructure Requirement	Application/API layer required	Cloud-managed	Requires SSIS + SQL Server environment
Monitoring & Error Handling	Custom-built	Built-in dashboards and alerts	Manual or SQL Server-based monitoring
Cost Structure	Development-heavy	Subscription-based	Infrastructure/licensing cost
Performance for Large Data Loads	Good with optimization	Excellent	Good
Best Use Case	Custom business workflows and app integrations	Data warehousing, BI, analytics, replication	Internal Microsoft-centric ETL pipelines
Time to Implement	Slow	Fast	Moderate
Recommended For	Engineering-led teams	Business and data teams	Enterprises using Microsoft stack extensively

You can also learn more about:

• IBM DB2 to SQL Server
• Elasticsearch to SQL Server
• Ftp SQL Server Integration
• Jira to SQL Server

Conclusion

In this article, we have explained 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.

Sign up for a 14-day free trial with Hevo and streamline your data integration. Also, check out Hevo’s pricing page for a better understanding of the plans.

FAQ on SQL Server to Salesforce