9 Steps to Integrate Your External Data Using Linked Server

Pulling in all your cloud data sources to SQL server is easy as 1-2-3 with Link Server and DataDirect Cloud connectivity.

People use linked servers all the time. When you use SQL server, a linked server is a powerful tool to bring today’s increasing number of external data sources into your SQL server.

1-2-3 Cloud Connectivity

This tutorial shows you specifically how to easily pull cloud data into your SQL server using Linked Server and Progress® DataDirect Cloud®. This includes HubSpot, Marketo, Salesforce and Oracle Service Cloud.

If you want to read more about this subject, you should check out my colleague Sumit Sarkar’s blog post: SQL Server Linked Server ODBC connection to Salesforce from Management Studio.

Step 1

Log in to DataDirect Cloud and ensure that you have the Salesforce data source created. (Note that the Data Source Name field will be important to remember later.)

Step 2

Go to the Downloads link (on the left of the DataDirect Cloud website) and click on the Windows x64 ODBC Driver. This should match the architecture of the SQL Server Management Studio. If the SQL Server Management Studio is 32-bit, you will need to install the Windows x86 DataDirect Cloud ODBC Driver.

Step 3

Open the ODBC Administrator. Configure a DataDirect Cloud 2.0 connection by clicking on the System DSN tab. Click Add and then find DataDirect Cloud 2.0 and configure your source:

a. Data Source Name: This is the name you want to identify the source with.
b. Database Name: Salesforce (This is the name of the DataDirect Cloud source we  created in step 1; in this case, mine was Salesforce so the same name is used here.)
c. Optionally, in the Advanced tab, you can expose audit columns by setting Config Options with the following values: “AuditColumns=All;MapSystemColumnNames=0”
d. Proxy Tab: If you need to connect through a proxy server, select the Proxy tab where you can insert the Proxy Host, Proxy Port, Proxy User and Proxy Password. Here's a link for more information on connecting through a proxy server. e. TestConnect: Log in to the DataDirect Cloud service by using your DataDirect Cloud credentials and ensure the connection is established.

Step 4

Launch SQL Server Management Studio.

Step 5

Navigate to Server Objects > Linked Servers > Providers > MSDASQL > and confirm that the Allow inprocess and Non transacted updates options are Enabled.

Step 6

Navigate to Instance > Server Objects > Linked Servers.

Step 7

Right-click and select New Linked Server.

a. Select provider as Microsoft OLE DB Provider for ODBC Drivers.
b. Enter the Product name as DataDirect.
c. Enter the Data Source name created in the preceding step 3 as SalesforceD2C.

Step 8

Click on the Security page and select Be made using this security context. Then enter your DataDirect Cloud username and password.

Step 9

Click on the Server Options tab and ensure that they match the following options shown in the following screen capture.

Data Connectivity to Your Cloud Source

That’s it! You’re all done! Now you’re ready to connect to more data sources than ever before.

Easy cloud connectivity for free

If you’re trying to connect to any or all of these sources, it’s easy using DataDirect Cloud connectivity. Download a free trial today.