Setup SSO Authentication with External Identity Providers

Configuring CData Virtuality OAuth 2 Server

To configure the CData Virtuality OAuth 2 server, follow these steps.

Use a browser to login to CData Virtuality OAuth 2 Server, e.g. localhost:8080.

Create a realm

• Provide a name, e.g. CDVSERVER_SSO

  • The name is the only detail needed for realm setup

• The “Create realm” button is available via menu in left upper corner

• Do always ensure that you work in that newly created realm and not in the master realm, especially after fresh login

Create a client

• “Client type”: "OpenID Connect" 

• “Client ID”: provide a id (name), e.g. cdvserver_sso_client

Set up access settings for your client in the CData Virtuality OAuth 2 server

Go to your realm → your client → Settings

Set up Capability config

• Turn on Client authentication

• Set up Authentication flow. Choose following:

  • Standard flow

  • Implicit flow (note for Anja: missed in Google doc)

  • Direct access grant (note for Anja: missed in Google doc)

  • Service accounts role

Add the following Valid redirect URIs:
<CData Virtuality server URL e.g. http://localhost:8180 >/*;

Add the following Web origins:
<CData Virtuality server URL>

Set up service account roles

1. Go to your realm → your client → Service account roles

2. Assign the following roles:

• realm-management view-users - to allow import/refresh of users

• realm-management view-realm - to allow import/refresh of roles

Configure identity provider

Initial setup

1. Open Identity Providers menu of your realm

2. Add "OpenID Connect" provider 

3. Provide an Alias and optionally a display name

4. Provide the details as collected from external Identity Provider

   1. "OpenID Connect metadata document URL" as discovery URL

   2. “Client ID”

   3. “Client Secret” 

5. Save the Identity Provider configuration

6. After saving: 

   • Extract/copy the URL shown as redirectUrl. 

   • This URL must be set up as a redirectUrl on your external Identity Provider (e.g. see Configure the "Sign-in redirect URIs" in Okta application section in Okta SSO for SaaS)

Users and roles

Users and roles could be loaded to the CData Virtuality OAuth 2 server using Identity Provider extension or by using Mappers.

Mappers setup

Create a Mapper to assign the Identity Provider groups to CData Virtuality OAuth 2 server roles. The “Mapper” menu is accessible via “Identity Providers” → Identity Provider added on previous step → “Mapper” tab 

1. “Name”: provide a name for the mapper to be created

2. “Sync mode override”: Force 

3. “Mapper type”: Claim to Role 

4. “Claim”: groups 

5. “Claim Value”: the id or the name for the Identity Provider group. Examples:

   1. Azure: 59def5b5-b0a5-443f-ab8d-f3de1782fd8a

   2. Okta: CDV-Admin

6. “Role”:  CData Virtuality OAuth 2 server role-name. To see the realm roles in assignment dialogue, please us "Filter by realms" in the interface 

Configure one mapper per Identity Provider group that shall be mapped to a CData Virtuality OAuth 2 server/CData Virtuality server role.

Extensions setup

The extension allows importing and synchronizing users and roles from external Identity Providers into the CDV OAuth2 server. Currently, the extension is supported only for Okta. For details on how to configure the Okta extension, see Okta SSO for SaaS.

Configure CData Virtuality Server

Setting the Option Values

You need to set following CData Virtuality OAuth 2 server option values:

Example:

CALL "SYSADMIN.setDefaultOptionValue"(
    "opt" => 'DV_AUTH_SERVER_URL'
    , "val" => ' http://localhost:8081/realms/CDVSERVER_SSO/.well-known/openid-configuration');;

CALL "SYSADMIN.setDefaultOptionValue"(
    "opt" => 'DV_AUTH_REALM'
    , "val" => 'CDVSERVER_SSO');; 

CALL "SYSADMIN.setDefaultOptionValue"(
    "opt" => 'DV_AUTH_CLIENT_ID'
    , "val" => 'cdvserver_sso_client';; 

CALL "SYSADMIN.setDefaultOptionValue"(
    "opt" => 'DV_AUTH_CLIENT_SECRET
    ', "val" => 'HCPYEWeiknqUOtwFRXWnodDPDSmmpCng');; 

CALL "SYSADMIN.setDefaultOptionValue"(
    "opt" => 'DV_AUTH_LOAD_USERS'
    , "val" => true);; 

CALL "SYSADMIN.setDefaultOptionValue"(
    "opt" => 'DV_AUTH_LOAD_ROLES'
    , "val" => true);;

Loading SSO Roles and Users

Connect to the СData Virtuality Server using the standard СData Virtuality authentication mechanism as admin and follow these steps:

Run the SYSADMIN.refreshSSOUserCache procedure:

CALL "SYSADMIN.refreshSSOUserCache"();;

Roles and users will be loaded.

Loaded external Identity Provider roles and users have @SSO ending (e.g. admin-role@SSO, user_1@SSO, etc.), and user-role mapping can be seen in SYSADMIN.UserRoles after SSO users and roles are loaded.

If you want to refresh explicitly in CData Virtuality Server, e.g. after manually removing a user or a role from CData Virtuality OAuth 2 server, call SYSADMIN.refreshSSOUserCache

Users

When using Mappers, users are added only after their first successful login. When using the extension, users are loaded along with their roles after executing SYSADMIN.refreshSSOUserCache.

Note for Anja: I didn’t added mapper for users info.

Roles

Note for Anja: I didn’t add the info about roles loading. The bug you mentioned in the Google doc(roles without users are not loaded) is fixed in v25.1. So, we don’t need to add an info about it in the document in 25 space but need to add to 4(CV) space.

Set permissions

Permissions can be set in one of two ways.

Please decide for one approach and do not mix them.

1. By setting permissions for an object to the SSO role:

CALL "SYSADMIN.setPermissions"(
    "role_name" => 'admin-role@SSO',
    "resourceName" => '*',
    "permissions" => 'CRUDEAL'
);;

2. By mapping SSO roles to CData Virtuality roles. In this case, all the permissions from the CData Virtuality role will be granted to the SSO role:

CALL "SYSADMIN.setPermissions"(
    "role_name" => 'admin-role@SSO',
    "mapToRole" => 'admin-role'
);;

Connecting to CData Virtuality Server using Web UI

After the DV_AUTH option values are set, roles and users are loaded and permissions are set, you can connect to the CData Virtuality Server via SSO using the SSO authentication wizard.

The default parameters, such as the CData Virtuality OAuth 2 server URL, client ID, and client secret, are set in the dvserver\standalone\deployments\api.war\WEB-INF\classes\service-on-premise.properties file.

To modify these parameters, unzip the api.war file, change the parameters as needed, and then zip it back and redeploy api.war.

Example of the settings:

• oidc.server.client.id=dvserver_sso_client

• oidc.server.config.url=http://localhost:8081/realms/CDVSERVER_SSO/.well-known/openid-configuration

• oidc.server.client.secret=HCPYEWeiknqUOtwFRXWnodDPDSmmpCng

• oidc.server.use_dv_roles=true

Note for Anja: I didn’t add Skip the intermediate login screen section

Connecting to CData Virtuality Server using Studio

After the DV_AUTH option values are set, roles and users are loaded and permissions are set, you can connect to the CData Virtuality Server using the OAuth 2.0 authentication with access/refresh tokens. For details on how to obtain tokens, see Obtain Access and Refresh Tokens section.

Connecting to CData Virtuality Server using JDBC driver

After the DV_AUTH option values are set, roles and users are loaded and permissions are set, you can connect to the CData Virtuality Server using the OAuth 2 authentication.

Set the authType=OAUTH2 parameter to use OAuth 2 authentication.

You should use access/refresh tokens to connect. For details on how to obtain tokens, see Obtain Access and Refresh Tokens section.

Connection string example:

jdbc:cdatavirtuality:datavirtuality@mms://localhost:31001;authType=OAUTH2;accessToken=<token>;refreshToken=<token>

Obtain Access and Refresh Tokens

Obtain Okta users access and refresh tokens:

1. Get authorization code:

   1. URL to obtain authorization code: <CData Virtuality OAuth 2 Server URL>/realms/<your realm name>/protocol/openid-connect/auth?response_type=code&client_id=<your client id>&redirect_uri=http://localhost/;

   2. Select Okta and login using standard Okta authorization;

   3. Copy code from the redirected URL:

      

2. Get access and refresh tokens:

   1. URL to get tokens: <CData Virtuality OAuth 2 Server URL>/realms/<your realm name>/protocol/openid-connect/token;

   2. Parameters in request body:

      • grant_type = authorization_code

      • client_id = <your client id>

      • client_secret = <your client secret>

      • redirect_uri = http://localhost/

      • code = from Step 1

3. Use the returned access and refresh tokens while connecting via CData Virtuality Studio and JDBC/ODBC clients.