HTTP Authentication

The SaveToDB add-in allows connecting to protected web resources.

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

SaveToDB 10 additionally supports OAuth1 and OAuth2.

When a user connects for the first time, the add-in shows the connection wizard to authenticate a user.

The Basic, Forms, and Windows schemes are quite simple, and a user can enter a username and password easily.

OAuth1 and OAuth2 require more data: registered client application credentials and authorization URLs and formats.

Database developers can configure these data using the Authentication event type in the xls.handlers table.

For example:

Use * to apply the connection string to all objects in the schema (like ZohoCRM in the sample).

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

Then click the Open Cache... button in the wizard, and copy and paste the connection string.

The connection string looks 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

First of all, the connection string has encrypted sensitive data like client_secret, refresh_token, and access_token.

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

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

Anyway, you can remove the token fields as users must acquire new tokens themselves.

You can remove all parts starting the location field in the sample above except for the name and cached properties.

The name property defines an authentication provider name shown in the connection wizard and used in the connection cache file.

The cached property allows saving the connection string in the connection cache file and sharing it across all workbooks.

If you disable this property, the add-in will save the connection string in the active workbook only, and a user has to connect with another workbook once again.