Any Suggestions? Problem Getting linked servers configured on SQL Server 2008 R2 64 bit, in order to query SalesForce - Forum - DataDirect Cloud - Progress Community

Any Suggestions? Problem Getting linked servers configured on SQL Server 2008 R2 64 bit, in order to query SalesForce

 Forum

Any Suggestions? Problem Getting linked servers configured on SQL Server 2008 R2 64 bit, in order to query SalesForce

  • I am posting on behalf of one of our Cloud Beta users:


    I’m working on processes that will be able to import data from and export data to the cloud.  These processes will run on SQL Server and will be executed as jobs on a regular schedule by the SQL Server Agent.  I watched the prerequisite videos, then spent several days reviewing forums and reading documentation for some of you other, similar products.  I was able to get linked servers to my both my Sandbox and live instances functioning and will soon be working on some tests with your bulk load option.

    I made notes as I worked on getting the linked servers functioning.  These are not complete in a couple of areas, particularly in the options on security but there is enough information here for users to get up and running.

  • Clifford Aguero wrote:
    How to configure DataDirect Cloud ODBC driver with Linked Server in SQL Server 2008 R2 64 bit
    1. Create a data source on the DataDirect Cloud
          Review the video provided by Progress Software Corporation
    2. Create an ODBC System DSN on your server
          a. Install the DataDirect Cloud ODBC Driver, 64 bit version
                - View the Readme file that comes with the installation, note the virtual directory
                requirement at the bottom of the file, this directory must be created before the driver
                will work properly.
          b. Open the ODBC Administrator
                - The proper ODBC Administrator for the bitness of the driver will now appear in a new Program
                Group under the Start Menu.  The group name will be Progress DataDirect Cloud (etc.), the
                newly installed driver should show in the list of available ODBC drivers.
          c. Click the System DSN tab
          d. Click the Add button
          e. Choose the DataDirect Cloud driver from the list
          f. Configure the following tabs in the ODBC Cloud Driver Setup window:
                General
                      - Data Source Name: Give it a name you will find easy to identify
                      - Description: Something to remind you what it's for
                      - Database Name: This is the name given to data source on the DataDirect Cloud
                Advanced
                      - Verify that Host Name = service.datadirectcloud.com
                      - Verify that Port = 443
                Security
                      - Authentication
                            User Name: This is the name of the account on the DataDirect Cloud
                            Logon Domain: Leave this blank
                      - Data Source Authentication
                            Data Source User: This is the user name on the SalesForce Cloud
                            Data Source Password: This is the user password on the SalesForce Cloud,
                                  do not include your security token here
          g. Click the Test Connect button at the bottom of the ODBC Cloud Driver Setup window
                In the Logon To Cloud dialog box
                      User Name: This should already be populated
                      Password: Type the password for the user on the DataDirect Cloud
                Press OK and wait for "Connection Established!" message
    3. Create a Linked Server
    These steps to be performed in MS SQL Server Management Studio. If you prefer you can write an
    equivalent sp_addlinkedserver call in T-SQL.
          a. Open the Server Objects node
          b. Right-click the Linked Servers node and choose New Linked Server from the drop-down list
                In the New Linked Server window, fill out the following pages:
                      - General
                            Linked Server: Give the linked server an appropriate name
                            Server Type: Other data source
                            Provider: Microsoft OLE DB Provider for ODBC Drivers
                            Product Name: "DataDirect Cloud Beta"
                            Data Source: This is the name you gave to the System DSN in the ODBC Administrator
                            Provider String: N/A
                            Location: N/A
                            Catalog: N/A
                      - Security
                            Users can be mapped specifically to remote login credentials
                            or
                            For a login not defined in the list above, check the radio button labeled
                            “Be made using this security context”
                                  Remote login: This is the user name on the DataDirect Cloud
                                  With password: This is the password on the DataDirect Cloud
    c. Click OK
          d. Once the spinning green wheel vanishes your new linked server should appear under
          the Linked Servers node

    Note: Assuming security is set up properly on the SQL Server, credentials will be passed properly to the DataDirect Cloud even when code is executed autononmously by the SQL Server Agent.  It's easiest to quickly verify the linked server is functioning by running a simple OpenQuery via Query Analyzer.