How to authenticate Salesforce through CData ODBC driver via OAuth?

Question

I have installed CData ODBC driver for Salesforce and able to connect Salesforce through username, password and security token. I wanted to access through OAuth as well. I have followed all the steps mentioned in the link below. How can we go further.

http://cdn.cdata.com/help/RFE/odbc/pg_oauth.htm

Here is my code to connect Salesforce through username, password and Security Token and able to save the data in a csv file. How to do the same through OAuth?

import pyodbc
import csv
cnxn = pyodbc.connect("DRIVER={CData ODBC Driver for Salesforce};User=yourusername;Password=password;Security Token=security token;")
cursor = cnxn.cursor()
query = "SELECT * from AccountPartner"
cursor.execute(query)
csvfile=open('persons.csv','w', newline='')
obj=csv.writer(csvfile)
for row in cursor:
    print(row)
    obj.writerow(row)
csvfile.close()

Answer 1

The instructions you need are further down in the documentation (also copied below): http://cdn.cdata.com/help/RFE/odbc/pg_oauthcustomappcreate.htm

You'll need to create the custom app in Salesforce and then set the prescribed OAuth-related connection properties in your connection string. Your code to create the connection will look like the following:

cnxn = pyodbc.connect("DRIVER={CData ODBC Driver for Salesforce};OAuthClientID=MY_CONSUMER_KEY;OAuthClientSecret=MY_CONSUMER_SECRET;OAuthCallbackURL=https://localhost:33333;InitiateOAuth=GETANDREFRESH;")

You may need to test the connection and trigger the OAuth flow outside of Python first. The instructions for that process can be found in the Unix ODBC section of the Help (http://cdn.cdata.com/help/RFE/odbc/pg_unixODBConlinux.htm) but are also copied below.

---

Instructions Copied from Documentation

Create a Connected App

To obtain the OAuth client credentials, consumer key, and consumer secret:

1. Log in to Salesforce.com.
2. From Setup, enter Apps in the Quick Find box and then click the link to create an app.
3. In the Connected Apps section of the resulting page, click New. Enter a name to be displayed to users when they log in to grant permissions to your app, along with a contact Email address.
4. Click Enable OAuth Settings and enter a value in the Callback URL box. If you are making a desktop application, set the Callback URL to http://localhost:33333 or a different port number of your choice. If you are making a web application, set the Callback URL to a page on your Web app you want the user to be returned to after they have authorized your application.
5. Select the scope of permissions that your app should request from the user.
6. Click your app name to open a page with information about your app. The OAuth client credentials, the consumer key, and consumer secret are displayed.

Authenticate to Salesforce from a Desktop Application

After setting the following connection properties, you are ready to connect:

• OAuthClientId: Set to the consumer key in your app settings.
• OAuthClientSecret: Set to the consumer secret in your app settings.
• CallbackURL: Set to the callback URL in your app settings.
• InitiateOAuth: Set to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property.

When you connect, the driver opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The driver then completes the following OAuth process:

1. Gets the callback URL and sets the access token and OAuthServerUrl to authenticate requests.
2. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.
3. Exchanges the returned refresh token for a new, valid access token.

Using unixODBC

Define a DSN

Create a DSN by adding an entry to odbc.ini or .odbc.ini. Below is an example DSN entry:

[CData Salesforce Source]
Driver=/opt/cdata/cdata-odbc-driver-for-salesforce/lib/libsalesforceodbc.x64.so
OAuthClientID=MY_CONSUMER_KEY
OAuthClientSecret=MY_CONSUMER_SECRET
OAuthCallbackURL=https://localhost:33333
InitiateOAuth=GETANDREFRESH

Test the Connection

You can use the unixODBC test tool, isql, to execute SQL queries to Salesforce from the command line. When testing the connection, use the -v flag to output any messages from the driver manager and the driver.

isql -v "CData Salesforce Source"

Answer 2

enter code hereMake the changes in your connection string as below

cnxn = pyodbc.connect("DRIVER={CData ODBC DRIVER for
Salesforce};InitiateOAuth='GETANDREFRESH';OAuthClientId='myclient_id';OAuthClientSecret='my_secret';callbackurl='my_redirect_uri';oauthaccesstoken='my_access_token';oauthserverurl='server_url_returned_in_oauth';OAuthRefreshToken='refresh_token';")