Author: Randy Betancourt
Date: September 25, 2025
Purpose
This document outlines authentication requirements and behaviors for Altair/Siemens SLC integration with Google BigQuery. We distinguish between service account keys and individual OAuth tokens, their security implications, and operational considerations in a production environment.
General Principles:
- Manage/protect authentication tokens the way passwords are managed/protected
- Block SLC user access writing corporate data to locally attached storage
- Configure a flexible SLC authentication behavior to enable:
- Private GCP Oauth keys providing maximum security by incurring administration overhead
- Service account keys (Trades convenience for increased risk posture)
- Enable SLC program execution to be automated through scripts and other workflow processes
Authentication Methods Overview
Service Account Keys vs Individual OAuth Tokens
Service Account Keys:
• JSON files containing private keys for service authentication
• Shared across multiple users and applications
• Long-lived credentials that whose expiration is managed
• Suitable for batch processing and automated systems
• Security Risk: Shared credentials create audit trail challenges
Individual OAuth Tokens:
• User-specific authentication tokens associated to individual GCP accounts
• Generated through OAuth 2.0 requiring users to manually refresh expired tokens
• Configurable to expire and/or refresh automatically
• Provides detailed and clear audit trails linking queries to specific users identities
• Recommended for SLC: Meets KeyBank's security requirement for individual user authentication
Auto-Rotate Keys Policy Impact
Current Challenge
The auto-rotate keys policy creates operational overhead when using private Oauth account keys:
• Keys expire without warning
• Manual intervention required to update configurations
• Service interruptions during key rotation periods
• Administrative burden for users to generate/consume new keys
OAuth Token Advantages
Individual OAuth tokens provide better alignment with auto-rotate policies:
Automatic refresh capability built into OAuth 2.0 protocol- Users need help automating the manual key generation process to support production needs. This extends to scheduled/batch SLC execution and process automation
- Better security posture with shorter-lived credentials
SLC Integration with BigQuery
Connection Methods
1. DSN-less connection Pass-Through SQL Method (Recommended for POC)
This connection method by-passes DSN attribute retrieval actions from ODBC.ini config file. The connection is transient and last for the duration of the PROC SQL execution context.
PROC SQL;
CONNECT TO BIGQUERY (
database = "<db_name>"
account = "<account_name>"
schema = "<schema_name>"
private_key_path = "<path>/credentials.json"
driver = "<path>/libgooglebigqueryodbc_sb64.so"
max_char_len = 100
scanstringcolumns = yes
READ_MODE = STORAGE
);
CREATE TABLE work.result AS
SELECT * FROM CONNECTION TO BIGQUERY (
-- Your BigQuery SQL here
);
DISCONNECT FROM BIGQUERY;
QUIT;
2. DSN-less connection LIBNAME Method(Recommended for POC)
This connection method by-passes DSN attribute retrieval actions from ODBC.ini config file. The connection is non-transient and last for the duration of the SLC execution context.
%let database_name = <db_name>;
%let schema_name = <schema_name>;
%let libname_ref = lb1;
%let driver_path = <path/libgooglebigqueryodbc_sb64.so;
%let private_key_path = <path>/credentials.json;
LIBNAME &libname_ref BIGQUERY
driver="&driver_path"
account="&database_name"
database="&database_name"
schema="&schema_name"
private_key_path="&private_key_path"
preserve_names=yes
preserve_col_names=yes
preserve_tab_names=yes;
Authentication Behaviors
ODBC.ini Read Behavior by SLC
Finding: When DSN parameter is specified in user program context, ODBC.ini settings override program-level parameters:
WARNING: BIGQUERY: DATASRC libname connection option is set and will override
account, private_key_path and database connection options.
Security Implication: If ODBC.ini contains shared service account credentials, individual user OAuth tokens specified in programs were ignored. This behavior is unexpected by the consultant and is being investigated further.
DSN-less Configuration (Recommended)
To ensure individual OAuth tokens are used:
1. Remove DSN parameter from connection strings
2. Specify all connection parameters explicitly in SAS code
3. Each user maintains their own OAuth credential file and protect accordingly
4. SAS language macro expansion to filename location for tokens is supported
SLC POC Authentication Testing Requirements
Two-User Authentication Test
Objective: Validate user queries are associated with their individual OAuth tokens.
Test Scenario:
1. User A executes query with their OAuth token setting SLC keyword private_key_path= the file location by name
2. User B executes query with their OAuth token setting SLC keyword private_key_path= the file location by name
3. Verify in BigQuery audit logs that queries are attributed to correct individual users
4. Confirm no shared service account usage in logs
Success Criteria:
1. Each query shows individual user attribution in BigQuery logs
2. No shared service account authentication events
3. Sign-off from security team that SLC is using individual user authentication
Production vs POC Considerations
POC Environment
• Authentication: Individual OAuth tokens per user
• Configuration: DSN-less connections with explicit parameters in user programs
• Maintenance: Manual OAuth token refresh as needed
• Security: Full audit trail per user
Production Environment with AuthDomains
AuthDomain Benefits:
• Centralized authentication management
• Integration with existing KeyBank identity systems
• Automated token lifecycle management
• Enhanced security posture
AuthDomain Challenges:
• Requires occasional administrative interventions
• Resource-intensive, one-time setup and configuration
• Dependency on any organisation identity infrastructure changes
Maintenance Requirements
POC Maintenance:
• Monitor OAuth token expiration dates
• Assist users with token refresh procedures
• Update credential file paths as needed
• Troubleshoot individual authentication issues
Production Maintenance:
• AuthDomain health monitoring
• Periodic review of domain configurations
• Coordination with identity team
• Automated alerting for authentication failures
Security Requirements Compliance
Example Security Standards
Individual User Authentication: Each query traced to specific user
Audit Trail: Complete logging of user activities
No Shared Credentials: Elimination of service account key sharing
Token Security: OAuth tokens stored securely per user
Implementation Checklist
• [ ] Remove shared service account keys from ODBC.ini
• [ ] Implement individual OAuth token distribution
• [ ] Configure DSN-less connections
• [ ] Test two-user authentication scenario
• [ ] Verify BigQuery audit log attribution
• [ ] Document user OAuth token refresh procedures
Operational Procedures
OAuth Token Management
1. Initial Setup: Guide users through OAuth consent flow
2. Token Storage: Secure individual credential files in user directories by best practices
3. Refresh Process: Update documented procedure for token renewal
Monitoring and Maintenance
1. Regular Audits: Review BigQuery logs for authentication compliance
2. Token Health Checks: Monitor approaching expiration dates
3. User Support: Assist with authentication issues
4. Documentation Updates: Keep procedures current with changes
Conclusion
SLC BigQuery integration can meet security requirements through individual OAuth token authentication. A POC can demonstrate proper user attribution while the documented approach provides a clear path to production deployment with the AuthDomain feature in SLC Hub.
