A closer look at configuring Single sign-on for SAS SpeedyStore
Introduction
SAS SpeedyStore is a new offering that has been released recently. It combines the advanced analytics and AI capabilities of the SAS Viya platform with a high performing and unified data platform that is Singlestore.
Various blogs have been written about SAS SpeedyStore. If you are interested in reading more about what SAS SpeedyStore is and some of it’s benefits and capabilities read this blog post here. If you are looking for details on the architecture behind SAS SpeedyStore have a look at this blog post here.
In this blog let’s look at what it takes to configure Single sign-on for SAS SpeedyStore. Using SSO allows you to securely access your data without providing your credentials in the code. Having users and groups from Entra ID available within the SInglestore database allows you to apply fine-grained access controls to data and track who has accessed what.
I’ll be sharing some of my insights that I gained while going through the steps to configure this! Keep on reading to find out more!
Enabling Single Sign-on for SAS SpeedyStore
To enable Single sign-on for SAS SpeedyStore these are the steps that need to be completed when using SCIM to provide identities to Singlestore:
- Configure Single Sign-on for the SAS Viya platform using OpenID connect.
- Setup JWT authentication for Singlestore users.
- Decide how the identities (users and groups) will be provided to Singlestore.
- Create a Singlestore account for synchronization.
- Registering a new Oauth client in SAS Viya.
- Creating the required Kubernetes secret for the synchronization process.
- Scheduling the synchronization process.
Throughout this blog post the above steps will be further detailed, and some additional insights will be provided that will help in configuring SSO for SAS Speedystore.
Configure Single sign-on for the SAS Viya platform
To configure Single sign-on for the SAS Viya platform please follow these instructions. Keep track of the following two things which are important for the setup of SSO on SAS SpeedyStore.
- Make sure to retrieve the jwks_uri from the OpenID connect metadata document. For azure this url is: https://login.microsoftonline.com/<entra_tenant_id>/discovery/v2.0/keys. This will be used in step 2
- Make sure that profile is included in the scopes configuration field. This will make sure UPN (user principle name) is included in the user claim.
Currently SSO for SAS SpeedyStore is only tested with OpenID connect on Azure. Technically it should work with any OpenID connect provider if it has a jwks endpoint and adheres to the OpenID connect standards. Perhaps another blog topic or let me know in the comments if you managed to set this up with something other then Azure.
Setup JWT authentication for SInglestore users
To setup JWT authentication two Singlestore engine variables need to be set.
- jwks_endpoint: variable contains the endpoint that is used to retrieve JWTs from Microsoft Entra ID
- jwks_username_field: variable specifies the name of the JWT field that determines the database username. The User Principal Name (UPN) attribute is used for this purpose.
Use the below patch to set these variables in Singlestore.
---
apiVersion: builtin
kind: PatchTransformer
metadata:
name: patch-transformer-jwt-s2-azure
patch: |-
- op: add
path: /spec/globalVariables/jwks_endpoint
value: "https://login.microsoftonline.com/entra_tenant_id/discovery/v2.0/keys"
- op: add
path: /spec/globalVariables/jwks_username_field
value: "upn"
target:
kind: MemsqlCluster
Now let’s have a look at the options that are available to provide information about users and groups to Singlestore.
Decide how the identities will be provided to Singlestore
To provide information about identities to Singlestore, like users, groups and memberships of users to one or multiple groups, there are two options that can be used: LDAP or SCIM (added as of Stable 2025.02).
You can’t use both at the same time. Which option to use depends on what is allowed within your organization.
SCIM requires a publicly available endpoint and a certificate that is signed by a public authority. If either of those is not available, then the decision has been made for you, and you will have to use LDAP. If you need to use LDAP, have a look at the official documentation here. This describes the configuration that is required to use LDAP in more detail.
I prefer using SCIM to provide information about identities to Singlestore as it is easier to set up. In the remainder of this blog, I will focus on configuring the SCIM method.
An overview of the steps involved to synchronize users and groups using SCIM
The above picture provides an overview of the steps involved in synchronizing user and group information to the Singlestore database. Synchronization is done through a cronjob which runs on the Kubernetes cluster and is part of the SAS SpeedyStore deployment. This cronjob is called singlestore-sync.
An important pre-requisite for this cronjob to work is that users and groups have been provisioned to SAS Viya through SCIM.
Let’s have a closer look at each of the steps shown in the above overview.
- The singlestore-sync cronjob requires an Oauth token to access the SAS Viya Identities SCIM API. This Oauth token is generated when you register a new Oauth client in the SAS Viya platform. Keep on reading to find out how you can register a new Oauth client to get the cronjob running.
- Using the Oauth token the SAS Viya identities SCIM API is accessed to query the state of users and groups.
- The SAS Viya identities SCIM API returns the state of users and groups to the singlestore-sync cronjob.
- The cronjob then retrieves the Singlestore credentials from the Kubernetes secret for the Singlestore account.
- Using the retrieved credentials from the Kubernetes secret for the Singlestore account, the cronjob will then retrieve the state of users and groups from the Singlestore database
- Once the state is retrieved from the Singlestore database a reconcile routine is run in the cronjob to reconcile the state with the state retrieved from the SAS Viya identity SCIM API
- Based on the results of step 6, users and groups are updated in the Singlestore database to reflect the current state. This is done through SQL statements.
Now let’s look at what permissions are needed by the Singlestore account that needs to created before the cronjob can be run.
Creating a Singlestore account for synchronization
A synchronization account is needed by the Kubernetes cronjob responsible for synchronizing users and groups into Singlestore. This account needs to be created within the Singlestore database and is provided through a Kubernetes secret to the Kubernetes cronjob.
This account will create, delete and update users and groups in Singlestore. It is recommended not to use an admin account for this purpose and limit the permissions of this account to what was just discussed. That’s sounds like a good plan!
However, the documentation doesn’t specify which permissions are required. It simply refers to the Singlestore documentation. No worries, this blog post can help! Going through the permissions matrix and looking at the available permissions, these are the permissions that are required by the synchronization account.
|
Permission |
Description |
|
CREATE USER |
Create and drop users (no grants). |
|
ALTER USER |
Alter user profiles with ALTER USER or SET PASSWORD commands. |
|
CREATE GROUP |
Requires GRANT permission |
|
DROP GROUP |
Requires GRANT permission |
|
GRANT GROUP |
Requires GRANT permission |
|
REVOKE GROUP |
Requires GRANT permission |
One thing to note about the above permissions is that permissions for creating, dropping, adding users to groups and removing users from groups all rely on the GRANT permission. To summarize, an account needs the following set of permissions to synchronize users and groups to Singlestore.
|
Permission |
Description |
|
CREATE USER |
Create and drop users (no grants). |
|
ALTER USER |
Alter user profiles with ALTER USER or SET PASSWORD commands. |
|
GRANT |
Grant / revoke permissions, manage roles & groups. Distinct from WITH GRANT OPTION. |
To create an account (user) on Singlestore with the above permission the following statements need to be executed.
# Logon to master pod. This contains the singlestore cli
kubectl exec -it node-sas-singlestore-cluster-master-0 -n viya -- bash
#Logon to singlestore using the admin user
singlestore -u admin --password=<password> -h svc-sas-singlestore-cluster-dml -P 3306
#Create user
CREATE USER scim IDENTIFIED BY <set-password-for-scim-user>;
#Grant permissions
GRANT CREATE USER, ALTER USER ON *.* TO 'scim'@'%';
GRANT GRANT OPTION ON *.* TO 'scim'@'%';
show grants for 'scim'@'%';
That’s it. Now we have a Singlestore account (user) that can be used to synchronize users through the Kubernetes cronjob.
Registering a new Oauth client in SAS Viya
Now that the account is created in the SingleStore database the next step is to register a new Oauth client in SAS Viya. There is a nice blog that describe the steps in detail to register a new Oauth client. To register a new Oauth client follow the below steps.
Setting variables
VIYA_INGRESS_URL=https://<INGRESS-URL>
# Specify SAS Viya platform username and password.
VIYA_USERNAME=<USERNAME>
VIYA_PASSWORD=<PASSWORD>
# Create an OAuth client ID and OAuth client secret to register the OAuth client
OAUTH_CLIENT_ID=singlestore
OAUTH_CLIENT_SECRET=$(uuidgen)
# Specify SingleStore account for synchronization.
SINGLESTORE_USERNAME=scim
SINGLESTORE_PASSWORD=<SINGLESTORE-PASSORD>The above code creates the necessary variables and sets the required values for these variables. Make sure that you provide credentials of a user that is part of the SAS Viya administrators group or else the below code will not work. Replace the password of the Singlestore user with the one that was used in the previous step.
Execute scrip to register new Oauth client
echo
echo "Executing steps to generate an OAuth API token."
echo
# Generate a bearer token.
export BEARER_TOKEN=$(curl -sk -X POST "${VIYA_INGRESS_URL}/SASLogon/oauth/token" -u "sas.cli:" \
-H "Content-Type: application/x-www-form-urlencoded" \
-d "grant_type=password&username=${VIYA_USERNAME}&password=${VIYA_PASSWORD}" | jq -r '.access_token')
json=$(cat <<EOF
{
"client_id": "$OAUTH_CLIENT_ID",
"client_secret": "$OAUTH_CLIENT_SECRET",
"scope": ["uaa.none"],
"authorities": ["identities"],
"access_token_validity": 63070000,
"authorized_grant_types": ["urn:ietf:params:oauth:grant-type:jwt-bearer", "client_credentials"]
}
EOF
)
# Register an OAuth client.
echo "Registering the client. Registration response follows."
echo
curl -kX POST "${VIYA_INGRESS_URL}/SASLogon/oauth/clients" \
-H "Content-Type: application/json" \
-H "Authorization: Bearer $BEARER_TOKEN" \
-d "$json"
OAUTH_API_ACCESS_TOKEN=$(curl -ks -X POST "${VIYA_INGRESS_URL}/SASLogon/oauth/token" \
-H "Content-Type: application/x-www-form-urlencoded" \
-d "grant_type=client_credentials" \
-u "${OAUTH_CLIENT_ID}:${OAUTH_CLIENT_SECRET}" | jq -r '.access_token')
The above script registers the new Oauth client in SAS Viya. First it uses the credentials you provided to logon to SAS Viya and retrieve what is called a BEARER_TOKEN. This BEARER_TOKEN is then used to authenticate against the SAS logon oauth clients endpoint. A json file that described the oauth client is send to the endpoint. Once the Oauth client is registered the credentials of that client are used to logon to the SAS Viya platform and retrieve an ACCESS_TOKEN. This token is provided to the singlestore-sync cronjob via a secret.
Bear in mind that this token has a lifespan which is set in the json file that describes the oauth client. This means that at a certain point in time this token will need to be refreshed.
Creating the Kubernetes secret
Now that a new Oauth client is registered in SAS Viya a Kubernetes secret needs to be created that contains the OAUTH client access_token, the Singlestore username and the password of the singlestore user. This information stored in the secret is used by the singlestore sync cronjob to synchronize users between SAS Viya and Singlestore.
Use the below code to create the secret on the Kubernetes cluster
# Create the secret using kubectl. Note that the secret must be named sas-singlestore-sync.
echo
echo "Creating secret via 'kubectl create secret...'"
echo
kubectl create secret generic sas-singlestore-sync \
--from-literal=SINGLESTORE_USER=$SINGLESTORE_USERNAME \
--from-literal=SINGLESTORE_PASS=$SINGLESTORE_PASSWORD \
--from-literal=OAUTH_API_ACCESS_TOKEN=$OAUTH_API_ACCESS_TOKEN \
-n viyaScheduling the synchronization process of users and groups
To trigger the synchronization process to load users and groups into Singlestore you can manually execute the singlestore-sync cronjob by executing the following command.
kubectl create job --from=cronjob/sas-singlestore-sync manual-job-name -n name-of-namespace
This will start the synchronization of users and groups to Singlestore. As an initial load this works flawlessly, but user and group information have the tendency to change. To cope with these changes, we need a way to schedule the synchronization process.
Luckily this can be achieved easily. To temporarily schedule the singlestore-sync cronjob run the below command.
kubectl patch cronjob sas-singlestore-sync --type=merge -p '{"spec":{"schedule":"0 1 * * *","suspend":false}}'
This will schedule the job to run every day at 1AM. But if you must redeploy to do an upgrade of SAS SpeedyStore you will lose these changes. To make it more permanently you can use the kustomize overlay that is provided with SAS Viya. Review the steps to permanently schedule the job here.
Validating the results of running the cronjob
Logs are generated when running the singlestore sync cronjob. These logs contain information about if users or groups have been added or removed. You can simply review these logs by executing kubectl logs <podname> -n <namespace>
Another way to validate the results is to logon to the Singlestore database and execute these commands
To show the users.
singlestore> show users;
+--------------------------------+--------+-------------+------------+-----------------------+----------+
| User | Type | Connections | Is deleted | Default resource pool | Is local |
+--------------------------------+--------+-------------+------------+-----------------------+----------+
| 'my.name@sas.com'@'%' | JWT | 0 | | | SYNC |
| 'admin'@'%' | Native | 1 | | | SYNC |
| 'root'@'%' | Native | 0 | | | LOCAL |
+--------------------------------+--------+-------------+------------+-----------------------+----------+
3 rows in set (0.00 sec)
To show groups and memberships of groups use.
singlestore> show groups for user my.name@sas.com'@'%';
+-------------------------------------------+
| Groups of user my.name@sas.com@% |
+-------------------------------------------+
| Sales |
+-------------------------------------------+
1 row in set (0.00 sec)
singlestore> show groups;
+--------+
| Groups |
+--------+
| Sales |
+--------+
1 row in set (0.00 sec)
The above commands should show something similar. If not, you will need to review the logs of the cronjob. If those don’t contain any helpful information you can increase the logging level and see if something pops-up that might help in further debugging the issue.
Is it possible to synchronize only specific users or groups?
The singlestore sync cronjob queries the SAS Identities SCIM API to retrieve information about users and groups and then loads those into the singlestore database. This means that all users and groups that have been loaded into SAS Viya through SCIM from Entra ID will also be loaded into singlestore.
Currently there is no way to apply a filter to this process. Perhaps this is something that will be added in the future.
Conclusion
Accessing your data using Single Sign-on allows you to securely access your data without the need of providing a username and password within the code. Having the users and groups from your Entra ID available within Singlestore allows you to apply fine-grained access controls to better secure your data that resides within the SpeedyStore database.
With the help of this blog and some insights I have shared configuring SSO for SAS SpeedyStore should be a walk in the park.
Reference
