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:

1. Open the CData.ODBC.Salesforce.Remoting.ini file, located in the remoting subfolder in the installation directory.

2. In the tdsd section, configure the settings for the TDS server:

   view source

   [tdsd]

   port = 1434

   maxc = 20

   session-timeout = 20

   logfile = SalesforceRemotingLog.txt

   verbosity = 2

   ssl-cert = "CData.ODBC.Salesforce.Remoting.pfx"

   ssl-subject = "*"

   ssl-password = "test"

   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.

3. In the databases section, define the catalog name and set it to the DSN:

   view source

   [databases]

   ;The database settings, default to installed system DSN name, odbc connection string is acceptable also.

   CDataSalesforce = DSN=CData Salesforce Source

4. In the acl section, add users that are allowed to connect to the linked server:

   view source

   [acl]

   CDataSalesforce = admin

5. In the users section, define passwords for authorized users. Below are the default values:

   view source

   [users]

   ;Passwords

   admin = test

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

1. Open SQL Server Management Studio and connect to an instance of SQL Server.
2. 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.
3. 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:
     view source

     Network Library=DBMSSOCN;

   • Catalog: Enter the catalog you defined in the databases section of the configuration settings file.

   

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

1. Call sp_addlinkedserver to create the linked server:

   view source

   EXEC sp_addlinkedserver @server='Salesforce',

   @srvproduct='CData.Salesforce.ODBC.Driver',

   @provider='SQLNCLI10',

   @datasrc='localhost,1434',

   @provstr='Network Library=DBMSSOCN;',

   @catalog='CDataSalesforce';

   GO

2. Call sp_droplinkedsvrlogin to remove the default mappings created by sp_addlinkedserver. By default, sp_addlinkedserver maps all local logins to the linked server.

   view source

   EXEC sp_droplinkedsrvlogin @rmtsrvname='Salesforce',

   @locallogin=NULL;

   GO

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

   view source

   EXEC sp_addlinkedsrvlogin @rmtsrvname='Salesforce',

   @rmtuser='admin',

   @rmtpassword='test',

   @useself='FALSE',

   @locallogin='YOUR-DOMAIN\your-user';

   GO

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.

Execute Queries

You can now execute queries to the Salesforce linked server from any tool that can connect to SQL Server. Set the table name accordingly: