Search code examples
apioauth-2.0salesforcepyodbccdata-drivers

How to authenticate Salesforce through CData ODBC driver via OAuth's accessToken?

I am only able to fetch salesforce data from username and password as below.

cnxn = pyodbc.connect("DRIVER={CData ODBC Driver for Salesforce};User=myUser;Password=myPassword;Security Token=myToken;")

But If I don't want to give username and password there and want to access data with Accesstoken which is returned from Oauth. How can I do that?

Solution

  • The instructions for connecting to Salesforce using OAuth with the CData ODBC Driver are here: http://cdn.cdata.com/help/RFE/odbc/pg_oauthcustomappcreate.htm (copied below).

    In short, you'll use the OAuth Access Token and OAuth Server URL your code will look similar to the following

    cnxn = pyodbc.connect("DRIVER={CData ODBC Driver for Salesforce};OAuthAccessToken=OAUTH_ACCESS_TOKEN;OAuthServerUrl=OAUTH_SERVER_URL;")

    Authenticate to Salesforce from a Web Application

    To obtain the access token, set the following connection properties:

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

    When connecting via a web application, or if the driver is not authorized to open a browser window, you need to exchange temporary verification values for the access token:

    1. Call GetOAuthAuthorizationUrl. The stored procedure returns the URL to the OAuth endpoint.
    2. Log in and authorize the application. You are redirected back to the callback URL. If you set the GrantType parameter to Implicit, the callback URL contains the OAuthAccessToken and OAuthServerUrl in a query string parameter. If you set the GrantType parameter to code, the callback URL contains the verifier code in the query string parameter named "code". Extract the verifier code and call GetOAuthAccessToken.

    The relevant part:

    To connect to data, set the following connection properties:

    • OAuthAccessToken
    • OAuthServerUrl

    To automatically refresh the access token when it expires, set InitiateOAuth to REFRESH and set OAuthRefreshToken. Alternatively, call the RefreshOAuthAccessToken stored procedure when the access token expires. Given a refresh token as input, the procedure returns a valid OAuth access token.

    As an alternative to retrieving the authorization URL and having the user log in to Salesforce, you can set up a password grant type by calling GetOAuthAccessToken, setting GrantType to PASSWORD. Here, you need to ensure that the user name and password are both set in the connection string, in addition to the client ID and secret of your application. Note that InitiateOAuth must be set to OFF for the password grant type to work. You cannot refresh the token obtained this way. This method has the advantage of removing the login step for users that cannot open a web browser, but it has the disadvantage of the user's credentials being exchanged in plain text between the server and Salesforce.

    Note: You can configure the session timeout in Salesforce by navigating to Setup > Administration Setup > Security Controls > Session Settings.