Use the TDS Remoting feature of the ODBC Driver to set up a linked server for Salesforce data.
You can use the TDS Remoting feature to set up a linked server for Salesforce data. After you have started the daemon, you can use the UI in SQL Server Management Studio or call stored procedures to create the linked server. You can then work with Salesforce data just as you would a a linked SQL Server instance.
Here's how to configure the link.
Configure the DSN
If you have not already done so, specify connection properties in a DSN (data source name). You can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs. This is the last step of the driver installation. See the "Getting Started" chapter in the help documentation for a guide to setting the required properties in the Microsoft ODBC Data Source Administrator.
Configure the TDS Daemon
The TDS Remoting feature of the ODBC driver enables you to create a linked server for Salesforce. The ODBC driver runs a daemon as a service that listens for TDS requests from clients. The daemon can be configured in a configuration settings file and through the CLI (command-line interface). Follow the steps below to use the configuration settings file to configure the DSN, SSL, access control, and other settings:
- Open the CData.ODBC.Salesforce.Remoting.ini file, located in the remoting subfolder in the installation directory.
In the tdsd section, configure the settings for the TDS server:
Note: By default, the daemon runs on port 1433, the default SQL Server port. If you already have SQL Server running on port 1433, change the default value for the port.
In the databases section, define the catalog name and set it to the DSN:
In the acl section, add users that are allowed to connect to the linked server:
In the users section, define passwords for authorized users. Below are the default values:
- Start the service the daemon is running under. You can start the service from the Services Snap-In: Click Start -> Run and enter services.msc. Right-click the CData Salesforce TDS Remoting service and click Start.
Create a Linked Server for Salesforce Data
After you have configured and started the daemon, create the linked server and connect. You can use the UI in SQL Server Management Studio or call stored procedures.
Create a Linked Server from the UI
Follow the steps below to create a linked server from the Object Explorer.
- Open SQL Server Management Studio and connect to an instance of SQL Server.
- In the Object Explorer, expand the node for the SQL Server database. In the Server Objects node, right-click Linked Servers and click New Linked Server. The New Linked Server dialog is displayed.
- In the General section, click the Other Data Source option and enter the following information after naming the linked server:
- Provider: Select SQL Server Native Client 10.0 in the menu.
- Product Name: Enter a name for the data source.
- Data Source: Enter the host and port the daemon is running on.
- Provider String: Enter the following connection string:
- Catalog: Enter the catalog you defined in the databases section of the configuration settings file.
- In the Security section, select the Be Made Using this Security Context option and enter the username and password of a user you authorized in the acl section of the configuration settings file.
Create a Linked Server Programmatically
In addition to using the SQL Server Management Studio UI to create a linked server, you can use stored procedures:
Call sp_addlinkedserver to create the linked server:
Call sp_droplinkedsvrlogin to remove the default mappings created by sp_addlinkedserver. By default, sp_addlinkedserver maps all local logins to the linked server.
Call the sp_addlinkedsrvlogin stored procedure to allow SQL Server users to connect with the credentials of an authorized user of the daemon. Note that the credentials you use to connect to the daemon must exist in the daemon's configuration settings file.
Connect from SQL Server Management Studio
SQL Server Management Studio uses the SQL Server Client OLE DB provider which requires the ODBC driver to be used inprocess. You must enable the 'Allow inprocess' option for the SQL Server Client Provider in Management Studio to query the linked server from SQL Server Management Studio. To do this, open the properties for the provider you are using under Server Objects -> Linked Servers -> Providers. Check the 'allow inprocess' option and save the changes.
You can now execute queries to the Salesforce linked server from any tool that can connect to SQL Server. Set the table name accordingly: