How to Use HTTP Authentication with gsqlcmd

How to Use HTTP Authentication with gsqlcmd

gsqlcmd supports the following HTTP authentication methods:

Use the /auth option to specify the authentication method.

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

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

The default method is Auto.

To authorize the request interactively, use the /interactive option:

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

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

For example, an OAuth2 authentication command looks like this:

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

You can consolidate all options into a single file:

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

However, separating client and service options allows you to update the token.json file when the token is refreshed.

Below are tips for specific authentication methods.

Basic Authentication

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

For example:

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

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

Windows Authentication

You can use Windows authentication with either the default Windows user credentials or 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

To specify a domain with a username, use:

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

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

Forms Authentication

If the service supports Forms authentication, you can use a username and password:

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

Alternatively, use interactive mode as shown above or specify the authorization cookie:

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

You can find such cookies in your browser's developer tools.

A better approach is to save the cookie in a file and use the /cookieFile option. This way, gsqlcmd updates the refreshed cookie in the file:

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 usually detects the Forms authentication scheme successfully.

When you specify a username and password, gsqlcmd detects the authorization URL. However, it may not always do so correctly. In such cases, specify the service root URL using the /authorizationUrl option:

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

OAuth2 Authentication

If you have an access token, use the following format:

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

This is equivalent to:

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

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

We recommend splitting options into three files and using them in any command with OAuth2 authentication:

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

You can use the following gsqlcmd modes with OAuth2 authentication:

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

Start with the get-token mode 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, although gsqlcmd typically handles this automatically.

Use the revoke-token mode to revoke the token.

Zoho CRM Configuration Sample

Here’s a sample configuration for Zoho CRM.

Suggested content for 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=

This file contains gsqlcmd options like /authorization_url_format, /authorization_url, etc. It also includes URL and body formats for operations like get-token, refresh-token, and revoke-token, along with options for acquiring the initial authorization.

The formats use variables in curly brackets that correspond to other gsqlcmd options specified inline or loaded from files. You can find these values in the service documentation, such as https://www.zoho.com/crm/developer/docs/api/v2/oauth-overview.html for Zoho CRM.

Suggested content for the client.txt file:

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

The formats in service.txt use values from this file, which you acquire when registering an application. For more details, refer to https://www.zoho.com/crm/developer/docs/api/v2/register-client.html.

When registering an application at Zoho CRM, choose the mobile application type.

Once you have configured the client and service options, acquire a token with the following command:

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

gsqlcmd will open a browser for application authorization and then acquire the access and refresh tokens. It saves the token to token.json, which looks like this 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 earlier. Additionally, service-specific values like location, accounts-server, and api_domain (also used in service.txt) are included.

gsqlcmd automatically detects these values and saves them in the token file. You can explicitly define the field list using the /tokenFields option.

After acquiring the token, you can download data with 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 convert data using:

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

Here’s 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.

Download the client credentials as JSON and rename the file to client.json. The 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"]
}}

This file contains complete service configuration, including client application credentials. gsqlcmd uses the token_uri as the default value for the /getTokenUrl and /requestTokenUrl options. It also uses standard OAuth2 URL and body formats that are sufficient for Google.

You can get a token with the following command:

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

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

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

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

OAuth1 Authentication

To authorize requests with the two-legged OAuth1 scheme, specify the /consumerKey and /consumerSecret options:

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

The /auth=OAuth1 parameter is optional.

For the three-legged OAuth1 scheme, you must acquire the access token first. Create a client.txt file with the following contents and fill in 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:

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

After that, use any query to download, convert, or import data from the service with a command like this:

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

Custom Authentication

Use the custom authentication 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 simplify authentication for several services. For instance, https://data.nasdaq.com/ provides a lot of free data and requires an API key in the URL.

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.