Using HTTP Authentication

gsqlcmd supports the following HTTP authentication methods:

Auto
None
Basic
Windows
Forms
OAuth2
OAuth1
Custom

Use the /auth option to specify the method.

For example, use the None method to get the result with an empty authorization header:

gsqlcmd download https://localhost/crm/contacts /auth=None

The default method is Auto.

Use the /interactive option to allow authorizing the request in an interactive mode.

For example:

gsqlcmd download https://localhost/crm/contacts /interactive

You can use the /options, /clientJson, /serviceJson, and /tokenJson options to load options from JSON or plain text files.

For example, a command with the OAuth2 authentication looks like

gsqlcmd download https://localhost/crm/contacts /clientJson=client.json /serviceJson=service.txt /tokenJson=token.json

You can place all options in a single file. For example:

gsqlcmd download https://localhost/crm/contacts /options=options.txt

However, the first method allows separating client and service options and updating the token.json file when the token is refreshed.

Below you will find tips for specific authentication methods.

Basic Authentication

The "Basic" authentication method requires a username and a password.

For example:

gsqlcmd download https://localhost/crm/contacts /auth=basic /username=user /password=pass

You can omit the /auth=basic option as gsqlcmd detects the required "Basic" authentication scheme automatically.

Windows Authentication

You can use the Windows authentication method with the default Windows user credentials or the specified username and password.

For example:

gsqlcmd download https://localhost/crm/contacts /auth=windows

gsqlcmd download https://localhost/crm/contacts /auth=windows /username=user /password=pass

You can specify a domain with a username. For example:

gsqlcmd download https://localhost/crm/contacts /auth=windows /username=domain/user /password=pass

You can omit the /auth=windows option as gsqlcmd detects the Windows authentication scheme automatically.

Forms Authentication

You can use a username and a password if a service supports the Forms script authentication.

For example:

gsqlcmd download https://localhost/crm/contacts /auth=forms /username=user /password=pass

Otherwise, you can use the interactive mode as shown above or specify the authorization cookie.

For example:

gsqlcmd download https://localhost/crm/contacts /auth=forms /cookie=.ASPXFORMSAUTH=D3A...

You can find such cookies in browser developer tools.

The better option is to place the cookie in a file and use the /cookieFile option.

In this case, gsqlcmd updates the refreshed cookie in the file.

For example:

gsqlcmd download https://localhost/crm/contacts /auth=forms /cookieFile=cookie.txt

You can use the /cookieFile option with any Forms authentication mode to save the acquired cookie.

You can omit the /auth=forms option as gsqlcmd detects the Forms authentication scheme in most cases successfully.

When a username and a password are specified, gsqlcmd detects the authorization URL. In some cases, it cannot detect it correctly.

In this case, specify the service root URL in the /authorizationUrl option.

For example:

gsqlcmd download https://localhost/crm/contacts /auth=forms /username=user /password=pass /authorizationUrl=https://localhost/crm/

OAuth2 Authentication

If you have an access token, you can use a simple form like this

gsqlcmd download https://localhost/crm/contacts /auth=oauth2 /tokenType=Bearer /accessToken=token

This form is equivalent to

gsqlcmd download https://localhost/crm/contacts "/header=Authorization: Bearer token"

If you have to acquire the token first and refresh it later, you must configure the OAuth2 method options.

gsqlcmd has enough options to configure any specific requirements.

We recommend split options into three files and use them in any command with the OAuth2 authentication like

gsqlcmd ... /clientJson=client.json /serviceJson=service.json /tokenJson=token.json ...

You can use the following gsqlcmd modes with the OAuth2 authentication:

download, convert, or import
get-token
refresh-token
revoke-token

Use the get-token mode first to acquire an access token. Alternatively, use the /interactive option in the download, convert, or import mode.

Use the refresh-token mode to refresh the token explicitly. Usually, this action is not required as gsqlcmd refreshes tokens when needed automatically.

Use the revoke-token mode to revoke the token.

Zoho CRM Configuration Sample

Below is a comprehensive sample of configuration files for Zoho CRM.

Here is a suggested content of the service.txt file:

authorization_url_format={authorization_url}?scope={scope}&client_id={client_id}&response_type=code&access_type=offline&redirect_uri={redirect_uri}
authorization_url=https://accounts.zoho.com/oauth/v2/auth

get_token_url_format={accounts-server}/oauth/v2/token
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={accounts-server}/oauth/v2/token?refresh_token={refresh_token}&client_id={client_id}&client_secret={client_secret}&grant_type=refresh_token
refresh_token_body_format=

revoke_token_url_format={accounts-server}/oauth/v2/token/revoke?token={refresh_token}
revoke_token_body_format=

The file contains gsqlcmd options like /authorization_url_format, /authorization_url, and so on.

The file contains URL and body formats for every operation like get-token, refresh-token, and revoke-token. Also, it includes options to acquire the initial authorization.

The formats contain variables in curly brackets of other gsqlcmd options specified inline or loaded from files.

You can find these values in the service documentation, like https://www.zoho.com/crm/developer/docs/api/v2/oauth-overview.html for Zoho CRM.

Here is a suggested content of the client.txt file:

client_id=1000...
client_secret=93a...
redirect_uri=https://www...
scope=ZohoCRM.modules.ALL

The formats of the service.txt file use values from this file.

You acquire such values when registering an application.

For example, read this https://www.zoho.com/crm/developer/docs/api/v2/register-client.html for Zoho CRM.

Choose a mobile application type when registering an application at Zoho CRM.

When you configured the client and service options, acquire a token using a command like this

gsqlcmd get-token /clientJson=client.txt /serviceJson=service.txt /tokenJson=token.json

gsqlcmd will open a browser to authorize the application and then acquire the access and refresh tokens.

It will save the token to the token.json file that looks like the following for Zoho CRM:

{
    "location":"us",
    "accounts-server":"https://accounts.zoho.com",
    "valid_to":"2021-03-10T00:36:15.850Z",
    "access_token":"1000.ee9764918066eb3a9ee2b58c00ed4737.28c820e4ef4400000000000000000741",
    "refresh_token":"1000.cedae4432cd94039e2a402bf29bc293b.17906477b8c900000000000000000071",
    "api_domain":"https://www.zohoapis.com",
    "token_type":"Bearer",
    "expires_in":"3600"
}

You can see values suitable for built-in gsqlcmd options like /tokenType and /accessToken discussed above.

Also, you can see the service-specific values like location, accounts-server, and api_domain (that also used in service.txt).

gsqlcmd detects such values automatically and saves them in the token file.

You can define the field list explicitly using the /tokenFields option.

When you acquire the token, you can download data using a command like this

gsqlcmd download https://www.zohoapis.com/crm/v2/Contacts contacts.json /clientJson=client.txt /serviceJson=service.txt /tokenJson=token.json

Or, you can convert data using a command like this

gsqlcmd convert "SELECT Email, First_Name AS FirstName, LastName AS LastName FROM https://www.zohoapis.com/crm/v2/Contacts" contacts.csv ^
    /clientJson=client.txt /serviceJson=service.txt /tokenJson=token.json

Google Search Console Configuration Sample

Below is a simpler sample of configuration files for Google Search Console.

Create an OAuth client ID of the desktop app type at https://console.cloud.google.com/apis/credentials/oauthclient.

Save the client credentials to JSON using the "DOWNLOAD JSON" link and rename it to client.json.

The file content looks like this

{"installed":{
    "client_id":"586418743564-9p0vq6cq8nmjmhgucsm0000000000000.apps.googleusercontent.com",
    "project_id":"searchconsoleapi-000000",
    "auth_uri":"https://accounts.google.com/o/oauth2/auth",
    "token_uri":"https://oauth2.googleapis.com/token",
    "auth_provider_x509_cert_url":"https://www.googleapis.com/oauth2/v1/certs",
    "client_secret":"Aii500000000000000000FCB",
    "redirect_uris":["urn:ietf:wg:oauth:2.0:oob","https://localhost"]
}}

The file contains a complete configuration of the service, including the client application credentials.

gsqlcmd uses the token_uri as a default value for the /getTokenUrl and /requestTokenUrl options.

Also, it uses the standard OAuth2 URL and body formats that are enough for Google.

So, you can get a token using the command:

gsqlcmd get-token /clientJson=client.json /tokenJson=token.json

Then, use any query to download, convert, or import data from the Google Search Console using a command like this

gsqlcmd ... /clientJson=client.json /tokenJson=token.json

See details at https://developers.google.com/webmaster-tools/v1/searchanalytics/query.

OAuth1 Authentication

To authorize the request with the two-ledged OAuth1 scheme, specify the /consumerKey and /consumerSecret options.

For example:

gsqlcmd download https://localhost/crm/contacts /consumerKey=app /consumerSecret=appSecret

The /auth=OAuth1 parameter is optional.

To authorize requests with the three-ledged OAuth1 scheme, you have to acquire the access token first.

You can create a client.txt file with the following contents and fill the values according to the service documentation and your registered application.

consumer_key=
consumer_secret=
callback=
request_token_url=
authorization_url=
access_token_url=

Then, acquire the access token using the command:

gsqlcmd get-token /clientJson=client.txt /tokenJson=token.json

Then, use any query to download, convert, or import data from the service using a command like this

gsqlcmd ... /clientJson=client.json /tokenJson=token.json

Custom Authentication

You can use the custom authentification method to authenticate requests using URL parameters, authorization headers, and cookies.

For example:

gsqlcmd download https://localhost/crm/contacts /urlParameters=api_key=key "/authorizationHeader=Bearer token" /cookieFile=cookie.txt

This method can be easier for several services.

For example, https://data.nasdaq.com/ publishes a lot of data for free. It requires an API key in the URL.