HTTP Authentication

The SaveToDB add-in enables connections to protected web resources.

All versions support the following authentication schemes: Basic, Forms, and Windows.

SaveToDB 10 also adds support for OAuth1 and OAuth2.

When a user connects for the first time, the add-in displays a connection wizard for authentication.

Basic, Forms, and Windows schemes are straightforward; users can easily enter their username and password.

In contrast, OAuth1 and OAuth2 require additional information, including registered client application credentials and authorization URLs.

Database developers can configure this information using the Authentication event type in the xls.handlers table.

For example:

Use * to apply the connection string to all objects in the schema (as shown with ZohoCRM in the example).

To obtain a connection string, connect to the target service using the SaveToDB connection wizard on your machine.

Click the Open Cache... button in the wizard, then copy and paste the connection string.

The connection string will look like this:

auth_method=OAuth2;
tested_url=https://www.zohoapis.com/crm/v2/Accounts;
service_url=https://www.zohoapis.com/crm/v2/;
client_id=1000...;
client_secret=AppEncrypted@...;
redirect_uri=https://www.savetodb.com/redirect/;
scope=ZohoCRM.modules.ALL,ZohoCRM.settings.READ;
authorization_url=https://accounts.zoho.com/oauth/v2/auth;
get_token_url={accounts-server}/oauth/v2/token;
refresh_token_url={get_token_url};
revoke_token_url={get_token_url}/revoke;
authorization_url_format="{authorization_url}?scope={scope}&client_id={client_id}&response_type=code&access_type=offline&redirect_uri={redirect_uri}";
get_token_url_format={get_token_url};
get_token_body_format="grant_type=authorization_code&client_id={client_id}&client_secret={client_secret}&redirect_uri={redirect_uri}&code={code}";
refresh_token_url_format={refresh_token_url};
refresh_token_body_format="refresh_token={refresh_token}&client_id={client_id}&client_secret={client_secret}&grant_type=refresh_token";
revoke_token_url_format="{revoke_token_url}?token={refresh_token}";
location=us;
accounts-server=https://accounts.zoho.com;
refresh_token=UsrEncrypted@...;
api_domain=https://www.zohoapis.com;
token_type=Bearer;
last_update=2021-06-23T18:18:45.698Z;
name=ZohoCRM;
cached=True;
access_token=UsrEncrypted@...;
expires_in=3600;
valid_to=2021-06-23T19:18:41.610Z

Note that the connection string contains encrypted sensitive data, including client_secret, refresh_token, and access_token.

The client_secret is encrypted at the application level and is accessible on all machines.

The refresh_token and access_token are encrypted by Windows at the user level and are only available under the user's account on that machine.

You can omit the token fields, as users must acquire new tokens themselves.

Remove all parts starting from the location field in the example above, except for the name and cached properties.

The name property specifies the authentication provider name displayed in the connection wizard and used in the connection cache file.

The cached property allows the connection string to be saved in the connection cache file, making it available across all workbooks.

If you disable this property, the add-in will save the connection string in the active workbook only, requiring the user to reconnect with any other workbook.