Remote authentication

The remote function support different methods for logging into different server. Since this is a very important method for using remote to access data in other server, this document will show examples of the how it is done and the different methods supported.

Supported commands used for authentication

@Connect	Nothing	Sets the url that will be used for the login operation. For dataeases and dg3 it will be striped down to onlys server part like http(s)://server.domain/
@IsLoggedIn	String	Will return yes if logged in and no if not
@Login	String	This depend on the login method used. If form based like django and dg3 is used, the page data is returned if there is any. A redirect will not fall trough in django or dg3 when successful login is done, so a blank page might be the result on success and the login page with a error message if not.
@logintype	Nothing	The type of login used. Defaut is dataease, but you can also use anybasic, basic, oauth2, digest, ntlm, negotiate, anybasic, post, dg3 and django. The anybasic method tests all of the basic methods from the strongest to basic and uses the best supported version. The negotiate method is not activated at the moment.
@Logout	Nothing	Clear login information and logs out.
@Password	Nothing	Set password used for login
@UserName	Nothing	Set user name used for login

DataEase server login

define "result" memo .
define "un" Text 60 .
define "pw" Text 60 .
define "url" Text 255 .
define "cookie" Text 255 .
define "desession" Text 100 .
define "LastSession" Text 255 .
un := "UserName".
pw := "Password".
url := "https://server.domain/". 
LastSession := Remote("@current:testuser").
Remote("@cleanup").
Remote("@username",un).
Remote("@password",pw).
Remote("@logintype:dataease").
Remote("@connect",url).
Remote("@login") .
define "idx" Number .
cookie := Remote("@cookieget", "0") .
while cookie not = blank do
  alert(cookie).
  if desession = blank then
    desession := StringFrom(cookie, concat("desessionid", chr(9))) .
    SetVar("desessionid", desession) .
  end
  idx := idx + 1 .
  cookie := Remote("@cookieget", concat("", idx)) .
end
-- alert(desession) .
-- alert(Remote("@isloggedin")).
result := Remote("@getraw", concat(url, "definition/table/Books")).
Remote(LastSession).
list records
  result .

This example use username and password to session login to deserver. The session used on the server is stored in desessionid cookie. This mean if you store the cookie value and use that later, you can reuse the same session as you already are logged into. This example show you how to read the desessionid cookie from the current session and store it in global variable "desessionid". Then it look up the definitions on the table named Books on deserver running on the server given by url var. As long as you do not do a cleanup, you can call the server as many times as you want using the same remote session.

Oauth2 login

This is the login of most modern APIs. It is a pain in the .. to get working, but is is what is used out the. For a client application like DataEase, it is a 3 step route to get an API like this to work. When all is set up to use it is simple. Under is get my primary calender definition using the google calender api, but about any oauth2 based login use the same way of setting up, just different cosoles where you register your interest in using the api and get back you clientid and secret.

define "result" memo .
define "accesstoken" Memo .

execdql("*:mailreadini.dql", "API") .
accesstoken := GetVar("apiaccesstoken") .
if accesstoken = blank then
  result := GetVar("apitokenerror") .
else
  --execdql("*:remotedebugon.dql") .
  Remote("@logintype", "OAuth2") .
  Remote("@login", accesstoken) .
  result := Remote("@getraw", "https://www.googleapis.com/calendar/v3/calendars/primary") .
  --Remote("@debugoff") .
end.
list records
  result .

In this example, you have got a api token and a refresh token that is stored in a json file and how to use is configured in you rdrrXaaa.ini file. This dql read the token setting and do a refresh if needed using the mailreadini.dql stored in dqls in the program folder. As you can see from the name, this is the config loader for email configs, but can also be used for API.

[API]
APIAUTHENTICATION=OAUTH2
APITOKENFILE=C:\data\myapp\oauth2\myapikey.json
APICONFIGFILE=C:\data\myapp\oauth2\oauth2config.json

In this example the config for using apis are in oauth2config.json file. Then the keys for using the api as a google user is stored in myapikey.json. The myapikey.json hold all the information needed to know if the id is valid and if not how to refresh it. The oauth2config.json hold what is needed to use the google api. This will be the clientid and secret you have set up in the api console.

{
  "tokentype": "bearer",
  "mysecret": "thiswillbetheapitokentheyouwillusewhenconnectingtotheapi",
  "idtoken": "thisislogstringuplettersandnumbersthatholdencryptedinfoaboutwhyyouareandwhenthesecretexpires",
  "refreshtoken": "thiswillholdtherefreshtokenthatisusedwhenyouneedanewsecret",
  "timeout": 3599,
  "scopes": [
    "https://www.googleapis.com/auth/userinfo.profile",
    "https://mail.google.com/",
    "openid",
    "https://www.googleapis.com/auth/calendar",
    "https://www.googleapis.com/auth/userinfo.email"
  ],
  "issue_time": "2025-03-14 09:57:18 UTC",
  "expire_time": "2025-03-14 10:57:18 UTC",
  "subject": "103853233731921730485",
  "email": "me@gmail.com",
  "maildomain": "google.com"
}

How myapikey.json would look like

{
  "endpoints": {
    "outlook.com": {
      "issuer": "https://login.microsoftonline.com/{tenantid}/v2.0/",
      "tenentid": "common",
      "discoveryfilepath": "",
      "clientid": "your-microsoft-client-id-here",
      "secret": "your~secret~here",
      "sendsecretfortoken": false,
      "redirecturl": "",
      "scopes": [
        "openid",
        "profile",
        "offline_access",
        "https://outlook.office.com/SMTP.Send",
        "https://outlook.office.com/IMAP.AccessAsUser.All",
        "https://outlook.office.com/POP.AccessAsUser.All"
      ]
    },
    "yahoodns.net": {
      "issuer": "https://api.login.yahoo.com",
      "tenentid": "",
      "discoveryfilepath": "",
      "clientid": "youryahooidhere",
      "secret": "thesecrethere",
      "sendsecretfortoken": false,
      "redirecturl": "https://redirect.dataease.com/yahoo",
      "scopes": [
        "profile",
        "email"
      ]
    },
    "google.com": {
      "issuer": "https://accounts.google.com",
      "tenentid": "",
      "discoveryfilepath": "",
      "clientid": "yourgooglecientidhere.apps.googleusercontent.com",
      "secret": "the-google_secret",
      "sendsecretfortoken": true,
      "redirecturl": "",
      "scopes": [
        "email",
        "profile",
        "https://mail.google.com/",
        "https://www.googleapis.com/auth/calendar"
      ]
    }
  },
  "localserverport": 8831
}

How oauth2config.json would look like. Things to notice is the outer "localserverport": 8831 as this is the internal server port used on 127.0.0.1 and the "redirecturl" blank. This mean we redirect direct to the localhost on your computer and need no mediating server to receive end redirect. The key "sendsecretfortoken" are mostly false, but google need it true. This indicates if the secret should be sendt when a refresh is done and not only when creating the first token. When using the config for getting XOAUTH2 mail protocol, the name used for the config, should be the same as returned for maildomain in automated mail config. For any other APIs, feel free to give your own names to the config. It will be third paramter in @OAuth2Create and stored in the resulting config under maildomain.

SetValue("Result", DEOS("@OAuth2Create", "C:\data\myapp\oauth2\oauth2config.json", "google.com", "C:\data\myapp\oauth2\myapikey.json", "me@gmail.com"))

To create a new apikey.json file, you need to have a oauth2config.json file configured with a clientid and secret for using the api. The scoope list have to match the documention of what is needed by the api. Then you have to make sure you login and select all the scopes (what you want to do with the api) from the api grant screen popping up in your default browser when running the command. When all this is setup, you simply call DEOS("@OAuth2Create"...) with the correct settings and authorize what you want to use in your default browser. If this is the first time you run it on your computer, you migth get a messae asking to allow decreateoauth2.exe to use your network. The same might happen later the first time when a refresh of token is needed and then the application name will be derefreshoauth2.exe.

To set up your own connection to google, microsoft or others

First of all, what we are setting up is openid connect 2 client autorization flow if you need to google it. This basically asks the server to let you allow a registered application in their sytem to use their apis on behalf of a user. The user grant the permission by logingin on a spesial login page given by the api provider. To be able to do that, you have to register and get a set of keys (client id and secret). Then you need to know the issuer url for their autorization server and for some, you will also need a tenentid (Microsoft).

Google

We use google as an example as they have a lot of APIs and are good at following the standards. There is one url to register and no tennentid to consider. Their security and timeouts are short, but if you are not scared by they warnings, you can very quickly getting to use all their APIs by setting up a developer id for your google account login.

Google api console: here

• Create or select a project and do New project and give it a name, create and then select it
• Select the API and services and select APIs to enable
• Find the Authorization scopes needed for the API and add then to your config. There are usually at least on  for read/write and on for readonly. 
• Configure your consent screen by giving name and email, select external unless you have workspace for your organization, fill email again and finish up
• Go to Credentials and Create credentials select OAuth Client ID, here you select Desktop app and give it a name, create and copy Client Id to "clientid" and Client secret to "secret" in for config file. If you download the json file, "client_id" goes to our "clientid" and "client_secret" goes to our "secret".

Microsoft

Microsoft have a very complex structure where you can configure everything and also rent your own part of the system as a tennent where you get your own tennentid. Due to this complexity, we are only goint to tuch the common part of the api that is used for all consumer parts of the Microsoft api structure. We also only support the client login flow (you autorizing the program to use the api by logging in on a spesial Microsoft web page).

Microsoft api console: here

• Here you do New registration
• Select type (Ex. Accounts in any organizational directory (Any Microsoft Entra ID tenant - Multitenant) and personal Microsoft accounts (e.g. Skype, Xbox) if you want outlook.com or apis)
• Platorm use Public client/native (mobilde & desktop) as this will allow for a localhost server flow
• Application (client) ID is going in "clientid": in your endpoint configuration
• Go to Manage->Certificates & secrets and create "New client secret" select expiry time copy value into "secret"
• Add "tenentid": "common" if you did the Public client/native
• Add your scopes by Manage -> API permissions and add whatever you select to the "scopes" list, and you are ready to go

Others

All apis using oauth2 will have similar processes of setting up the api.You need to find their issuer url to be used to discover all the other urls used for getting the tokens. Then you need to figure out how to register a application that can use localhost as redirect server and get your client id and secret to use.

DG3 login

define "un" Text 60 .
define "pw" Text 60 .
define "url" Text 255 .
define "LastSession" Text 255 .
un := "UserName".
pw := "Password".
url := "https://server.domain/". 
LastSession := Remote("@current:testuser").
Remote("@cleanup").
Remote("@username",un).
Remote("@password",pw).
Remote("@logintype:dg3").
Remote("@connect",url).
Remote("@login").
alert(Remote("@isloggedin")).
Remote(LastSession).

Example on DG3 login tha will altert with yes if logged in and no if not.

OpenID Connect login

This is a obsolete way of doing it only working in DE10.

This way of login is not natively supported by DataEase, but you can du it manually by using remote to get a authentication bearer.

First you need to find out what url that a used to login to the server. This is done in a discovery fase. In the example below this is done manually before building the login. You cal do this automatically before each call to the server as well by do a remote to the discovery service that can be found at 

https://the.loginservice.domain/.well-known/openid-configuration

From here you can find the token_endpoint and the supported algorithms (id_token_signing_alg_values_supported) for getting the authentication token.

When you know all of this you need to generate signatures and send them to the correct login service. If you need to use the RSA routines for encrypting the jwt token, you must decode your key and store it in a readable format for the DEOS("@signature"...) function. To do that you need your encrypted key deliverered by the authentication provider, your key password and OpenSSL.

# To export the private key from the pfx file:
openssl pkcs12 -in win_cert.p12 -nocerts -out key.pem
# And now remove the key password:
openssl rsa -in key.pem -out key_with_no_pw.key<br>

Commands to decode your private key that you need to use.

define "Dummy" Text .
define "RSession" Text 100 .
define "kid" Text 100 .
define "clientid" Text 100 .
define "companykey" Text 255 .
define "tokenendpoint" Text 100 .
define "keypath" Text 250 .
define "accesstoken" Memo .
kid := "QtiXUhB5OuFHIegxeCZKx40nxAs" . -- this is a spesial id that you will find used on MicroSoft server
clientid := "0ce0527e-8272-6492-b334-5e0f4cc072c6" . -- some id you got back from your provider
companykey := "831bfe10-ab1e-4caf-96e9-912a6ed2ef91" . -- a key given to you by the provider that
tokenendpoint := "https://the-login.server.domain/connect/token" . -- url from disco
keypath := concat(GetCurrent("AppPath"),"\keytest.key") . -- your extracted key stored in a file in your app
RSession := Remote("@current:openidconnect") .
Dummy :=  Remote("@cleanup") .
Dummy :=  Remote("@debug:file", concat(GetCurrent("AppPath"),"\remotelog.txt") ) . -- debug to see what is called
Dummy := DEOS("@signature", "createsignature", "jwt", "rs256", DEOS("@ReadFromFile",keypath)) .
Dummy := DEOS("@signature", "addtoheader", "kid", kid) .
Dummy := DEOS("@signature", "addtopayload", "jti", DEOS("@guid")) .
Dummy := DEOS("@signature", "addtopayload", "sub", clientid) .
Dummy := DEOS("@signature", "addtopayload", "iss", clientid) .
Dummy := DEOS("@signature", "addtopayload", "aut", tokenendpoint) .
Dummy := Remote("resource=", tokenendpoint).
Dummy := Remote("grant_type=client_credentials") .
Dummy := Remote("client_id=", clientid).
Dummy := Remote("client_assertion_type=urn:ietf:params:oauth:client-assertion-type:jwt-bearer").
Dummy := Remote("client_assertion=", DEOS("@signature", "getsignature")).
Dummy := Remote("scope=AppFramework").
-- Get the return code from the server, since it comes as text set it back to session
Dummy := Remote("@setjson",Remote(concat("@posturl:", tokenendpoint))).
-- If all when well there shoud be a access token for you now
Dummy := SetVar("accesstoken",Remote("access_token")) . 
Dummy := SetVar("companykey", companykey) .
Dummy :=  Remote("@debugoff") .
Dummy := Remote(RSession) . -- return the the one set when we started 

Example of how to get and use a open id connect authentication bearer

define "Dummy" Text .
define "RSession" Text 100 .
RSession := Remote("@current:companyinfo") .
Dummy :=  Remote("@cleanup") .
Dummy :=  Remote("@debug:file", concat(GetCurrent("AppPath"),"\remotegetinfo.txt") ) .
Dummy := ExecDQL("@ConnectToAPI") .
Dummy := Alert(GetVar("accesstoken")) . 
Dummy := Remote("@headerset",concat("Authorization: Bearer ",GetVar("accesstoken"))) .
Dummy := Remote("@headerset",concat("CompanyKey: ",GetVar("companykey"))) .
Dummy := Remote("@get:https://api.server.domain/api/into/something") .
Dummy := alert(Remote("@json")).
Dummy :=  Remote("@debugoff") .
Dummy := Remote(RSession) . -- return the the one set when we started 

How to use the access token if this ExecDQL was stored with the name ConnectToAPI in $$DQLStore$$