Connecting to XML

For information on the module to import, and how to configure the necessary connection properties in a connection string, see Establishing a Connection.

Other available connection properties can be used to configure other aspects of the connector capabilities.

Python Version Support

XML Version Support

See Also

• Using the Connector: Establish connections and query XML through Python code.
• From SQLAlchemy: Use SQLAlchemy to establish a connection with dialect URL, and interact with XML data using mapped classes and Sessions.

The CData Python Connector for XML is designed for streaming XML only.

This streamed file content does not include all of the metadata associated with remotely stored XML files, such as file and folder name.

If access to both the file metadata and the actual file content is needed, then the CData Python Connector for XML must be used in tandem with the associated file system driver(s) for the service the XML files are remotely stored in.

The following file system drivers are available:

• AmazonS3
• Box
• Dropbox
• FTP
• GoogleCloudStorage
• IBLCloudObjectStorage
• OneDrive
• SFTP

See the relevant CData file system driver's documentation for a configuration guide for connecting to stored XML file metadata.

Dependencies

Installation

Windows:

pip install cdata_xml_connector-26.0.9655-cp310-abi3-win_amd64.whl

Linux:

pip install cdata_xml_connector-26.0.9655-cp310-abi3-linux_x86_64.whl

macOS:

pip install cdata_xml_connector-26.0.9655-cp310-abi3-macosx_12_0_arm64.whl

The macOS wheel supports arm64 (Apple Silicon) architectures only on macOS 12 and newer.

Regardless of the environment, certain distributions might require that the "pip3 install" command be used instead, to differentiate from a Python 2 distribution that might exist already. After installation, confirm whether the connector is successfully installed by running the "pip list" command. If "cdata_xml_connector" is present in the list output by the command, then the installation was successful.

Upgrading

When upgrading, "pip install" does not automatically clean up old JRE files. To avoid leftover files that could cause JVM errors, uninstall the previous version before installing the new one.

Licensing

After the installation is complete, a separate step is needed to activate a license for the connector. Among the CData assets in the distribution's site packages, there is an install-license tool that activates this license. From within the distribution's site-packages folder, after navigating to the "cdata/installlic_xml" folder, simply use a command like the below to activate the license. Omitting the <key> argument activates a trial license:

• Windows:
  

  ./install-license.exe <key>

• Linux / Mac:
  

  ./install-license.sh <key>

Sometimes, file access issues may cause pip to install the connector in a fallback file path that is not the python distribution's main or primary site-packages location. This can make it difficult to find where the connector was installed, and from there, the license activator. In that event, this python script below will print out the full file path of the connector's native file. This file will be stored in the mentioned cdata folder, from which the installlic_xml folder is trivial to find:

import os
import cdata.xml
path = os.path.abspath(cdata.xml.__file__)
print(path)

Uninstallation

If the connector needs to be uninstalled for any reason, do so by running the pip uninstall command, as in the example below:

pip uninstall cdata-xml-connector

The objects available within our connector are accessible from the "cdata.xml" module. To use the module's objects directly:

1. Import the module as follows:
   

   import cdata.xml as mod

2. To establish a connection string, call the connect() method from the connector object using an appropriate connection string, such as:
   

   mod.connect("DataModel=Relational;URI=C:\people.xml")

The CData Python Connector for XML allows connecting to local and remote XML resources. Set the URI property to the XML resource location, in addition to any other properties necessary to connect to your data source.

Connecting to Local Files

Set the ConnectionType to Local. Local files support SELECT\INSERT\UPDATE\DELETE queries.

Set the URI to a folder containing XML files: C:\folder1.

Connecting to Cloud-Hosted XML Files

If you need INSERT/UPDATE/DELETE cloud files, you can download the corresponding CData connector for that cloud host (supported via stored procedures), make changes with the local file's corresponding connector, then upload the file using the cloud source's stored procedures.

As an example, if you wanted to update a file stored on SharePoint, you could use the CData SharePoint connector's DownloadDocument procedure to download the XML file, update the local XML file with the CData XML connector, then use the SharePoint connector's UploadDocument procedure to upload the changed file to SharePoint.

A unique prefix at the beginning of the URI connection property is used to identify the cloud data store being targed by the connector and the remainder of the path is a relative path to the desired folder (one table per file) or single file (a single table).

Amazon S3

Set the following to identify your XML resources stored on Amazon S3:

• ConnectionType: Set the ConnectionType to Amazon S3.
• URI: Set this to an XML document in a bucket: s3://bucket1/folder1.
  • You can also connect to XML resources stored on Cloudera Ozone, after creating a volume and bucket and making a symbolic link to that bucket: s3://linktobucket

See Connecting to Amazon S3 for more information regarding how to connect and authenticate to XML files hosted on Amazon S3.

Azure Blob Storage

Set the following to identify your XML resources stored on Azure Blob Storage:

• ConnectionType: Set this to Azure Blob Storage.
• URI: Set this to the name of your container and the name of the blob. For example: azureblob://mycontainer/myblob.

See Connecting to Azure Blob Storage for more information regarding how to connect and authenticate to XML files hosted on Amazon Blob Storage.

Azure Data Lake Storage

Set the following to identify your XML resources stored on Azure Data Lake Storage:

• ConnectionType: Set this to Azure Data Lake Storage Gen1, Azure Data Lake Storage Gen2, or Azure Data Lake Storage Gen2 SSL.
• URI: Set this to the name of the file system, the name of the folder which contains your XML files, and the name of an XML file. For example:
  • Gen 1: adl://myfilesystem/folder1
  • Gen 2: abfs://myfilesystem/folder1
  • Gen 2 SSL: abfss://myfilesystem/folder1

See Connecting to Azure Data Lake Storage for more information regarding how to connect and authenticate to XML files hosted on Azure Data Lake Storage.

Azure File Storage

Set the following properties to connect:

• ConnectionType: Set this to Azure Files.
• URI: Set this the name of your azure file share and the name of the resource. For example: azurefile://fileShare/remotePath.
• AzureStorageAccount (Required): Set this to the account associated with the Azure file.

You can authenticate either an Azure access key or an Azure shared access signature. Set one of the following:

• AzureAccessKey: Set this to the access key associated with the Azure file.
• AzureSharedAccessSignature: Set this to the shared access signature associated with the Azure file.

Box

Set the following to identify your XML resources stored on Box:

• ConnectionType: Set this to Box.
• URI: Set this the name of the file system, the name of the folder which contains your XML files, and the name of an XML file. For example: box://folder1.

See Connecting to Box for more information regarding how to connect and authenticate to XML files hosted on Box.

Dropbox

Set the following to identify your XML resources stored on Dropbox:

• ConnectionType: Set this to Dropbox.
• URI: Set this to the path to a XML file. For example: dropbox://folder1.

See Connecting to Dropbox for more information regarding how to connect and authenticate to XML files hosted on Dropbox.

FTP

The connector supports both plaintext and SSL/TLS connections to FTP servers.

Set the following connection properties to connect:

• ConnectionType: Set this to either FTP or FTPS.
• URI: Set this to the address of the server followed by the path to the XML file. For example: ftp://localhost:990/folder1or ftps://localhost:990/folder1.
• User: Set this to your username on the FTP(S) server you want to connect to.
• Password: Set this to your password on the FTP(S) server you want to connect to.

Google Cloud Storage

Set the following to identify your XML resources stored on Google Cloud Storage:

• ConnectionType: Set this to Google Cloud Storage.
• URI: Set this to the path to the name of the file system, the name of the folder which contains your XML files, and the name of a XML file. For example: gs://bucket/remotePath.

See Connecting to Google Cloud Storage for more information regarding how to connect and authenticate to XML files hosted on Google Cloud Storage.

Google Drive

Set the following to identify your XML resources stored on Google Drive:

• ConnectionType: Set this to Google Drive.
• URI: Set to the path to the name of the file system, the name of the folder which contains your XML files, and the name of an XML file. For example: gdrive://folder1.

See Connecting to Google Drive for more information regarding how to connect and authenticate to XML files hosted on Google Drive.

HDFS

Set the following to identify your XML resources stored on HDFS:

• ConnectionType: Set this to HDFS or HDFS Secure.
• URI: Set this to the path to a XML file. For example:
  • HDFS: webhdfs://host:port/remotePath
  • HDFS Secure: webhdfss://host:port/remotePath
  • Cloudera Ozone (via the HttpFS gateway): webhdfs://<Ozone server>:<port>/user/myuser
    • You must use Kerberos authentication to access XML files stored on Ozone.
    • Ensure that you have Ozone 718.2.x on the Ozone cluster.
    • Cloudera Manager version 7.10.1 is required.

There are two authentication methods available for connecting to HDFS data source, Anonymous Authentication and Negotiate (Kerberos) Authentication.

Anonymous Authentication

In some situations, you can connect to HDFS without any authentication connection properties. To do so, set the AuthScheme property to None (default).

Authenticate using Kerberos

When authentication credentials are required, you can use Kerberos for authentication. See Using Kerberos for details on how to authenticate with Kerberos.

HTTP Streams

Set the following to identify your XML resources stored on HTTP streams:

• ConnectionType: Set this to HTTP or HTTPS.
• URI: Set this to the URI of your HTTP(S) stream. For example:
  • HTTP: http://remoteStream
  • HTTPS: https://remoteStream

See Connecting to HTTP Streams for more information regarding how to connect and authenticate to XML files hosted on HTTP Streams.

IBM Cloud Object Storage

Set the following to identify your XML resources stored on IBM Cloud Object Storage:

• ConnectionType: Set this to IBM Object Storage Source.
• URI: Set this to the bucket and folder. For example: ibmobjectstorage://bucket1/remotePath.
• Region: Set this property to your IBM instance region. For example: eu-gb.

See Connecting to IBM Object Storage for more information regarding how to connect and authenticate to XML files hosted on IBM Cloud Object Storage.

OneDrive

Set the following to identify your XML resources stored on OneDrive:

• ConnectionType: Set this to OneDrive.
• URI: Set this to the path to a XML file. For example: onedrive://remotePath.

See Connecting to OneDrive for more information regarding how to connect and authenticate to XML files hosted on OneDrive.

OneLake

Set the following to identify your XML resources stored on OneLake:

• ConnectionType: Set this to OneLake.
• URI: Set this to the name of the workspace, followed by the item and item type. Optionally, include the folder path to be used as the root folder. For example: onelake://Workspace/Test.LakeHouse/Files/CustomFolder.

See Connecting to OneLake for more information regarding how to connect and authenticate to XML files hosted on OneLake.

Oracle Cloud Storage

Set the following properties to authenticate with IAMSecretKey:

• ConnectionType: Set the ConnectionType to Oracle Cloud Storage.
• URI: Set this to an XML document in a bucket: os://bucket/remotePath.
• AccessKey: Set this to an Oracle Cloud Access Key.
• SecretKey: Set this to an Oracle Cloud Secret Key.
• OracleNamespace: Set this to an Oracle cloud namespace.
• Region (optional): Set this to the hosting region for your S3-like Web Services.

SFTP

Set the following to identify your XML resources stored on SFTP:

• ConnectionType: Set this to SFTP.
• URI: Set this to the address of the server followed by the path. For example: sftp://server:port/remotePath.

See Connecting to SFTP for more information regarding how to connect and authenticate to XML files hosted on SFTP.

SharePoint Online

Set the following to identify your XML resources stored on SharePoint Online:

• ConnectionType: Set this to SharePoint GRAPH, SharePoint REST V1.
• URI: Set this to a document library containing XML files. For example:
  • SharePoint Online REST: spgraph://remotePath
  • SharePoint Online REST V1: sprestv1://remotePath

    Use the Sharepoint URL as the remote path. Not the display name.

If your files are stored in a non-root-level SharePoint Online site (for example, under /sites/<your site>/), be sure to set the StorageBaseURL property to the full path of the SharePoint site.

• To access files in the top-level document library:
  • URI: Set this to spgraph://Documents/ or sprestv1://Documents/
  • StorageBaseURL: Set this to https://<your domain>.sharepoint.com/sites/<your site>/
• To access a subfolder within that site:
  • URI: Set this to spgraph://Documents/<subfolder>/ or sprestv1://Documents/<subfolder>/
  • StorageBaseURL: Set this to https://<your domain>.sharepoint.com/sites/<your site>/

Using the full SharePoint site URL ensures the connector can properly locate files stored in non-root-level locations within your organization's SharePoint Online environment.

See Connecting to SharePoint Online for more information regarding how to connect and authenticate to XML files hosted on SharePoint Online.

SharePoint On Premise

Set the following to identify your XML resources stored on SharePoint On Premise:

• ConnectionType: Set this to SharePoint REST V1, or SharePoint SOAP.
• URI: Set this to a document library containing XML files. For example:
  • SharePoint On Premise REST V1: sprestv1://remotePath
  • SharePoint On Premise SOAP: sp://remotePath

    Use the Sharepoint URL as the remote path. Not the display name.

See Connecting to SharePoint On Premise for more information regarding how to connect and authenticate to XML files hosted on SharePoint On Premise.

Securing XML Connections

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store. To specify another certificate, see the SSLServerCert property for the available formats to do so.

Java Native Interface (JNI) is a standard programming interface for writing Java native methods and embedding the Java virtual machine into native applications.

The connector leverages the JNI for improved performance on Mac and Linux.

Configure the Config INI File

The Linux and Mac editions of the XML python connector are configured with an ini file. This file is used to set several parameters, including JNI behavior. This file is to be located in:

{path_to_distribution_site-packages}/cdata/config.ini

Ensure that any configuration properties you set in the ini file fall under the following section name (adjust the 311 number if you are using an different python version from 3.11):

• For Linux:
  

  [xml.cpython-311-x86_64-linux-gnu.so]

• For Mac:
  

  [xml.cpython-311-darwin.so]

Configure the JNI connector's behavior by editing the properties in the connector's config.ini file. The connector can be configured as follows:

• LOGFILE: Set this the same way as the CDATA_LOGFILE envrionment variable below.
• JAVA_HOME: Configure the path to the JVM library location used to launch the JVM.
• CLASS_PATH: Use a colon-separated list to configure the paths to the third-party jar libraries.

Configure Environment Variables

Additionally, set the following environment variables:

• CDATA_JAVA_HOME: Configure the path to the JVM library location used to launch the JVM.
• CDATA_JVM_OPTIONS: Place JVM options here.
• CDATA_LOGFILE: Set this in the following scheme: <SCHEME>://<TAG>[|<LEVEL>]
  • SCHEME: The options are STDOUT, FILE.
    • STDOUT: Both the native wrapper and odbc core log into stdout. The Logfile and Verbosity properties can override the behavior of ODBC core.
    • FILE: The native wrapper logs into <FILENAME> while the odbc core logs into <FILENAME>.driver.log. The Logfile and Verbosity properties can override the behavior of ODBC core.
  • TAG
    • For STDOUT, set this to 1. For FILE, set this to the filename.
  • LEVEL
    • Set to one of: FATAL | ERROR | WARNING | INFO | DEBUG

The following are some examples of this syntax:

• STDOUT://1|DEBUG
• FILE:///tmp/my_py.log|DEBUG

Custom Logger

The Python connector supports a custom logging mechanism for redirecting log output to any destination, such as a cloud storage service or logging framework. Use setCustomLoggerFactory() to register a factory function that creates a logger instance for each connection.

The factory function receives the context string from the Logfile connection property (the portion after CUSTOM://) and must return an object with a writeLog(verbosity, message) method.

To enable custom logging:

1. Call setCustomLoggerFactory() with your factory function before opening connections.
2. Set Logfile to CUSTOM:// followed by a context string to identify the connection.
3. Set Verbosity to the desired log level.

The following example demonstrates a custom logger factory that creates a separate logger instance per connection:

import cdata.xml as mod
import time

class MyLogger:
    def __init__(self, loggerId):
        self.loggerId = loggerId
    def writeLog(self, verbosity, message):
        print("[MyLogger " + self.loggerId + "] " + message)

def createLogger(context):
    return MyLogger(context[len("MyLoggerId="):])

mod.setCustomLoggerFactory(createLogger)

conn1 = mod.connect("...;Logfile=CUSTOM://MyLoggerId=1;Verbosity=2;")
# do something with conn1
time.sleep(1)  # Wait for logs to flush from conn1

conn2 = mod.connect("...;Logfile=CUSTOM://MyLoggerId=2;Verbosity=2;")
# do something with conn2
time.sleep(1)  # Wait for logs to flush from conn2

After connecting to your data source, set DataModel to more closely match the data representation to the structure of your data.

Connecting to XML

Below are example connection strings to XML files or streams, using the connector's default data modeling configuration (see below)

Modeling XML Data

The DataModel property controls how your data is represented into tables.

• Document (default): Model a top-level, document view of your XML data. The connector returns nested object arrays as aggregated XML objects.
• FlattenedDocuments: Implicitly join nested array objects and parent objects into a single table.
• Relational: View nested object arrays as individual, related tables containing a primary key and a foreign key that links to the parent document.

Next Steps

• See Parsing Hierarchical Data for examples showing how to query a dataset in each DataModel configuration.
• See Modeling XML Data for information on customizing schema discovery and how to execute SQL to XML.
• See Fine-Tuning Data Access for more advanced connection settings: fine-tune the default data modeling settings, connect through a firewall, or troubleshoot connections.

Before You Connect

Obtain AWS Keys

To obtain the credentials for an IAM user:

1. Sign into the IAM console.
2. In the navigation pane, select Users.
3. To create or manage the access keys for a user, select the user and then navigate to the Security Credentials tab.

To obtain the credentials for your AWS root account:

1. Sign into the AWS Management console with the credentials for your root account.
2. Select your account name or number.
3. In the menu that displays, select My Security Credentials.
4. To manage or create root account access keys, click Continue to Security Credentials and expand the "Access Keys" section.

Connecting to Amazon S3

Specify the following to connect to data:

• AWSRegion: Set this to the region where your XML data is hosted.
• StorageBaseURL (optional): Specify the base S3 service URL only if it has a different URL from "amazonaws.com". Make sure to specify the full URL. For example: http://127.0.0.1:9000.

Authenticating to Amazon S3

There are several authentication methods available for connecting to XML including:

• Root Credentials
• AWS Role, as an AWS Role (from an EC2 Instance or by specifying the root credentials)
• SSO (ADFS, Okta, PingFederate)
• Temporary Credentials
• Credentials File
• EKS Pod Identity

Root Credentials

To authenticate using account root credentials, set these parameters:

• AuthScheme: AwsRootKeys.
• AWSAccessKey: The access key associated with the AWS root account.
• AWSSecretKey: The secret key associated with the AWS root account.

Note: Amazon discourages using root credentials for anything beyond simple testing. The account root credentials have the full permissions of the user, posing a security risk and making this the least secure authentication method.

If multi-factor authentication is required, specify the following:

• CredentialsLocation: The location of the settings file where MFA credentials are saved.
• MFASerialNumber: The serial number of the MFA device if one is being used.
• MFAToken: The temporary token available from your MFA device.

Note: If you want to control the duration of the temporary credentials, set the TemporaryTokenDuration property (default: 3600 seconds).

Using AWS From an EC2 Instance

Set AuthScheme to AwsEC2Roles.

If you are using the connector from an EC2 Instance and have an IAM Role assigned to the instance, you can use the IAM Role to authenticate. Since the connector automatically obtains your IAM Role credentials and authenticates with them, it is not necessary to specify AWSAccessKey and AWSSecretKey.

If you are also using an IAM role to authenticate, you must additionally specify the following:

• AWSRoleARN: Specify the Role ARN for the role you'd like to authenticate with. This causes the connector to attempt to retrieve credentials for the specified role.
• AWSExternalId (optional): Only required if you are assuming a role in another AWS account.

IMDSv2 Support

The XML connector now supports IMDSv2. Unlike IMDSv1, the new version requires an authentication token. Endpoints and response are the same in both versions.

In IMDSv2, the XML connector first attempts to retrieve the IMDSv2 metadata token and then uses it to call AWS metadata endpoints. If it is unable to retrieve the token, the connector reverts to IMDSv1.

AWS Web Identity

Set AuthScheme to AwsWebIdentity.

If you are either using XML from a container configured to assume role with web identity (such as a Pod in an EKS cluster with an OpenID Provider) or have authenticated with a web identity provider associated with an IAM role (and have thus obtained an identity token), you can exchange the web identity token and IAM role information for temporary security credentials to authenticate and access AWS services.

If the container has AWS_ROLE_ARN and AWS_WEB_IDENTITY_TOKEN_FILE specified in the environment variables, XML automatically obtains the credentials.

You can also authenticate by specifying both AWSRoleARN and AWSWebIdentityToken to execute the AssumeRoleWithWebIdentity API operation.

AWS IAM Roles

To authenticate as an AWS role, set these properties:

• AWSAccessKey: The access key of the IAM user to assume the role for.
• AWSSecretKey: The secret key of the IAM user to assume the role for.
• AWSRoleARN: Specify the Role ARN for the role you'd like to authenticate with. This will cause the connector to attempt to retrieve credentials for the specified role.
• AWSExternalId (optional): Only required if you are assuming a role in another AWS account.

If multi-factor authentication is required, specify the following:

• CredentialsLocation: The location of the settings file where MFA credentials are saved.
• MFASerialNumber: The serial number of the MFA device if one is being used.
• MFAToken: The temporary token available from your MFA device.

Note: If you want to control the duration of the temporary credentials, set the TemporaryTokenDuration property (default: 3600 seconds).

Note: In some circumstances it might be preferable to use an IAM role for authentication, rather than the direct security credentials of an AWS root user. If you are specifying the AWSAccessKey and AWSSecretKey of an AWS root user, you cannot use roles.

ADFS

To connect to ADFS, set these properties:

• AuthScheme: ADFS.
• User: The authenticating ADFS user.
• Password: The password of the authenticating ADFS user.
• SSOLoginURL: The SSO provider's login URL.

To authenticate to ADFS, set these SSOProperties:

• RelyingParty: The value of the ADFS server's Relying Party Identifier.

Example connection string:

AuthScheme=ADFS;User=username;Password=password;SSOLoginURL='https://sts.company.com';SSOProperties='RelyingParty=https://saml.salesforce.com';

ADFS Integrated

The ADFS Integrated flow indicates you are connecting with the user credentials of the currently logged in Windows user. To use the ADFS Integrated flow, do not specify the User and Password, but otherwise follow the same steps noted above under ADFS.

Okta

To connect to Okta, set these properties:

• AuthScheme: Okta.
• User: The authentiating Okta user.
• Password: The password of the authenticating Okta user.
• SSOLoginURL: The SSO provider's login URL.

If you are either using a trusted application or proxy that overrides the Okta client request OR configuring MFA, you must use combinations of SSOProperties to authenticate using Okta. Set any of the following, as applicable:

• APIToken: When authenticating a user via a trusted application or proxy that overrides the Okta client request context, set this to the API Token the customer created from the Okta organization.
• MFAType: If you have configured the MFA flow, set this to one of the following supported types: OktaVerify, Email, or SMS.
• MFAPassCode: If you have configured the MFA flow, set this to a valid passcode.
  If you set this to empty or an invalid value, the connector issues a one-time password challenge to your device or email. After the passcode is received, reopen the connection where the retrieved one-time password value is set to the MFAPassCode connection property.
• MFARememberDevice: True by default. Okta supports remembering devices when MFA is required. If remembering devices is allowed according to the configured authentication policies, the connector sends a device token to extend MFA authentication lifetime. If you do not want MFA to be remembered, set this variable to False.

Example connection string:

AuthScheme=Okta;SSOLoginURL='https://example.okta.com/home/appType/0bg4ivz6cJRZgCz5d6/46';User=oktaUserName;Password=oktaPassword;

PingFederate

• AuthScheme: PingFederate.
• User: The authenticating PingFederate user.
• Password: The authenticating user's PingFederate password.
• SSOLoginURL: The SSO provider's login URL.
• AWSRoleARN (optional): If you have multiple role ARNs, specify the one you want to use for authorization.
• AWSPrincipalARN (optional): If you have multiple principal ARNs, specify the one you want to use for authorization.
• SSOExchangeURL: The Partner Service Identifier URI configured in your PingFederate server instance under: SP Connections > SP Connection > WS-Trust > Protocol Settings. This should uniquely identify a PingFederate SP Connection, so it is a good idea to set it to your AWS SSO ACS URL. You can find it under AWS SSO > Settings > View Details next to the Authentication field.
• SSOProperties (optional): If you want to include your username and password as an authorization header in requests to Amazon S3, set this to Authscheme=Basic.

To enable mutual SSL authentication for SSOLoginURL, the WS-Trust STS endpoint, configure these SSOProperties:

• SSLClientCert
• SSLClientCertType
• SSLClientCertSubject
• SSLClientCertPassword

Example connection string:

authScheme=pingfederate;SSOLoginURL=https://mycustomserver.com:9033/idp/sts.wst;SSOExchangeUrl=https://us-east-1.signin.aws.amazon.com/platform/saml/acs/764ef411-xxxxxx;user=admin;password=PassValue;AWSPrincipalARN=arn:aws:iam::215338515180:saml-provider/pingFederate;AWSRoleArn=arn:aws:iam::215338515180:role/SSOTest2;

Temporary Credentials

To authenticate using temporary credentials, specify the following:

• AuthScheme: AwsTempCredentials.
• AWSAccessKey: The access key of the IAM user who will assume the role.
• AWSSecretKey: The secret key of the IAM user who will assume the role.
• AWSSessionToken: Your AWS session token, provided with your temporary credentials. For details, see AWS Identity and Access Management User Guide.

The connector can now request resources using the same permissions provided by long-term credentials (such as IAM user credentials) for the lifespan of the temporary credentials.

To authenticate using both temporary credentials and an IAM role, set all the parameters described above, and specify these additional parameters:

• AWSRoleARN: The Role ARN for the role you'd like to authenticate with. This prompts the connector to retrieve credentials for the specified role.
• AWSExternalId (optional): Only required if you are assuming a role in another AWS account.

If multi-factor authentication is required, specify the following:

• CredentialsLocation: The location of the settings file where MFA credentials are saved.
• MFASerialNumber: The serial number of the MFA device if one is being used.
• MFAToken: The temporary token available from your MFA device.

Note: If you want to control the duration of the temporary credentials, set the TemporaryTokenDuration property (default: 3600 seconds).

Credentials Files

You can use any credentials file to authenticate, including any configurations related to AccessKey/SecretKey authentication, temporary credentials, role authentication, or MFA.

To do this, set these properties:

• AuthScheme: AwsCredentialsFile.
• AWSCredentialsFile: The location of your credentials file.
• AWSCredentialsFileProfile (optional): The name of the profile you would like to use from the specified credentials file. If not specified, the default profile is used.

EKS Pod Identity

Set AuthScheme to EKSPodIdentity.

If you are running XML from a Pod in an Amazon EKS cluster configured with EKS Pod Identity, the connector automatically retrieves temporary credentials from the EKS Pod Identity Agent running on the node.

The EKS Pod Identity Agent sets the AWS_CONTAINER_CREDENTIALS_FULL_URI and AWS_CONTAINER_AUTHORIZATION_TOKEN_FILE environment variables in the Pod. The connector reads these environment variables to obtain credentials.

If the environment variables are not available, you can specify the values manually using AWSContainerCredentialsFullURI and AWSContainerAuthorizationTokenFile.

Azure AD

This configuration requires two separate Azure AD applications:

• The "XML" application used for single sign-on, and
• A custom OAuth application with user_impersonation permission on the "XML" application. (See Creating a Custom OAuth App.)

To connect to Azure AD, set the AuthScheme to AzureAD, and set these properties:

• OAuthClientId: The application Id of the connector application, listed in the Overview section of the app registration.
• OAuthClientSecret: The client secret value of the connector application. Azure AD displays this when you create a new client secret.
• CallbackURL: The redirect URI of the connector application. For example: https://localhost:33333.
• InitiateOAuth: Set this to GETANDREFRESH.

To authenticate to Azure AD, set these SSOProperties:

• Resource: The application Id URI of the XML application, listed in the app registration's Overview section. In most cases this is the URL of your custom XML domain.
• AzureTenant: The Id of the Azure AD tenant where the applications are registered.

Example connection string:

AuthScheme=AzureAD;InitiateOAuth=GETANDREFRESH;OAuthClientId=3ea1c786-d527-4399-8c3b-2e3696ae4b48;OauthClientSecret=xxx;CallbackUrl=https://localhost:33333;SSOProperties='Resource=https://signin.aws.amazon.com/saml;AzureTenant=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx';

Before You Connect

To obtain the credentials for an AzureBlob user, follow the steps below:

1. Sign into the Azure portal with the credentials for your root account.
2. Click on Storage Accounts and select the storage account you want to use.
3. Under Settings, click Access keys.
4. Your storage account name and key will be displayed on that page.

Connecting to Azure Blob Storage

Set AzureStorageAccount to your Azure Blob Storage account name.

Authenticating to Azure Blob Storage

You can authenticate to Azure Blob Storage via Access Key, Shared Access Signatures (SAS), AzureAD user, Azure MSI, or Azure Service Principal.

Access Key

Set the following to authenticate with an Azure Access Key:

• AuthScheme: Set this to AccessKey.
• AzureAccessKey: Set this to the storage key associated with your Azure Blob Storage account.

Shared Access Signature (SAS)

• AuthScheme: Set this to AzureStorageSAS.
• AzureSharedAccessSignature: Set this to the SAS associated with your Azure Blob Storage account.

1. Sign into the Azure Portal with the credentials for your root account. (https://portal.azure.com/)
2. Click storage accounts and select the storage account you want to use.
3. Under settings, click Shared Access Signature.
4. Set the permissions.
5. Specify when you want the token to expire.
6. Click Generate SAS and copy the shared access signature it generates.
7. Set AzureSharedAccessSignature to the shared access signature from the previous step.

AzureAD User

AuthScheme must be set to AzureAD in all user account flows.

Desktop Applications

Get and Refresh the OAuth Access Token

After setting the following, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• OAuthClientId (custom applications only): Set this to the client Id assigned when you registered your app.
• OAuthClientSecret (custom applications only): Set this to the client secret assigned when you registered your app.
• CallbackURL (custom application only): Set this to the redirect URI defined when you registered your app. For example: http://localhost:33333

1. The connector obtains an access token from the Microsoft identity platform and uses it to request data.
2. The OAuth values are saved in the location specified in OAuthSettingsLocation, to be persisted across connections.

Web Applications

When connecting via a Web application, you need to register a custom OAuth app with Azure Active Directory. You can then use the connector to get and manage the OAuth token values. See Creating a Custom OAuth App for more information.

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Redirect URI you specified in your app settings. The stored procedure returns the URL to the OAuth endpoint.

2. Navigate to the URL that the stored procedure returned in Step 1. Log in, and authorize the web application. After authenticating, the browser redirects you to the redirect URI. There will be a parameter called code appended to the redirect URI. Note the value of this parameter.
3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the code parameter in the query string of the redirect URI.

After you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the connector automatically refresh the OAuth access token, set the following on the first data connection.

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.
• OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
• OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: Set this to the location where you want the connector to save the OAuth values, which persist across connections.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token. Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Headless Machines

To configure the driver, use OAuth with a user account on a headless machine. You need to authenticate on another device that has an internet browser.

1. Choose one of two options:
   • Option 1: Obtain the OAuthVerifier value as described in "Obtain and Exchange a Verifier Code" below.
   • Option 2: Install the connector on a machine with an internet browser and transfer the OAuth authentication values after you authenticate through the usual browser-based flow.
2. Then configure the connector to automatically refresh the access token on the headless machine.

Option 1: Obtain and Exchange a Verifier Code

To obtain a verifier code, you must authenticate at the OAuth authorization URL.

Follow the steps below to authenticate from the machine with an internet browser and obtain the OAuthVerifier connection property.

1. Choose one of these options:
   • If you are using the Embedded OAuth Application, call the GetOAuthAuthorizationURL stored procedure. Open the URL returned by the stored procedure in a browser.
   • If you are using a custom OAuth application, set the following properties:
     • InitiateOAuth: Set to OFF.
     • OAuthClientId: Set to the client Id assigned when you registered your application.
     • OAuthClientSecret: Set to the client secret assigned when you registered your application.
     Then call the GetOAuthAuthorizationURL stored procedure with the appropriate CallbackURL. Open the URL returned by the stored procedure in a browser.
2. Log in and grant permissions to the connector. You are then redirected to the redirect URI.

There will be a parameter called code appended to the redirect URI. Note the value of this parameter. Later you will set this in the OAuthVerifier connection property.

Next, you need to exchange the OAuth verifier code for OAuth refresh and access tokens.

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

• InitiateOAuth: Set this to REFRESH.
• OAuthVerifier: Set this to the noted verifier code (the value of the code parameter in the redirect URI).
• OAuthClientId: (custom applications only) Set this to the client Id in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) Set this to the client secret in the custom OAuth application settings.
• OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified location.

Test the connection to generate the OAuth settings file, then re-set the following properties to connect:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location containing the encrypted OAuth authentication values. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Option 2: Transfer OAuth Settings

Prior to connecting on a headless machine, you need to install and create a connection with the driver on a device that supports an internet browser. Set the connection properties as described in "Desktop Applications" above.

After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

Test the connection to generate the OAuth settings file, then copy the OAuth settings file to your headless machine.

On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location of the OAuth settings file you copied from the machine with the browser. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Azure Service Principal

The authentication as an Azure Service Principal is handled via the OAuth Client Credentials flow. It does not involve direct user authentication. Instead, credentials are created for just the application itself. All tasks taken by the application are done without a default user context, but based on the assigned roles. The application access to the resources is controlled through the assigned roles' permissions.

Create an AzureAD App and an Azure Service Principal

When authenticating using an Azure Service Principal, you must create and register an Azure AD application with an Azure AD tenant. See Creating an Entra ID (Azure AD) Application for more details.

In your App Registration in portal.azure.com, navigate to API Permissions and select the Microsoft Graph permissions. There are two distinct sets of permissions: Delegated permissions and Application permissions. The permissions used during client credential authentication are under Application Permissions.

Assign a role to the application

To access resources in your subscription, you must assign a role to the application.

1. Open the Subscriptions page by searching and selecting the Subscriptions service from the search bar.
2. Select the subscription to assign the application to.
3. Open the Access control (IAM) and select Add > Add role assignment to open the Add role assignment page.
4. Select Owner as the role to assign to your created Azure AD app.

Client Secret

Set these connection properties:

• AuthScheme: AzureServicePrincipal to use a client secret.
• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• AzureTenant: The tenant you want to connect to.
• OAuthClientId: The client Id in your application settings.
• OAuthClientSecret: The client secret in your application settings.

Certificate

Set these connection properties:

• AuthScheme: AzureServicePrincipalCert to use a certificate.
• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• AzureTenant: The tenant you want to connect to.
• OAuthJWTCert: The JWT Certificate store.
• OAuthJWTCertType: The type of the certificate store specified by OAuthJWTCert.

You are now ready to connect. Authentication with client credentials takes place automatically like any other connection, except there is no window opened prompting the user. Because there is no user context, there is no need for a browser popup. Connections take place and are handled internally.

Azure MSI

If you are connecting from an Azure VM with permissions for Azure Data Lake Storage, set AuthScheme to AzureMSI.

There are two types of custom AzureAD applications: AzureAD and AzureAD with an Azure Service Principal. Both are OAuth-based.

When to Create a Custom Application

You may choose to use your own AzureAD Application Credentials when you want to

• control branding of the Authentication Dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

Custom AzureAD Applications

Create a Custom AzureAD App

Follow the steps below to obtain the AzureAD values for your application, the OAuthClientId and OAuthClientSecret.

1. Log in to https://portal.azure.com.
2. In the left-hand navigation pane, select All services. Filter and select App registrations.
3. Click New registrations.
4. Enter an application name and select the desired tenant setup. When creating a custom AzureAD application in Azure Active Directory, you can define whether the application is single- or multi-tenant. If you select the default option, "Accounts in this organizational directory only", you must set the AzureTenant connection property to the Id of the Azure AD Tenant when establishing a connection with the CData Python Connector for XML. Otherwise, the authentication attempt fails with an error. If your application is for private use only, "Accounts in this organization directory only" should be sufficient. Otherwise, if you want to distribute your application, choose one of the multi-tenant options.
5. Set the redirect url to http://localhost:33333, the connector's default. Or, specify a different port and set CallbackURL to the exact reply URL you defined.
6. Click Register to register the new application. This opens an application management screen. Note the value in Application (client) ID as the OAuthClientId and the Directory (tenant) ID as the AzureTenant.
7. Navigate to the "Certificates & Secrets" and define the application authentication type. There are two types of authentication available: using a client secret or a certificate. The recommended authentication method is using a certificate.
   • Option 1: Upload a certificate: In "Certificates & Secrets", select Upload certificate and the certificate to upload from your local machine.
   • Option 2: Create a new application secret: In "Certificates & Secrets", select New Client Secret for the application and specify its duration. After saving the client secret, the key value is displayed. Copy this value as it is displayed only once. You will need it as the OAuthClientSecret.
8. Select API Permissions > Add. If your application connects without a user context, select Application Permissions. If your application authenticates on behalf of a signed-in user, choose Delegated permissions.
9. Save your changes.
10. If you have selected to use permissions that require admin consent (such as the Application Permissions), you can grant them from the current tenant on the API Permissions page. Otherwise, follow the steps under "Admin Consent".

Custom AzureAD Service Principal Applications

When authenticating using an Azure Service Principal, you must create both a custom AzureAD application and a service principal that can access the necessary resources. Follow the steps below to create a custom AzureAD application and obtain the connection properties for Azure Service Principal authentication.

Create a Custom AzureAD App with an Azure Service Principal

Follow the steps below to obtain the AzureAD values for your application.

1. Log in to https://portal.azure.com.
2. In the left-hand navigation pane, select All services. Filter and select App registrations.
3. Click New registrations.
4. Enter an app name and select Any Azure AD Directory - Multi Tenant. Then set the redirect url to http://localhost:33333, the connector's default.
5. After creating the application, copy the Application (client) Id value displayed in the "Overview" section. This value is used as the OAuthClientId
6. Define the app authentication type by going to the "Certificates & Secrets" section. There are two types of authentication available: using a client secret and using a certificate. The recommended authentication method is via a certificate.
   • Option 1 - Upload a certificate: In "Certificates & Secrets", select Upload certificate and the certificate to upload from your local machine.
   • Option 2 - Create a new application secret: In "Certificates & Secrets", select New Client Secret for the application and specify its duration. After saving the client secret, the key value is displayed. Copy this value as it is displayed only once. You will use it as the OAuthClientSecret.
7. On the Authentication tab, make sure to select Access tokens (used for implicit flows).

Connecting to Azure Data Lake Storage

Set AzureStorageAccount to your Azure Data Lake Storage account name.

Authenticating to Azure Data Lake Storage

You can authenticate to Azure Data Lake Storage via Access Key, Shared Access Signature (SAS), AzureAD user, Azure MSI, or Azure Service Principal.

Access Key

Set the following to authenticate with an Azure Access Key:

• AuthScheme: Set this to AccessKey.
• AzureAccessKey: Set this to the storage key associated with your Azure Data Lake Storage account.

Shared Access Signature (SAS)

• AuthScheme: Set this to AzureStorageSAS.
• AzureSharedAccessSignature: Set this to the SAS associated with your Azure Blob Storage account.

1. Sign into the Azure Portal with the credentials for your root account. (https://portal.azure.com/)
2. Click storage accounts and select the storage account you want to use.
3. Under settings, click Shared Access Signature.
4. Set the permissions.
5. Specify when you want the token to expire.
6. Click Generate SAS and copy the shared access signature it generates.
7. Set AzureSharedAccessSignature to the shared access signature from the previous step.

AzureAD User

AuthScheme must be set to AzureAD in all user account flows.

Desktop Applications

Get and Refresh the OAuth Access Token

After setting the following, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• OAuthClientId (custom applications only): Set this to the client Id assigned when you registered your app.
• OAuthClientSecret (custom applications only): Set this to the client secret assigned when you registered your app.
• CallbackURL (custom application only): Set this to the redirect URI defined when you registered your app. For example: http://localhost:33333

1. The connector obtains an access token from the Microsoft identity platform and uses it to request data.
2. The OAuth values are saved in the location specified in OAuthSettingsLocation, to be persisted across connections.

Web Applications

When connecting via a Web application, you need to register a custom OAuth app with Azure Active Directory. You can then use the connector to get and manage the OAuth token values. See Creating a Custom OAuth App for more information.

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Redirect URI you specified in your app settings. The stored procedure returns the URL to the OAuth endpoint.

2. Navigate to the URL that the stored procedure returned in Step 1. Log in, and authorize the web application. After authenticating, the browser redirects you to the redirect URI. There will be a parameter called code appended to the redirect URI. Note the value of this parameter.
3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the code parameter in the query string of the redirect URI.

After you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the connector automatically refresh the OAuth access token, set the following on the first data connection.

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.
• OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
• OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: Set this to the location where you want the connector to save the OAuth values, which persist across connections.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token. Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Headless Machines

To configure the driver, use OAuth with a user account on a headless machine. You need to authenticate on another device that has an internet browser.

1. Choose one of two options:
   • Option 1: Obtain the OAuthVerifier value as described in "Obtain and Exchange a Verifier Code" below.
   • Option 2: Install the connector on a machine with an internet browser and transfer the OAuth authentication values after you authenticate through the usual browser-based flow.
2. Then configure the connector to automatically refresh the access token on the headless machine.

Option 1: Obtain and Exchange a Verifier Code

To obtain a verifier code, you must authenticate at the OAuth authorization URL.

Follow the steps below to authenticate from the machine with an internet browser and obtain the OAuthVerifier connection property.

1. Choose one of these options:
   • If you are using the Embedded OAuth Application, call the GetOAuthAuthorizationURL stored procedure. Open the URL returned by the stored procedure in a browser.
   • If you are using a custom OAuth application, set the following properties:
     • InitiateOAuth: Set to OFF.
     • OAuthClientId: Set to the client Id assigned when you registered your application.
     • OAuthClientSecret: Set to the client secret assigned when you registered your application.
     Then call the GetOAuthAuthorizationURL stored procedure with the appropriate CallbackURL. Open the URL returned by the stored procedure in a browser.
2. Log in and grant permissions to the connector. You are then redirected to the redirect URI.

There will be a parameter called code appended to the redirect URI. Note the value of this parameter. Later you will set this in the OAuthVerifier connection property.

Next, you need to exchange the OAuth verifier code for OAuth refresh and access tokens.

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

• InitiateOAuth: Set this to REFRESH.
• OAuthVerifier: Set this to the noted verifier code (the value of the code parameter in the redirect URI).
• OAuthClientId: (custom applications only) Set this to the client Id in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) Set this to the client secret in the custom OAuth application settings.
• OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified location.

Test the connection to generate the OAuth settings file, then re-set the following properties to connect:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location containing the encrypted OAuth authentication values. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Option 2: Transfer OAuth Settings

Prior to connecting on a headless machine, you need to install and create a connection with the driver on a device that supports an internet browser. Set the connection properties as described in "Desktop Applications" above.

After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

Test the connection to generate the OAuth settings file, then copy the OAuth settings file to your headless machine.

On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location of the OAuth settings file you copied from the machine with the browser. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Azure Service Principal

The authentication as an Azure Service Principal is handled via the OAuth Client Credentials flow. It does not involve direct user authentication. Instead, credentials are created for just the application itself. All tasks taken by the application are done without a default user context, but based on the assigned roles. The application access to the resources is controlled through the assigned roles' permissions.

Create an AzureAD App and an Azure Service Principal

When authenticating using an Azure Service Principal, you must create and register an Azure AD application with an Azure AD tenant. See Creating an Entra ID (Azure AD) Application for more details.

In your App Registration in portal.azure.com, navigate to API Permissions and select the Microsoft Graph permissions. There are two distinct sets of permissions: Delegated permissions and Application permissions. The permissions used during client credential authentication are under Application Permissions.

Assign a role to the application

To access resources in your subscription, you must assign a role to the application.

1. Open the Subscriptions page by searching and selecting the Subscriptions service from the search bar.
2. Select the subscription to assign the application to.
3. Open the Access control (IAM) and select Add > Add role assignment to open the Add role assignment page.
4. Select Owner as the role to assign to your created Azure AD app.

Client Secret

Set these connection properties:

• AuthScheme: AzureServicePrincipal to use a client secret.
• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• AzureTenant: The tenant you want to connect to.
• OAuthClientId: The client Id in your application settings.
• OAuthClientSecret: The client secret in your application settings.

Certificate

Set these connection properties:

• AuthScheme: AzureServicePrincipalCert to use a certificate.
• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• AzureTenant: The tenant you want to connect to.
• OAuthJWTCert: The JWT Certificate store.
• OAuthJWTCertType: The type of the certificate store specified by OAuthJWTCert.

You are now ready to connect. Authentication with client credentials takes place automatically like any other connection, except there is no window opened prompting the user. Because there is no user context, there is no need for a browser popup. Connections take place and are handled internally.

Azure MSI

If you are connecting from an Azure VM with permissions for Azure Data Lake Storage, set AuthScheme to AzureMSI.

There are two types of custom AzureAD applications: AzureAD and AzureAD with an Azure Service Principal. Both are OAuth-based.

When to Create a Custom Application

You may choose to use your own AzureAD Application Credentials when you want to

• control branding of the Authentication Dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

Custom AzureAD Applications

Create a Custom AzureAD App

Follow the steps below to obtain the AzureAD values for your application, the OAuthClientId and OAuthClientSecret.

1. Log in to https://portal.azure.com.
2. In the left-hand navigation pane, select All services. Filter and select App registrations.
3. Click New registrations.
4. Enter an application name and select the desired tenant setup. When creating a custom AzureAD application in Azure Active Directory, you can define whether the application is single- or multi-tenant. If you select the default option, "Accounts in this organizational directory only", you must set the AzureTenant connection property to the Id of the Azure AD Tenant when establishing a connection with the CData Python Connector for XML. Otherwise, the authentication attempt fails with an error. If your application is for private use only, "Accounts in this organization directory only" should be sufficient. Otherwise, if you want to distribute your application, choose one of the multi-tenant options.
5. Set the redirect url to http://localhost:33333, the connector's default. Or, specify a different port and set CallbackURL to the exact reply URL you defined.
6. Click Register to register the new application. This opens an application management screen. Note the value in Application (client) ID as the OAuthClientId and the Directory (tenant) ID as the AzureTenant.
7. Navigate to the "Certificates & Secrets" and define the application authentication type. There are two types of authentication available: using a client secret or a certificate. The recommended authentication method is using a certificate.
   • Option 1: Upload a certificate: In "Certificates & Secrets", select Upload certificate and the certificate to upload from your local machine.
   • Option 2: Create a new application secret: In "Certificates & Secrets", select New Client Secret for the application and specify its duration. After saving the client secret, the key value is displayed. Copy this value as it is displayed only once. You will need it as the OAuthClientSecret.
8. Select API Permissions > Add. If your application connects without a user context, select Application Permissions. If your application authenticates on behalf of a signed-in user, choose Delegated permissions.
9. Save your changes.
10. If you have selected to use permissions that require admin consent (such as the Application Permissions), you can grant them from the current tenant on the API Permissions page. Otherwise, follow the steps under "Admin Consent".

Custom AzureAD Service Principal Applications

When authenticating using an Azure Service Principal, you must create both a custom AzureAD application and a service principal that can access the necessary resources. Follow the steps below to create a custom AzureAD application and obtain the connection properties for Azure Service Principal authentication.

Create a Custom AzureAD App with an Azure Service Principal

Follow the steps below to obtain the AzureAD values for your application.

1. Log in to https://portal.azure.com.
2. In the left-hand navigation pane, select All services. Filter and select App registrations.
3. Click New registrations.
4. Enter an app name and select Any Azure AD Directory - Multi Tenant. Then set the redirect url to http://localhost:33333, the connector's default.
5. After creating the application, copy the Application (client) Id value displayed in the "Overview" section. This value is used as the OAuthClientId
6. Define the app authentication type by going to the "Certificates & Secrets" section. There are two types of authentication available: using a client secret and using a certificate. The recommended authentication method is via a certificate.
   • Option 1 - Upload a certificate: In "Certificates & Secrets", select Upload certificate and the certificate to upload from your local machine.
   • Option 2 - Create a new application secret: In "Certificates & Secrets", select New Client Secret for the application and specify its duration. After saving the client secret, the key value is displayed. Copy this value as it is displayed only once. You will use it as the OAuthClientSecret.
7. On the Authentication tab, make sure to select Access tokens (used for implicit flows).

Connecting to Box

Use the OAuth authentication standard to connect to Box. You can authenticate with a user account or with a service account. A service account is required to grant organization-wide access scopes to the connector. The connector facilitates these authentication flows as described below.

User Accounts (OAuth)

AuthScheme must be set to OAuth in all user account flows.

Desktop Applications

Get and Refresh the OAuth Access Token

After setting the following, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• OAuthClientId (custom applications only): Set this to the client Id assigned when you registered your app.
• OAuthClientSecret (custom applications only): Set this to the client secret assigned when you registered your app.
• CallbackURL (custom application only): Set this to the redirect URI defined when you registered your app. For example: http://localhost:33333

1. The connector obtains an access token from Box and uses it to request data.
2. The OAuth values are saved in the location specified in OAuthSettingsLocation, to be persisted across connections.

Web Applications

When connecting via a Web application, you need to register a custom OAuth app with Box. You can then use the connector to get and manage the OAuth token values. See Create a Custom OAuth App for more information.

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Redirect URI you specified in your app settings. The stored procedure returns the URL to the OAuth endpoint.

2. Navigate to the URL that the stored procedure returned in Step 1. Log in, and authorize the web application. After authenticating, the browser redirects you to the redirect URI. There will be a parameter called code appended to the redirect URI. Note the value of this parameter.
3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the code parameter in the query string of the redirect URI.

After you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the connector automatically refresh the OAuth access token, set the following on the first data connection.

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.
• OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
• OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: Set this to the location where you want the connector to save the OAuth values, which persist across connections.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token. Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Headless Machines

To configure the driver, use OAuth with a user account on a headless machine. You need to authenticate on another device that has an internet browser.

1. Choose one of two options:
   • Option 1: Obtain the OAuthVerifier value as described in "Obtain and Exchange a Verifier Code" below.
   • Option 2: Install the connector on a machine with an internet browser and transfer the OAuth authentication values after you authenticate through the usual browser-based flow.
2. Then configure the connector to automatically refresh the access token on the headless machine.

Option 1: Obtain and Exchange a Verifier Code

To obtain a verifier code, you must authenticate at the OAuth authorization URL.

Follow the steps below to authenticate from the machine with an internet browser and obtain the OAuthVerifier connection property.

1. Choose one of these options:
   • If you are using the Embedded OAuth Application, call the GetOAuthAuthorizationURL stored procedure. Open the URL returned by the stored procedure in a browser.
   • If you are using a custom OAuth application, set the following properties:
     • InitiateOAuth: Set to OFF.
     • OAuthClientId: Set to the client Id assigned when you registered your application.
     • OAuthClientSecret: Set to the client secret assigned when you registered your application.
     Then call the GetOAuthAuthorizationURL stored procedure with the appropriate CallbackURL. Open the URL returned by the stored procedure in a browser.
2. Log in and grant permissions to the connector. You are then redirected to the redirect URI. There will be a parameter called code appended to the redirect URI. Note the value of this parameter. Later you will set this in the OAuthVerifier connection property.

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

• InitiateOAuth: Set this to REFRESH.
• OAuthVerifier: Set this to the noted verifier code (the value of the code parameter in the redirect URI).
• OAuthClientId: (custom applications only) Set this to the client Id in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) Set this to the client secret in the custom OAuth application settings.
• OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified location.

Test the connection to generate the OAuth settings file, then re-set the following properties to connect:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location containing the encrypted OAuth authentication values. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Option 2: Transfer OAuth Settings

Prior to connecting on a headless machine, you need to install and create a connection with the driver on a device that supports an internet browser. Set the connection properties as described in "Desktop Applications" above.

After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

Test the connection to generate the OAuth settings file, then copy the OAuth settings file to your headless machine.

On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location of the OAuth settings file you copied from the machine with the browser. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Authenticate with a Service Account

Set the AuthScheme to OAuthJWT to authenticate with this method.

Service accounts have silent authentication, without user authentication in the browser. You can also use a service account to delegate enterprise-wide access scopes to the connector.

You need to create an OAuth application in this flow. See Create a Custom OAuth App to create and authorize an app. You can then connect to Box data that the service account has permission to access.

After setting the following connection properties, you are ready to connect:

• InitiateOAuth: Set to GETANDREFRESH.
• OAuthClientId: Set to the Client Id in your app settings.
• OAuthClientSecret: Set to the Client Secret in your app settings.
• OAuthJWTCertType: Set to "PEMKEY_FILE".
• OAuthJWTCert: Set to the path to the .pem file you generated.
• OAuthJWTCertPassword: Set to the password of the .pem file.
• OAuthJWTCertSubject: Set to "*" to pick the first certificate in the certificate store.
• OAuthJWTSubjectType: Set to "enterprise" or "user" depending on the Application Access Value you selected in your app settings. The default value of this connection property is "enterprise".
• OAuthJWTSubject: Set to your enterprise Id if your subject type is set to "enterprise" or your app user Id if your subject type is set to "user".
• OAuthJWTPublicKeyId: Set to the Id of your public key in your app settings.

1. Creates and signs the JWT with the claim set required by the connector.
2. Exchanges the JWT for the access token.
3. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.
4. Submits the JWT for a new access token when the token expires.

Creating a Custom OAuth Application

You may choose to use your own OAuth Application Credentials when you want to:

• control branding of the authentication dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

Procedure

At the Box Enterprise Developer Console:

1. Log in to your Box developers dashboard.
2. Click Create New App.
3. Specify basic application information, as appropriate.
4. Specify your application type (e.g., Custom App).
5. Select the User Authentication (OAuth 2.0) authentication method.
6. Set the Redirect URI:
   • If this is a desktop application or headless machine application, set the Redirect URI to http://localhost:33333 or a different port number.
   • If this is a web application, set the Redirect URI to https://<yourwebappserver>:<port>.
7. Click Create App.
8. The next task is to create a public and private key pair.
   • To create a keypair from the Developer Console:
     1. Navigate to the Developer Console Configuration tab.
     2. Scroll down to Add and Manage Public Keys.
     3. Click Generate a Public/Private Keypair. Box creates a keypair in a JSON file, and downloads that file to your desktop. You can then move that file to your application code.

        Note: Box does not back up private keys for security reasons. Be careful to back up the Public/Private JSON file. If you lose your private key, you must reset the entire keypair.

   • To add a keypair manually:
     1. Open a terminal window and run the following OpenSSL commands:
        

        openssl genrsa -des3 -out private.pem 2048
        openssl rsa -in private.pem -outform PEM -pubout -out public.pem

        Note: To run OpenSSL in a Windows environment, install the Cygwin package.

     2. At the Developer Console, navigate to the configuration tab for the Custom OAuth application you just created.
     3. Scroll down to Add and Manage Public Keys.
     4. Click Add a Public Key.
     5. Click Verify and Save.
9. Before the custom application can be used, a Box Admin must authorize it within the Box Admin Console.
   1. Navigate to your application within the Developer Console.
   2. Click the Authorization tab.
   3. At the prompt to Submit app for authorization for access to the Enterprise, click Review and Submit.
      Your Box Enterprise Admin approves the application.
10. Finally, select the scope of user permissions your custom OAuth application must request.

After your application is created and registered, click Configuration from the main menu to access your settings. Note the displayed Redirect URI, Client ID, and Client Secret. You will need these values later.

When JWT Access Scopes Change

If you change the JWT access scopes, you must reauthorize the application in the enterprise admin console:

1. Click Apps in the main manu.
2. Select the ellipsis button next to your JWT application name.
3. Select Reauthorize App in the menu.

Connecting to Dropbox

Dropbox uses the OAuth authentication standard.

Dropbox OAuth Scopes

You need to choose between using CData's embedded OAuth app or Create a Custom OAuth App.

The embedded app includes the following scopes:

• account_info.read
• file_requests.read
• files.content.read
• files.content.write
• files.metadata.read
• sharing.read
• sharing.write

Desktop Applications

Get and Refresh the OAuth Access Token

After setting the following, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• OAuthClientId (custom applications only): Set this to the client Id assigned when you registered your app.
• OAuthClientSecret (custom applications only): Set this to the client secret assigned when you registered your app.
• CallbackURL (custom application only): Set this to the redirect URI defined when you registered your app. For example: http://localhost:33333

1. The connector obtains an access token from Dropbox and uses it to request data.
2. The OAuth values are saved in the location specified in OAuthSettingsLocation, to be persisted across connections.

Web Applications

When connecting via a Web application, you need to register a custom OAuth app with Dropbox. You can then use the connector to get and manage the OAuth token values. See Create a Custom OAuth App for more information.

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the noted App key value from your OAuth app settings.
• OAuthClientSecret: Set this to the App secret from your OAuth app settings.

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Redirect URI you specified in your app settings. The stored procedure returns the URL to the OAuth endpoint.

2. Navigate to the URL that the stored procedure returned in Step 1. Log in, and authorize the web application. After authenticating, the browser redirects you to the redirect URI. There will be a parameter called code appended to the redirect URI. Note the value of this parameter.
3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the code parameter in the query string of the redirect URI.

After you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the connector automatically refresh the OAuth access token, set the following on the first data connection.

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the noted App key value from your OAuth app settings.
• OAuthClientSecret: Set this to the App secret from your OAuth app settings.
• OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
• OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: Set this to the location where you want the connector to save the OAuth values, which persist across connections.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token. Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

• OAuthClientId: Set this to the noted App key value from your OAuth app settings.
• OAuthClientSecret: Set this to the App secret from your OAuth app settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Headless Machines

To configure the driver, use OAuth with a user account on a headless machine. You need to authenticate on another device that has an internet browser.

1. Choose one of two options:
   • Option 1: Obtain the OAuthVerifier value as described in "Obtain and Exchange a Verifier Code" below.
   • Option 2: Install the connector on a machine with an internet browser and transfer the OAuth authentication values after you authenticate through the usual browser-based flow.
2. Then configure the connector to automatically refresh the access token on the headless machine.

Option 1: Obtain and Exchange a Verifier Code

To obtain a verifier code, you must authenticate at the OAuth authorization URL.

Follow the steps below to authenticate from the machine with an internet browser and obtain the OAuthVerifier connection property.

1. Choose one of these options:
   • If you are using the Embedded OAuth Application, call the GetOAuthAuthorizationURL stored procedure. Open the URL returned by the stored procedure in a browser.
   • If you are using a custom OAuth application, set the following properties:
     • InitiateOAuth: Set to OFF.
     • OAuthClientId: Set this to the noted App key value from your OAuth app settings.
     • OAuthClientSecret: Set this to the App secret from your OAuth app settings.
     Then call the GetOAuthAuthorizationURL stored procedure with the appropriate CallbackURL. Open the URL returned by the stored procedure in a browser.
2. Log in and grant permissions to the connector. You are then redirected to the redirect URI. There will be a parameter called code appended to the redirect URI. Note the value of this parameter. Later you will set this in the OAuthVerifier connection property.

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

• InitiateOAuth: Set this to REFRESH.
• OAuthVerifier: Set this to the noted verifier code (the value of the code parameter in the redirect URI).
• OAuthClientId: (custom applications only) Set this to the noted App key value from your OAuth app settings.
• OAuthClientSecret: (custom applications only) Set this to the App secret from your OAuth app settings.
• OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified location.

Test the connection to generate the OAuth settings file, then re-set the following properties to connect:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the noted App key value from your OAuth app settings.
• OAuthClientSecret: (custom applications only) Set this to the App secret from your OAuth app settings.
• OAuthSettingsLocation: Set this to the location containing the encrypted OAuth authentication values. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Option 2: Transfer OAuth Settings

Prior to connecting on a headless machine, you need to install and create a connection with the driver on a device that supports an internet browser. Set the connection properties as described in "Desktop Applications" above.

After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

Test the connection to generate the OAuth settings file, then copy the OAuth settings file to your headless machine.

On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the noted App key value from your OAuth app settings.
• OAuthClientSecret: (custom applications only) Set this to the App secret from your OAuth app settings.
• OAuthSettingsLocation: Set this to the location of the OAuth settings file you copied from the machine with the browser. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

When To Create a Custom OAuth Application

You may choose to use your own OAuth Application Credentials when you want to

• control branding of the Authentication Dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

Create a Custom OAuth App

1. Log in to your Dropbox developers dashboard and click Create New App. Select the Dropbox API type. Select the Full Dropbox access for your app.
2. After creating your app, you can view Configuration from the main menu that displays your app settings.
3. On the app Settings tab, note the values of App key and App secret for later connector configuration.
4. Set the Redirect URI and store the specified value for later connector configuration.
   • When setting up a desktop app or headless app, set the Redirect URI to http://localhost:33333 or a different port number.
   • When setting up a web app, set the Redirect URI to https://<yourwebappserver>:<port>.
5. On the app Permissions tab, select the scope of user permissions your app will request.

No further values need to be specified in the XML app settings.

Connecting to Google Cloud Storage

Set the ProjectId property to the Id of the project you want to connect to.

Authenticating to Google Cloud Storage

The connector supports using user accounts, service accounts and GCP instance accounts for authentication.

The following sections discuss the available authentication schemes for Google Cloud Storage:

• User Accounts (OAuth)
• Service Account (OAuthJWT)
• GCP Instance Account

User Accounts (OAuth)

AuthScheme must be set to OAuth in all user account flows.

Desktop Applications

CData provides an embedded OAuth application that simplifies OAuth desktop Authentication. Alternatively, you can create a custom OAuth application. See Create a Custom OAuth App for information about creating custom applications and reasons for doing so.

For authentication, the only difference between the two methods is that you must set two additional connection properties when using custom OAuth applications.

After setting the following connection properties, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH, which instructs the connector to automatically attempt to get and refresh the OAuth access token.
• OAuthClientId: (custom applications only) Set this to the Client Id in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) Set this to the Client Secret in the custom OAuth application settings.

• Extracts the access token from the callback URL.
• Obtains a new access token when the old one expires.
• Saves OAuth values in OAuthSettingsLocation that persist across connections.

Web Applications

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Callback URL you specified in your application settings. The stored procedure returns the URL to the OAuth endpoint.
2. Navigate to the URL that the stored procedure returned in Step 1. Log in to the custom OAuth application and authorize the web application. Once authenticated, the browser redirects you to the callback URL.
3. Call the GetOAuthAccessToken stored procedure. Set AuthMode to WEB and the Verifier input to the "code" parameter in the query string of the callback URL.

Once you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the driver automatically refresh the OAuth access token, set the following on the first data connection:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.
• OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
• OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: Set this to the location where the connector saves the OAuth token values, which persist across connections.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token.

Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Headless Machines

1. Choose one of two options:
   • Option 1: Obtain the OAuthVerifier value as described in "Obtain and Exchange a Verifier Code" below.
   • Option 2: Install the connector on a machine with an internet browser and transfer the OAuth authentication values after you authenticate through the usual browser-based flow.
2. Then configure the connector to automatically refresh the access token on the headless machine.

Option 1: Obtain and Exchange a Verifier Code

To obtain a verifier code, you must authenticate at the OAuth authorization URL.

Follow the steps below to authenticate from the machine with an internet browser and obtain the OAuthVerifier connection property.

1. Choose one of these options:
   • If you are using the Embedded OAuth Application click Google Cloud Storage OAuth endpoint to open the endpoint in your browser.
   • If you are using a custom OAuth application, create the Authorization URL by setting the following properties:
     • InitiateOAuth: Set to OFF.
     • OAuthClientId: Set to the client Id assigned when you registered your application.
     • OAuthClientSecret: Set to the client secret assigned when you registered your application.
     Then call the GetOAuthAuthorizationURL stored procedure with the appropriate CallbackURL. Open the URL returned by the stored procedure in a browser.
2. Log in and grant permissions to the connector. You are then redirected to the callback URL, which contains the verifier code.
3. Save the value of the verifier code. Later you will set this in the OAuthVerifier connection property.

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

• InitiateOAuth: Set this to REFRESH.
• OAuthVerifier: Set this to the verifier code.
• OAuthClientId: (custom applications only) Set this to the Client Id in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) Set this to the Client Secret in the custom OAuth application settings.
• OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified location.

After the OAuth settings file is generated, you need to re-set the following properties to connect:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location containing the encrypted OAuth authentication values. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Option 2: Transfer OAuth Settings

Prior to connecting on a headless machine, you need to create and install a connection with the driver on a device that supports an internet browser. Set the connection properties as described in "Desktop Applications" above.

After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

Once you have successfully tested the connection, copy the OAuth settings file to your headless machine.

On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location of your OAuth settings file. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Service Accounts (OAuthJWT)

To authenticate using a service account, you must create a new service account and have a copy of the accounts certificate. If you do not already have a service account, you can create one by following the procedure in Create a Custom OAuth App.

For a JSON file, set these properties:

• AuthScheme: Set this to OAuthJWT.
• InitiateOAuth: Set this to GETANDREFRESH.
• OAuthJWTCertType: Set this to GOOGLEJSON.
• OAuthJWTCert: Set this to the path to the .json file provided by Google.
• OAuthJWTSubject: (optional) Only set this value if the service account is part of a GSuite domain and you want to enable delegation. The value of this property should be the email address of the user whose data you want to access.

For a PFX file, set these properties instead:

• AuthScheme: Set this to OAuthJWT.
• InitiateOAuth: Set this to GETANDREFRESH.
• OAuthJWTCertType: Set this to PFXFILE.
• OAuthJWTCert: Set this to the path to the .pfx file provided by Google.
• OAuthJWTCertPassword: (optional) Set this to the .pfx file password. In most cases you must provide this since Google encrypts PFX certificates.
• OAuthJWTCertSubject: (optional) Set this only if you are using a OAuthJWTCertType which stores multiple certificates. Should not be set for PFX certificates generated by Google.
• OAuthJWTIssuer: Set this to the email address of the service account. This address will usually include the domain iam.gserviceaccount.com.
• OAuthJWTSubject: (optional) Only set this value if the service account is part of a GSuite domain and you want to enable delegation. The value of this property should be the email address of the user whose data you want to access.

GCP Instance Accounts

When running on a GCP virtual machine, the connector can authenticate using a service account tied to the virtual machine. To use this mode, set AuthScheme to GCPInstanceAccount.

Creating a Custom OAuth Application

(For information on getting and setting the OAuthAccessToken and other configuration parameters, see the Desktop Authentication section of "Connecting to XML".)

However, you must create a custom OAuth application to connect to XML via the Web. And since custom OAuth applications seamlessly support all three commonly-used auth flows, you might want to create custom OAuth applications (use your own OAuth Application Credentials) for those auth flows anyway.

Custom OAuth applications are useful if you want to:

• control branding of the authentication dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

The following sections describe how to enable the Directory API and create custom OAuth applications for user accounts (OAuth) and Service Accounts (OAuth/JWT).

Enable the Cloud Storage API

1. Navigate to the Google Cloud Console.
2. Select Library from the left-hand navigation menu. This opens the Library page.
3. In the search field, enter "Cloud Storage API" and select Cloud Storage API from the search results.
4. On the Cloud Storage API page, click ENABLE.

Create an OAuth Application

User Accounts (OAuth)

For users whose AuthScheme is OAuth and who need to authenticate over a web application, you must always create a custom OAuth application. (For desktop and headless flows, creating a custom OAuth application is optional.)

Do the following:

1. Navigate to the Google Cloud Console.
2. Create a new project or select an existing project.
3. At the left-hand navigation menu, select Credentials.
4. If this project does not already have a consent screen configured, click CONFIGURE CONSENT SCREEN to create one. If you are not using a Google Workspace account, you are restricted to creating an External-type Consent Screen, which requires specifying a support email and developer contact email. Additional info is optional.
5. On the Credentials page, select Create Credentials > OAuth Client ID.
6. In the Application Type menu, select Web application.
7. Specify a name for your custom OAuth application.
8. Under Authorized redirect URIs, click ADD URI and enter a redirect URI.
9. Click Enter, then CREATE. The Cloud Console returns you to the Credentials page.
   A window opens that displays your client Id and client secret.
10. Record the client Id and Client Secret for later use as the OAuthClientId and OAuthClientSecret connection properties.

Note: The client secret remains accessible from from the Google Cloud Console.

Service Accounts (OAuthJWT)

To create a new service account:

1. Navigate to the Google Cloud Console.
2. Create a new project or select an existing project.
3. At the left-hand navigation menu, select Credentials.
4. Select Create Credentials > Service account.
5. On the Create service account page, enter the service account name, ID, and an optional description.
6. Click DONE. The Cloud Console redisplays the Credentials page.
7. In the Service Accounts section, select the service account you just created.
8. Click the Advanced Settings section and enable Domain-Wide Delegation.
9. Record the Client ID shown for domain-wide delegation. You'll use this in the Admin Console.
10. In a new tab, navigate to the Google Admin Console.
11. Go to Security > API Controls > Domain-Wide Delegation.
12. Click Manage Domain-Wide Delegation, then Add new.
13. Enter the recorded Client ID and the list of required scopes. See OAuth Scopes and Endpoints for more details.
14. Back in the Cloud Console, select the KEYS tab for the service account.
15. Click ADD KEY > Create new key.
16. Select a supported key type (see OAuthJWTCert and OAuthJWTCertType).
17. Click CREATE. The key is automatically downloaded to your device.
18. Record the additional information for later use.

In the service account flow, the connector exchanges a JSON Web Token (JWT) for the OAuthAccessToken. The private key downloaded in the steps above is used to sign the JWT. The connector inherits the permissions granted to the service account, including any scopes configured through domain-wide delegation.

Authenticating to Google Drive

The connector supports using user accounts, service accounts and GCP instance accounts for authentication.

The following sections discuss the available authentication schemes for Google Drive:

• User Accounts (OAuth)
• Service Account (OAuthJWT)
• GCP Instance Account

User Accounts (OAuth)

AuthScheme must be set to OAuth in all user account flows.

Desktop Applications

CData provides an embedded OAuth application that simplifies OAuth desktop Authentication. Alternatively, you can create a custom OAuth application. See Create a Custom OAuth App for information about creating custom applications and reasons for doing so.

For authentication, the only difference between the two methods is that you must set two additional connection properties when using custom OAuth applications.

After setting the following connection properties, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH, which instructs the connector to automatically attempt to get and refresh the OAuth access token.
• OAuthClientId: (custom applications only) Set this to the Client Id in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) Set this to the Client Secret in the custom OAuth application settings.

• Extracts the access token from the callback URL.
• Obtains a new access token when the old one expires.
• Saves OAuth values in OAuthSettingsLocation that persist across connections.

Web Applications

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Callback URL you specified in your application settings. The stored procedure returns the URL to the OAuth endpoint.
2. Navigate to the URL that the stored procedure returned in Step 1. Log in to the custom OAuth application and authorize the web application. Once authenticated, the browser redirects you to the callback URL.
3. Call the GetOAuthAccessToken stored procedure. Set AuthMode to WEB and the Verifier input to the "code" parameter in the query string of the callback URL.

Once you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the driver automatically refresh the OAuth access token, set the following on the first data connection:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.
• OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
• OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: Set this to the location where the connector saves the OAuth token values, which persist across connections.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token.

Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Headless Machines

1. Choose one of two options:
   • Option 1: Obtain the OAuthVerifier value as described in "Obtain and Exchange a Verifier Code" below.
   • Option 2: Install the connector on a machine with an Internet browser and transfer the OAuth authentication values after you authenticate through the usual browser-based flow.
2. Then configure the connector to automatically refresh the access token on the headless machine.

Option 1: Obtain and Exchange a Verifier Code

To obtain a verifier code, you must authenticate at the OAuth authorization URL.

Follow the steps below to authenticate from the machine with an Internet browser and obtain the OAuthVerifier connection property.

1. Choose one of these options:
   • If you are using the Embedded OAuth Application, call the GetOAuthAuthorizationURL stored procedure. Open the URL returned by the stored procedure in a browser.
   • If you are using a custom OAuth application, create the Authorization URL by setting the following properties:
     • InitiateOAuth: Set to OFF.
     • OAuthClientId: Set to the client Id assigned when you registered your application.
     • OAuthClientSecret: Set to the client secret assigned when you registered your application.
     Then call the GetOAuthAuthorizationURL stored procedure with the appropriate CallbackURL. Open the URL returned by the stored procedure in a browser.
2. Log in and grant permissions to the connector. You are then redirected to the callback URL, which contains the verifier code.
3. Save the value of the verifier code. Later you will set this in the OAuthVerifier connection property.

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

• InitiateOAuth: Set this to REFRESH.
• OAuthVerifier: Set this to the verifier code.
• OAuthClientId: (custom applications only) Set this to the Client Id in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) Set this to the Client Secret in the custom OAuth application settings.
• OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified location.

After the OAuth settings file is generated, you need to re-set the following properties to connect:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location containing the encrypted OAuth authentication values. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Option 2: Transfer OAuth Settings

Prior to connecting on a headless machine, you need to create and install a connection with the driver on a device that supports an Internet browser. Set the connection properties as described in "Desktop Applications" above.

After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

Once you have successfully tested the connection, copy the OAuth settings file to your headless machine.

On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location of your OAuth settings file. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Service Accounts (OAuthJWT)

To authenticate using a service account, you must create a new service account and have a copy of the accounts certificate. If you do not already have a service account, you can create one by following the procedure in Create a Custom OAuth App.

For a JSON file, set these properties:

• AuthScheme: Set this to OAuthJWT.
• InitiateOAuth: Set this to GETANDREFRESH.
• OAuthJWTCertType: Set this to GOOGLEJSON.
• OAuthJWTCert: Set this to the path to the .json file provided by Google.
• OAuthJWTSubject: (optional) Only set this value if the service account is part of a GSuite domain and you want to enable delegation. The value of this property should be the email address of the user whose data you want to access.

For a PFX file, set these properties instead:

• AuthScheme: Set this to OAuthJWT.
• InitiateOAuth: Set this to GETANDREFRESH.
• OAuthJWTCertType: Set this to PFXFILE.
• OAuthJWTCert: Set this to the path to the .pfx file provided by Google.
• OAuthJWTCertPassword: (optional) Set this to the .pfx file password. In most cases you must provide this since Google encrypts PFX certificates.
• OAuthJWTCertSubject: (optional) Set this only if you are using a OAuthJWTCertType which stores multiple certificates. Should not be set for PFX certificates generated by Google.
• OAuthJWTIssuer: Set this to the email address of the service account. This address will usually include the domain iam.gserviceaccount.com.
• OAuthJWTSubject: (optional) Only set this value if the service account is part of a GSuite domain and you want to enable delegation. The value of this property should be the email address of the user whose data you want to access.

GCP Instance Accounts

When running on a GCP virtual machine, the connector can authenticate using a service account tied to the virtual machine. To use this mode, set AuthScheme to GCPInstanceAccount.

Creating a Custom OAuth Application

(For information on getting and setting the OAuthAccessToken and other configuration parameters, see the Desktop Authentication section of "Connecting to XML".)

However, you must create a custom OAuth application to connect to XML via the Web. And since custom OAuth applications seamlessly support all three commonly-used auth flows, you might want to create custom OAuth applications (use your own OAuth Application Credentials) for those auth flows anyway.

Custom OAuth applications are useful if you want to:

• control branding of the authentication dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

The following sections describe how to enable the Directory API and create custom OAuth applications for user accounts (OAuth) and Service Accounts (OAuth/JWT).

Enable the Google Drive API

1. Navigate to the Google Cloud Console.
2. Select Library from the left-hand navigation menu. This opens the Library page.
3. In the search field, enter "Google Drive API" and select Google Drive API from the search results.
4. On the Google Drive API page, click ENABLE.

Create an OAuth Application

User Accounts (OAuth)

For users whose AuthScheme is OAuth and who need to authenticate over a web application, you must always create a custom OAuth application. (For desktop and headless flows, creating a custom OAuth application is optional.)

Do the following:

1. Navigate to the Google Cloud Console.
2. Create a new project or select an existing project.
3. At the left-hand navigation menu, select Credentials.
4. If this project does not already have a consent screen configured, click CONFIGURE CONSENT SCREEN to create one. If you are not using a Google Workspace account, you are restricted to creating an External-type Consent Screen, which requires specifying a support email and developer contact email. Additional info is optional.
5. On the Credentials page, select Create Credentials > OAuth Client ID.
6. In the Application Type menu, select Web application.
7. Specify a name for your custom OAuth application.
8. Under Authorized redirect URIs, click ADD URI and enter a redirect URI.
9. Click Enter, then CREATE. The Cloud Console returns you to the Credentials page.
   A window opens that displays your client Id and client secret.
10. Record the client Id and Client Secret for later use as the OAuthClientId and OAuthClientSecret connection properties.

Note: The client secret remains accessible from from the Google Cloud Console.

Service Accounts (OAuthJWT)

To create a new service account:

1. Navigate to the Google Cloud Console.
2. Create a new project or select an existing project.
3. At the left-hand navigation menu, select Credentials.
4. Select Create Credentials > Service account.
5. On the Create service account page, enter the service account name, ID, and an optional description.
6. Click DONE. The Cloud Console redisplays the Credentials page.
7. In the Service Accounts section, select the service account you just created.
8. Click the Advanced Settings section and enable Domain-Wide Delegation.
9. Record the Client ID shown for domain-wide delegation. You'll use this in the Admin Console.
10. In a new tab, navigate to the Google Admin Console.
11. Go to Security > API Controls > Domain-Wide Delegation.
12. Click Manage Domain-Wide Delegation, then Add new.
13. Enter the recorded Client ID and the list of required scopes. See OAuth Scopes and Endpoints for more details.
14. Back in the Cloud Console, select the KEYS tab for the service account.
15. Click ADD KEY > Create new key.
16. Select a supported key type (see OAuthJWTCert and OAuthJWTCertType).
17. Click CREATE. The key is automatically downloaded to your device.
18. Record the additional information for later use.

In the service account flow, the connector exchanges a JSON Web Token (JWT) for the OAuthAccessToken. The private key downloaded in the steps above is used to sign the JWT. The connector inherits the permissions granted to the service account, including any scopes configured through domain-wide delegation.

Authenticating to HTTP(S)

The connector generically supports connecting to XML data stored on HTTP(S) streams.

Several authentication methods, such as user/password, digest access, OAuth, OAuthJWT, and OAuth PASSWORD flow are supported.

You can also connect to streams that have no authentication set up.

No Authentication

Connect to an HTTP(S) stream with no authentication by setting the AuthScheme connection property to None.

Basic

Set the following to connect:

• AuthScheme: Set this to Basic.
• User: Set this to the username associated with your HTTP(S) stream.
• Password: Set this to the password associated with your HTTP(S) stream.

Digest

Set the following to connect:

• AuthScheme: Set this to Digest.
• User: Set this to the username associated with your HTTP(S) stream.
• Password: Set this to the password associated with your HTTP(S) stream.

OAuth

OAuth requires the authenticating user to interact with XML using the browser. The connector facilitates this in various ways as described in the following sections.

Before following the procedures below, you need to register an OAuth app with the service containing the XML data you want to work with.

Creating a custom application in most services requires registering as a developer and creating an app in the UI of the service.

This is not necessarily true for all services. In some you must contact the service provider to create the app for you. However it is done, you must obtain the values for OAuthClientId, OAuthClientSecret, and CallbackURL.

Desktop Applications

After setting the following connection properties, you are ready to connect:

• OAuthVersion: Set this to the OAuth Version, either 1.0 or 2.0.
• OAuthRequestTokenURL: Required for OAuth 1.0. In OAuth 1.0, this is the URL where the app makes a request for the request token.
• OAuthAuthorizationURL: Required for OAuth 1.0 and 2.0. This is the URL where the user logs into the service and grants permissions to the application. In OAuth 1.0, if permissions are granted, the request token is authorized.
• OAuthAccessTokenURL: Required for OAuth 1.0 and 2.0. This is the URL where the request for the access token is made. In OAuth 1.0, the authorized request token is exchanged for the access token.
• OAuthRefreshTokenURL: Required for OAuth 2.0. In OAuth 2.0, this is the URL where the refresh token is exchanged for a new access token when the old one expires. Note that for your data source this may be the same as the access token URL.
• OAuthClientId: Set this to the client Id in your app settings. This may also be called the consumer key.
• OAuthClientSecret: Set this to the client secret in your app settings. This may also be called the consumer secret.
• CallbackURL: Set this to http://localhost:33333. If you specified a redirect URL in your app settings, this must match.
• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the access token in the connection string.

1. Extracts the access token from the callback URL and authenticates requests.
2. Refreshes the access token when it expires.
3. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

Web Application

When connecting via a Web application, or if the connector is not authorized to open a browser window, use the provided stored procedures to get and manage the OAuth token values.

Set Up the OAuth Flow

Provide the OAuth URLs to authenticate in the Web flow.

• OAuthRequestTokenURL: Required for OAuth 1.0. In OAuth 1.0, this is the URL where the app makes a request for the request token.
• OAuthAuthorizationURL: Required for OAuth 1.0 and 2.0. This is the URL where the user logs into the service and grants permissions to the application. In OAuth 1.0, if permissions are granted, the request token is authorized.
• OAuthAccessTokenURL: Required for OAuth 1.0 and 2.0. This is the URL where the request for the access token is made. In OAuth 1.0, the authorized request token is exchanged for the access token.
• OAuthRefreshTokenURL: Required for OAuth 2.0. In OAuth 2.0, this is the URL where the refresh token is exchanged for a new access token when the old one expires. Note that for your data source this may be the same as the access token URL.

In addition to the OAuth URLs, set the following additional connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the client Id in your app settings. This may also be called the consumer key.
• OAuthClientSecret: Set this to the client secret in your app settings. This may also be called the consumer secret.
• OAuthVersion: Set this to the OAuth version, either 1.0 or 2.0.

You can then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the AuthMode input to WEB and set the CallbackURL input to the Redirect URI you specified in your app settings. The stored procedure returns the URL to the OAuth endpoint.
2. Log in and authorize the application. You are redirected back to the callback URL.

3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB.

   In OAuth 1.0, set the Verifier input to the "oauth_verifier" parameter. Extract the verifier code from the callback URL. Additionally, set the AuthToken and AuthSecret to the values returned by GetOAuthAccessToken.

   In OAuth 2.0, set the Verifier input to the "code" parameter in the query string of the callback URL.

The OAuthAccessToken returned by GetOAuthAccessToken has a limited lifetime. To automatically refresh the token, set the following on the first data connection. Alternatively, use the RefreshOAuthAccessToken stored procedure to manually refresh the token.

OAuth Endpoints

• OAuthRequestTokenURL
• OAuthAuthorizationURL
• OAuthAccessTokenURL
• OAuthRefreshTokenURL

OAuth Tokens and Keys

• OAuthClientId
• OAuthClientSecret
• OAuthRefreshToken
• OAuthAccessToken

Initiate OAuth

• OAuthVersion: Set this to 1.0 or 2.0.
• InitiateOAuth: Set this to REFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• OAuthSettingsLocation: Set this to the location where the connector will save the OAuth values, to be persisted across connections.

On subsequent data connections, set the following:

• InitiateOAuth
• OAuthSettingsLocation
• OAuthRequestTokenURL
• OAuthAuthorizationURL
• OAuthAccessTokenURL
• OAuthRefreshTokenURL

OAuth JWT

Set AuthScheme to OAuthJWT.

The connector supports using JWT as an authorization grant in situations where a user cannot perform an interactive sign-on. After setting the following connection properties, you are ready to connect:

• OAuthVersion: Set this to 2.0.
• OAuthAccessTokenURL: Set this to the URL where the JWT is exchanged for an access token.
• OAuthJWTCert: Set this to the certificate you want to use. In most cases this will be a path to a PEM or PFX file.
• OAuthJWTCertType: Set this to the correct certificate type. In most cases this will either PEMKEY_FILE or PFXFILE.
• OAuthJWTCertPassword: If the certificate is encrypted, set this to the encryption password.
• OAuthJWTIssuer: Set this to the issuer. This corresponds to the iss field in the JWT.
• InitiateOAuth: Set this to GETANDREFRESH.

Note that the JWT signature algorithm cannot be set directly. The connector only supports the RS256 algorithm.

The connector will then construct a JWT including the following fields, and submit it to OAuthAccessTokenURL for an access token.

• scope This will come from Scope if it is provided.
• aud This will come from OAuthJWTAudience if it is provided.
• iss This will come from OAuthJWTIssuer.
• iat This is the time when the JWT is generated.
• exp This is the value of iat plus the value of OAuthJWTValidityTime.
• sub This will come from OAuthJWTSubject if it is provided.

OAuthPassword

AuthScheme: Set this to OAuthPassword.

OAuth requires the authenticating user to interact with XML using the browser. The connector facilitates this in various ways as described in the following sections.

Before following the procedures below, you need to register an OAuth app with the service containing the XML data you want to work with.

Creating a custom application in most services requires registering as a developer and creating an app in the UI of the service.

This is not necessarily true for all services. In some you must contact the service provider to create the app for you. However it is done, you must obtain the values for OAuthClientId, OAuthClientSecret, and CallbackURL.

After setting the following connection properties, you are ready to connect:

• OAuthVersion: Set this to the OAuth Version, either 1.0 or 2.0.
• OAuthRequestTokenURL: Required for OAuth 1.0. In OAuth 1.0, this is the URL where the app makes a request for the request token.
• OAuthAuthorizationURL: Required for OAuth 1.0 and 2.0. This is the URL where the user logs into the service and grants permissions to the application. In OAuth 1.0, if permissions are granted, the request token is authorized.
• OAuthAccessTokenURL: Required for OAuth 1.0 and 2.0. This is the URL where the request for the access token is made. In OAuth 1.0, the authorized request token is exchanged for the access token.
• OAuthRefreshTokenURL: Required for OAuth 2.0. In OAuth 2.0, this is the URL where the refresh token is exchanged for a new access token when the old one expires. Note that for your data source this may be the same as the access token URL.
• OAuthClientId: Set this to the client Id in your app settings. This may also be called the consumer key.
• OAuthClientSecret: Set this to the client secret in your app settings. This may also be called the consumer secret.
• CallbackURL: Set this to http://localhost:33333. If you specified a redirect URL in your app settings, this must match.
• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the access token in the connection string.

1. Extracts the access token from the callback URL and authenticates requests.
2. Refreshes the access token when it expires.
3. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

Before You Connect

Register a New Instance of Cloud Object Storage

If you do not already have Cloud Object Storage in your IBM Cloud account, you can follow the procedure below to install an instance of SQL Query in your account:

1. Log in to your IBM Cloud account.
2. Navigate to the Cloud Object Storage page, choose a name for your instance and click Create. You will be redirected to the instance of Cloud Object Storage you just created.

API Key

To connect with IBM Cloud Object Storage, you will need an ApiKey. You can obtain this as follows:

1. Log in to your IBM Cloud account.
2. Navigate to the Platform API Keys page.
3. On the middle-right corner click Create an IBM Cloud API Key to create a new API Key.
4. In the pop-up window, specify the API Key name and click Create. Note the ApiKey as you can never access it again from the dashboard.

Connecting to IBM Cloud Object Storage

Set Region to to your IBM instance region.

Authenticating to IBM Cloud Object Storage

You can authenticate to IBM Cloud Object Storage using either IAMSecretKey, or OAuth authentication.

IAMSecretKey

Set the following properties to authenticate:

• AccessKey: Set this to an IBM Access Key (a username).
• SecretKey: Set this to an IBM Secret Key.

ConnectionType=IBM Object Storage Source;URI=ibmobjectstorage://bucket1/folder1; AccessKey=token1; SecretKey=secret1; Region=eu-gb;

OAuth

Set the following to authenticate using OAuth authentication.

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• AuthScheme: Set this to OAuth.
• ApiKey: Set this to the IBM API Key noted during setup.

ConnectionType=IBM Object Storage Source;URI=ibmobjectstorage://bucket1/folder1; ApiKey=key1; Region=eu-gb; AuthScheme=OAuth; InitiateOAuth=GETANDREFRESH;

When you connect, the connector completes the OAuth process.

1. Extracts the access token and authenticates requests.
2. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

Connecting to OneDrive

You can connect to OneDrive using an AzureAD user, with MSI authentication, or using an Azure Service Principal.

AzureAD Users

AuthScheme must be set to AzureAD in all user account flows.

Desktop Applications

Get and Refresh the OAuth Access Token

After setting the following, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• OAuthClientId (custom applications only): Set this to the client Id assigned when you registered your app.
• OAuthClientSecret (custom applications only): Set this to the client secret assigned when you registered your app.
• CallbackURL (custom application only): Set this to the redirect URI defined when you registered your app. For example: http://localhost:33333

1. The connector obtains an access token from the Microsoft identity platform and uses it to request data.
2. The OAuth values are saved in the location specified in OAuthSettingsLocation, to be persisted across connections.

Web Applications

When connecting via a Web application, you need to register a custom OAuth app with Azure Active Directory. You can then use the connector to get and manage the OAuth token values. See Creating a Custom OAuth App for more information.

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Redirect URI you specified in your app settings. The stored procedure returns the URL to the OAuth endpoint.

2. Navigate to the URL that the stored procedure returned in Step 1. Log in, and authorize the web application. After authenticating, the browser redirects you to the redirect URI. There will be a parameter called code appended to the redirect URI. Note the value of this parameter.
3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the code parameter in the query string of the redirect URI.

After you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the connector automatically refresh the OAuth access token, set the following on the first data connection.

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.
• OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
• OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: Set this to the location where you want the connector to save the OAuth values, which persist across connections.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token. Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Headless Machines

To configure the driver, use OAuth with a user account on a headless machine. You need to authenticate on another device that has an internet browser.

1. Choose one of two options:
   • Option 1: Obtain the OAuthVerifier value as described in "Obtain and Exchange a Verifier Code" below.
   • Option 2: Install the connector on a machine with an internet browser and transfer the OAuth authentication values after you authenticate through the usual browser-based flow.
2. Then configure the connector to automatically refresh the access token on the headless machine.

Option 1: Obtain and Exchange a Verifier Code

To obtain a verifier code, you must authenticate at the OAuth authorization URL.

Follow the steps below to authenticate from the machine with an internet browser and obtain the OAuthVerifier connection property.

1. Choose one of these options:
   • If you are using the Embedded OAuth Application, call the GetOAuthAuthorizationURL stored procedure. Open the URL returned by the stored procedure in a browser.
   • If you are using a custom OAuth application, set the following properties:
     • InitiateOAuth: Set to OFF.
     • OAuthClientId: Set to the client Id assigned when you registered your application.
     • OAuthClientSecret: Set to the client secret assigned when you registered your application.
     Then call the GetOAuthAuthorizationURL stored procedure with the appropriate CallbackURL. Open the URL returned by the stored procedure in a browser.
2. Log in and grant permissions to the connector. You are then redirected to the redirect URI.

There will be a parameter called code appended to the redirect URI. Note the value of this parameter. Later you will set this in the OAuthVerifier connection property.

Next, you need to exchange the OAuth verifier code for OAuth refresh and access tokens.

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

• InitiateOAuth: Set this to REFRESH.
• OAuthVerifier: Set this to the noted verifier code (the value of the code parameter in the redirect URI).
• OAuthClientId: (custom applications only) Set this to the client Id in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) Set this to the client secret in the custom OAuth application settings.
• OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified location.

Test the connection to generate the OAuth settings file, then re-set the following properties to connect:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location containing the encrypted OAuth authentication values. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Option 2: Transfer OAuth Settings

Prior to connecting on a headless machine, you need to install and create a connection with the driver on a device that supports an internet browser. Set the connection properties as described in "Desktop Applications" above.

After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

Test the connection to generate the OAuth settings file, then copy the OAuth settings file to your headless machine.

On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location of the OAuth settings file you copied from the machine with the browser. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Azure Service Principal

The authentication as an Azure Service Principal is handled via the OAuth Client Credentials flow. It does not involve direct user authentication. Instead, credentials are created for just the application itself. All tasks taken by the application are done without a default user context, but based on the assigned roles. The application access to the resources is controlled through the assigned roles' permissions.

Create an AzureAD App and an Azure Service Principal

When authenticating using an Azure Service Principal, you must create and register an Azure AD application with an Azure AD tenant. See Creating an Entra ID (Azure AD) Application for more details.

In your App Registration in portal.azure.com, navigate to API Permissions and select the Microsoft Graph permissions. There are two distinct sets of permissions: Delegated permissions and Application permissions. The permissions used during client credential authentication are under Application Permissions.

Assign a role to the application

To access resources in your subscription, you must assign a role to the application.

1. Open the Subscriptions page by searching and selecting the Subscriptions service from the search bar.
2. Select the subscription to assign the application to.
3. Open the Access control (IAM) and select Add > Add role assignment to open the Add role assignment page.
4. Select Owner as the role to assign to your created Azure AD app.

Client Secret

Set these connection properties:

• AuthScheme: AzureServicePrincipal to use a client secret.
• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• AzureTenant: The tenant you want to connect to.
• OAuthClientId: The client Id in your application settings.
• OAuthClientSecret: The client secret in your application settings.

Certificate

Set these connection properties:

• AuthScheme: AzureServicePrincipalCert to use a certificate.
• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• AzureTenant: The tenant you want to connect to.
• OAuthJWTCert: The JWT Certificate store.
• OAuthJWTCertType: The type of the certificate store specified by OAuthJWTCert.

You are now ready to connect. Authentication with client credentials takes place automatically like any other connection, except there is no window opened prompting the user. Because there is no user context, there is no need for a browser popup. Connections take place and are handled internally.

Azure MSI

If you are connecting from an Azure VM with permissions for Azure Data Lake Storage, set AuthScheme to AzureMSI.

There are two types of custom AzureAD applications: AzureAD and AzureAD with an Azure Service Principal. Both are OAuth-based.

When to Create a Custom Application

You may choose to use your own AzureAD Application Credentials when you want to

• control branding of the Authentication Dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

Custom AzureAD Applications

Create a Custom AzureAD App

Follow the steps below to obtain the AzureAD values for your application, the OAuthClientId and OAuthClientSecret.

1. Log in to https://portal.azure.com.
2. In the left-hand navigation pane, select All services. Filter and select App registrations.
3. Click New registrations.
4. Enter an application name and select the desired tenant setup. When creating a custom AzureAD application in Azure Active Directory, you can define whether the application is single- or multi-tenant. If you select the default option, "Accounts in this organizational directory only", you must set the AzureTenant connection property to the Id of the Azure AD Tenant when establishing a connection with the CData Python Connector for XML. Otherwise, the authentication attempt fails with an error. If your application is for private use only, "Accounts in this organization directory only" should be sufficient. Otherwise, if you want to distribute your application, choose one of the multi-tenant options.
5. Set the redirect url to http://localhost:33333, the connector's default. Or, specify a different port and set CallbackURL to the exact reply URL you defined.
6. Click Register to register the new application. This opens an application management screen. Note the value in Application (client) ID as the OAuthClientId and the Directory (tenant) ID as the AzureTenant.
7. Navigate to the "Certificates & Secrets" and define the application authentication type. There are two types of authentication available: using a client secret or a certificate. The recommended authentication method is using a certificate.
   • Option 1: Upload a certificate: In "Certificates & Secrets", select Upload certificate and the certificate to upload from your local machine.
   • Option 2: Create a new application secret: In "Certificates & Secrets", select New Client Secret for the application and specify its duration. After saving the client secret, the key value is displayed. Copy this value as it is displayed only once. You will need it as the OAuthClientSecret.
8. Select API Permissions > Add. If your application connects without a user context, select Application Permissions. If your application authenticates on behalf of a signed-in user, choose Delegated permissions.
9. Save your changes.
10. If you have selected to use permissions that require admin consent (such as the Application Permissions), you can grant them from the current tenant on the API Permissions page. Otherwise, follow the steps under "Admin Consent".

Custom AzureAD Service Principal Applications

When authenticating using an Azure Service Principal, you must create both a custom AzureAD application and a service principal that can access the necessary resources. Follow the steps below to create a custom AzureAD application and obtain the connection properties for Azure Service Principal authentication.

Create a Custom AzureAD App with an Azure Service Principal

Follow the steps below to obtain the AzureAD values for your application.

1. Log in to https://portal.azure.com.
2. In the left-hand navigation pane, select All services. Filter and select App registrations.
3. Click New registrations.
4. Enter an app name and select Any Azure AD Directory - Multi Tenant. Then set the redirect url to http://localhost:33333, the connector's default.
5. After creating the application, copy the Application (client) Id value displayed in the "Overview" section. This value is used as the OAuthClientId
6. Define the app authentication type by going to the "Certificates & Secrets" section. There are two types of authentication available: using a client secret and using a certificate. The recommended authentication method is via a certificate.
   • Option 1 - Upload a certificate: In "Certificates & Secrets", select Upload certificate and the certificate to upload from your local machine.
   • Option 2 - Create a new application secret: In "Certificates & Secrets", select New Client Secret for the application and specify its duration. After saving the client secret, the key value is displayed. Copy this value as it is displayed only once. You will use it as the OAuthClientSecret.
7. On the Authentication tab, make sure to select Access tokens (used for implicit flows).

Authenticating to OneLake

You can authenticate to OneLake via AzureAD user, Azure MSI, or Azure Service Principal.

AzureAD User

AuthScheme must be set to AzureAD in all user account flows.

Desktop Applications

Get and Refresh the OAuth Access Token

After setting the following, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• OAuthClientId (custom applications only): Set this to the client Id assigned when you registered your app.
• OAuthClientSecret (custom applications only): Set this to the client secret assigned when you registered your app.
• CallbackURL (custom application only): Set this to the redirect URI defined when you registered your app. For example: http://localhost:33333

1. The connector obtains an access token from the Microsoft identity platform and uses it to request data.
2. The OAuth values are saved in the location specified in OAuthSettingsLocation, to be persisted across connections.

Web Applications

When connecting via a Web application, you need to register a custom OAuth app with Azure Active Directory. You can then use the connector to get and manage the OAuth token values. See Creating a Custom OAuth App for more information.

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Redirect URI you specified in your app settings. The stored procedure returns the URL to the OAuth endpoint.

2. Navigate to the URL that the stored procedure returned in Step 1. Log in, and authorize the web application. After authenticating, the browser redirects you to the redirect URI. There will be a parameter called code appended to the redirect URI. Note the value of this parameter.
3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the code parameter in the query string of the redirect URI.

After you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the connector automatically refresh the OAuth access token, set the following on the first data connection.

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.
• OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
• OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: Set this to the location where you want the connector to save the OAuth values, which persist across connections.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token. Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Headless Machines

To configure the driver, use OAuth with a user account on a headless machine. You need to authenticate on another device that has an internet browser.

1. Choose one of two options:
   • Option 1: Obtain the OAuthVerifier value as described in "Obtain and Exchange a Verifier Code" below.
   • Option 2: Install the connector on a machine with an internet browser and transfer the OAuth authentication values after you authenticate through the usual browser-based flow.
2. Then configure the connector to automatically refresh the access token on the headless machine.

Option 1: Obtain and Exchange a Verifier Code

To obtain a verifier code, you must authenticate at the OAuth authorization URL.

Follow the steps below to authenticate from the machine with an internet browser and obtain the OAuthVerifier connection property.

1. Choose one of these options:
   • If you are using the Embedded OAuth Application, call the GetOAuthAuthorizationURL stored procedure. Open the URL returned by the stored procedure in a browser.
   • If you are using a custom OAuth application, set the following properties:
     • InitiateOAuth: Set to OFF.
     • OAuthClientId: Set to the client Id assigned when you registered your application.
     • OAuthClientSecret: Set to the client secret assigned when you registered your application.
     Then call the GetOAuthAuthorizationURL stored procedure with the appropriate CallbackURL. Open the URL returned by the stored procedure in a browser.
2. Log in and grant permissions to the connector. You are then redirected to the redirect URI.

There will be a parameter called code appended to the redirect URI. Note the value of this parameter. Later you will set this in the OAuthVerifier connection property.

Next, you need to exchange the OAuth verifier code for OAuth refresh and access tokens.

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

• InitiateOAuth: Set this to REFRESH.
• OAuthVerifier: Set this to the noted verifier code (the value of the code parameter in the redirect URI).
• OAuthClientId: (custom applications only) Set this to the client Id in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) Set this to the client secret in the custom OAuth application settings.
• OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified location.

Test the connection to generate the OAuth settings file, then re-set the following properties to connect:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location containing the encrypted OAuth authentication values. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Option 2: Transfer OAuth Settings

Prior to connecting on a headless machine, you need to install and create a connection with the driver on a device that supports an internet browser. Set the connection properties as described in "Desktop Applications" above.

After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

Test the connection to generate the OAuth settings file, then copy the OAuth settings file to your headless machine.

On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location of the OAuth settings file you copied from the machine with the browser. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Azure Service Principal

The authentication as an Azure Service Principal is handled via the OAuth Client Credentials flow. It does not involve direct user authentication. Instead, credentials are created for just the application itself. All tasks taken by the application are done without a default user context, but based on the assigned roles. The application access to the resources is controlled through the assigned roles' permissions.

Create an AzureAD App and an Azure Service Principal

When authenticating using an Azure Service Principal, you must create and register an Azure AD application with an Azure AD tenant. See Creating an Entra ID (Azure AD) Application for more details.

In your App Registration in portal.azure.com, navigate to API Permissions and select the Microsoft Graph permissions. There are two distinct sets of permissions: Delegated permissions and Application permissions. The permissions used during client credential authentication are under Application Permissions.

Assign a role to the application

To access resources in your subscription, you must assign a role to the application.

1. Open the Subscriptions page by searching and selecting the Subscriptions service from the search bar.
2. Select the subscription to assign the application to.
3. Open the Access control (IAM) and select Add > Add role assignment to open the Add role assignment page.
4. Select Owner as the role to assign to your created Azure AD app.

Client Secret

Set these connection properties:

• AuthScheme: AzureServicePrincipal to use a client secret.
• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• AzureTenant: The tenant you want to connect to.
• OAuthClientId: The client Id in your application settings.
• OAuthClientSecret: The client secret in your application settings.

Certificate

Set these connection properties:

• AuthScheme: AzureServicePrincipalCert to use a certificate.
• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• AzureTenant: The tenant you want to connect to.
• OAuthJWTCert: The JWT Certificate store.
• OAuthJWTCertType: The type of the certificate store specified by OAuthJWTCert.

You are now ready to connect. Authentication with client credentials takes place automatically like any other connection, except there is no window opened prompting the user. Because there is no user context, there is no need for a browser popup. Connections take place and are handled internally.

Azure MSI

If you are connecting from an Azure VM with permissions for Azure Data Lake Storage, set AuthScheme to AzureMSI.

There are two types of custom AzureAD applications: AzureAD and AzureAD with an Azure Service Principal. Both are OAuth-based.

When to Create a Custom Application

You may choose to use your own AzureAD Application Credentials when you want to

• control branding of the Authentication Dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

Custom AzureAD Applications

Create a Custom AzureAD App

Follow the steps below to obtain the AzureAD values for your application, the OAuthClientId and OAuthClientSecret.

1. Log in to https://portal.azure.com.
2. In the left-hand navigation pane, select All services. Filter and select App registrations.
3. Click New registrations.
4. Enter an application name and select the desired tenant setup. When creating a custom AzureAD application in Azure Active Directory, you can define whether the application is single- or multi-tenant. If you select the default option, "Accounts in this organizational directory only", you must set the AzureTenant connection property to the Id of the Azure AD Tenant when establishing a connection with the CData Python Connector for XML. Otherwise, the authentication attempt fails with an error. If your application is for private use only, "Accounts in this organization directory only" should be sufficient. Otherwise, if you want to distribute your application, choose one of the multi-tenant options.
5. Set the redirect url to http://localhost:33333, the connector's default. Or, specify a different port and set CallbackURL to the exact reply URL you defined.
6. Click Register to register the new application. This opens an application management screen. Note the value in Application (client) ID as the OAuthClientId and the Directory (tenant) ID as the AzureTenant.
7. Navigate to the "Certificates & Secrets" and define the application authentication type. There are two types of authentication available: using a client secret or a certificate. The recommended authentication method is using a certificate.
   • Option 1: Upload a certificate: In "Certificates & Secrets", select Upload certificate and the certificate to upload from your local machine.
   • Option 2: Create a new application secret: In "Certificates & Secrets", select New Client Secret for the application and specify its duration. After saving the client secret, the key value is displayed. Copy this value as it is displayed only once. You will need it as the OAuthClientSecret.
8. Select API Permissions > Add a permission > Azure Storage > user_impersonation > Add permissions.
9. Save your changes.
10. If you have selected to use permissions that require admin consent (such as the Application Permissions), you can grant them from the current tenant on the API Permissions page. Otherwise, follow the steps under "Admin Consent".

Custom AzureAD Service Principal Applications

When authenticating using an Azure Service Principal, you must create both a custom AzureAD application and a service principal that can access the necessary resources. Follow the steps below to create a custom AzureAD application and obtain the connection properties for Azure Service Principal authentication.

Create a Custom AzureAD App with an Azure Service Principal

Follow the steps below to obtain the AzureAD values for your application.

1. Log in to https://portal.azure.com.
2. In the left-hand navigation pane, select All services. Filter and select App registrations.
3. Click New registrations.
4. Enter an app name and select Any Azure AD Directory - Multi Tenant. Then set the redirect url to http://localhost:33333, the connector's default.
5. After creating the application, copy the Application (client) Id value displayed in the "Overview" section. This value is used as the OAuthClientId
6. Define the app authentication type by going to the "Certificates & Secrets" section. There are two types of authentication available: using a client secret and using a certificate. The recommended authentication method is via a certificate.
   • Option 1 - Upload a certificate: In "Certificates & Secrets", select Upload certificate and the certificate to upload from your local machine.
   • Option 2 - Create a new application secret: In "Certificates & Secrets", select New Client Secret for the application and specify its duration. After saving the client secret, the key value is displayed. Copy this value as it is displayed only once. You will use it as the OAuthClientSecret.
7. On the Authentication tab, make sure to select Access tokens (used for implicit flows).

Add Service Principal to Workspace

Follow the steps below to add a service principal to a workspace.

1. Log in to Microsoft Fabric.
2. Click the gear icon (Settings) on the top right.
3. Select Admin portal.
4. In the left-hand navigation pane, select Tenant settings.
5. Scroll until you find Developer settings.
6. Expand Service principals can use Fabric APIs.
7. Enable the option.
8. Select Apply.
9. Select the workspace where you want to add your service principal.
10. Click Manage access.
11. Click Add people or groups.
12. Enter the name of your application (verify the ID if there are multiple applications with the same name).
13. Set the level of access you would like to grant to your application. Contributor is the lowest security level necessary to access OneLake via the API.
14. Select Add.

Connecting to SFTP

You can authenticate to SFTP using a user and password or an SSH certificate. Additionally, you can connect to an SFTP server that has no authentication enabled.

No Authentication

Set SSHAuthMode to None to connect without authentication, assuming your server supports doing so.

Password

Provide user credentials associated with your SFTP server:

• SSHAuthMode: Set this to Password.
• SSHUser: A username associated with your SFTP server.
• SSHPassword: The password associated with the user.

SSH Certificate

Set the following to connect.

• SSHAuthMode: Set this to Public_Key.
• SSHClientCert: Specify the SSH certificate in the form specified by SSHClientCertType (see the associated documentation for this connection property).
• SSHClientCertType: The type of the key store specified in SSHClientCert.
• SSHClientCertPassword (optional): The certificate store password.
• SSHClientCertSubject (optional): If there are multiple keys in your key store, specify the desired key, by name, here.

Connecting to SharePoint Online (REST)

The following authentication schemes are supported for the REST API:

• AzureAD
• MSI
• AzureServicePrincipal

AzureAD

Your organization may require Admin Consent when authorizing a new AzureAD application for your Azure Tenant. In all AzureAD flows, any initial installation and use of an AzureAD application requires that an administrator approve the application for their Azure Tenant.

Desktop Applications

CData provides an embedded OAuth application that simplifies OAuth desktop authentication. Alternatively, you can create a custom AzureAD application. See Creating a Custom AzureAD App for information about creating custom applications and reasons for doing so.

For authentication, the only difference between the two methods is that you must set two additional connection properties when using custom AzureAD applications.

After setting the following connection properties, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• OAuthClientId: (custom applications only) Set this to the client Id in your application settings.
• OAuthClientSecret: (custom applications only) Set this to the client secret in your application settings.
• CallbackURL: Set this to the Redirect URL in your application settings.

When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:

1. Extracts the access token from the callback URL and authenticates requests.
2. Obtains a new access token when the old one expires.
3. Saves OAuth values in OAuthSettingsLocation. These stored values persist across connections.

Web Applications

When connecting via a Web application, you need to create and register a custom AzureAD application with XML. See Creating a Custom AzureAD App for more information about custom applications. You can then use the connector to acquire and manage the OAuth token values.

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the client Id in your application settings.
• OAuthClientSecret: Set this to the client secret in your application settings

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the callback URL you specified in your application settings. If necessary, set the Scope parameter to request custom permissions.

   The stored procedure returns the URL to the OAuth endpoint.

2. Open the URL, log in, and authorize the application. You are redirected back to the callback URL.
3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the "code" parameter in the query string of the callback URL. If necessary, set the Scope parameter to request custom permissions.

Once you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the driver automatically refresh the OAuth access token, set the following on the first data connection:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the client Id in your application settings.
• OAuthClientSecret: Set this to the client secret in your application settings.
• OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
• OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: Set this to the location where the connector saves the OAuth token values, which persist across connections.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token.

Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

• OAuthClientId: Set this to the client Id in your application settings.
• OAuthClientSecret: Set this to the client secret in your application settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Headless Machines

To configure the driver to use OAuth with a user account on a headless machine, you need to authenticate on another device that has an internet browser.

1. Choose one of these two options:
   • Option 1: Obtain the OAuthVerifier value as described in "Obtain and Exchange a Verifier Code" below.
   • Option 2: Install the connector on another machine and transfer the OAuth authentication values after you authenticate through the usual browser-based flow, as described in "Transfer OAuth Settings" below.

2. Then configure the connector to automatically refresh the access token from the headless machine.

Option 1: Obtain and Exchange a Verifier Code

To obtain a verifier code, you must authenticate at the OAuth authorization URL.

Follow the steps below to authenticate from the machine with an internet browser and obtain the OAuthVerifier connection property.

1. Choose one of these options:
   • If you are using the Embedded OAuth Application click XML OAuth endpoint to open the endpoint in your browser.
   • If you are using a custom OAuth application, create the Authorization URL by setting the following properties:
     • InitiateOAuth: Set to OFF.
     • OAuthClientId: Set to the client Id assigned when you registered your application.
     • OAuthClientSecret: Set to the client secret assigned when you registered your application.
     Then call the GetOAuthAuthorizationURL stored procedure with the appropriate CallbackURL. Open the URL returned by the stored procedure in a browser.

2. Log in and grant permissions to the connector. You are then redirected to the callback URL, which contains the verifier code.
3. Save the value of the verifier code. Later you will set this in the OAuthVerifier connection property.

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

• InitiateOAuth: Set this to REFRESH.
• OAuthVerifier: Set this to the verifier code.
• OAuthClientId: (custom applications only) Set this to the client Id in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) Set this to the client secret in the custom OAuth application settings.
• OAuthSettingsLocation: Set this to the location of the file where the driver saves the OAuth token values that persist across connections.

After the OAuth settings file is generated, you need to re-set the following properties to connect:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location containing the encrypted OAuth authentication values. Make sure this location grants read and write permissions to the connector to enable the automatic refreshing of the access token.

Option 2: Transfer OAuth Settings

Prior to connecting on a headless machine, you need to create and install a connection with the driver on a device that supports an internet browser. Set the connection properties as described in "Desktop Applications" above.

After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

Once you have successfully tested the connection, copy the OAuth settings file to your headless machine.

On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location of your OAuth settings file. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Azure Service Principal

The authentication as an Azure Service Principal is handled via the OAuth Client Credentials flow. It does not involve direct user authentication. Instead, credentials are created for just the application itself. All tasks taken by the application are done without a default user context, but based on the assigned roles. The application access to the resources is controlled through the assigned roles' permissions.

Create an AzureAD App and an Azure Service Principal

When authenticating using an Azure Service Principal, you must create and register an Azure AD application with an Azure AD tenant. See Creating an Entra ID (Azure AD) Application for more details.

In your App Registration in portal.azure.com, navigate to API Permissions and select the Microsoft Graph permissions. There are two distinct sets of permissions: Delegated permissions and Application permissions. The permissions used during client credential authentication are under Application Permissions.

Assign a role to the application

To access resources in your subscription, you must assign a role to the application.

1. Open the Subscriptions page by searching and selecting the Subscriptions service from the search bar.
2. Select the subscription to assign the application to.
3. Open the Access control (IAM) and select Add > Add role assignment to open the Add role assignment page.
4. Select Owner as the role to assign to your created Azure AD app.

Client Secret

Set these connection properties:

• AuthScheme: AzureServicePrincipal to use a client secret.
• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• AzureTenant: The tenant you want to connect to.
• OAuthClientId: The client Id in your application settings.
• OAuthClientSecret: The client secret in your application settings.

Certificate

Set these connection properties:

• AuthScheme: AzureServicePrincipalCert to use a certificate.
• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• AzureTenant: The tenant you want to connect to.
• OAuthJWTCert: The JWT Certificate store.
• OAuthJWTCertType: The type of the certificate store specified by OAuthJWTCert.

You are now ready to connect. Authentication with client credentials takes place automatically like any other connection, except there is no window opened prompting the user. Because there is no user context, there is no need for a browser popup. Connections take place and are handled internally.

MSI

If you are running XML on an Azure VM, you can leverage Managed Service Identity (MSI) credentials to connect:

• AuthScheme: Set this to AzureMSI.

The MSI credentials are automatically obtained for authentication.

Azure Service Principal

When authenticating using an Azure Service Principal, you must register an application with an Azure AD tenant.

Assign a role to the application

To access resources in your subscription, you must assign a role to the application.

1. Open the Subscriptions page by searching and selecting the Subscriptions service from the search bar.
2. Select the particular subscription to assign the application to.
3. Open the Access control (IAM) and select Add > Add role assignment to open the Add role assignment page.
4. Select Owner as the role to assign to your created Azure AD app.

Authenticate with an Azure Service Principal

You are ready to connect after setting one of the below connection properties groups, depending on the configured app authentication (client secret or certificate).

Before choosing client secret or certicate authentication, set the following:

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• AzureTenant: Set this to the tenant you wish to connect to.
• OAuthClientId: Set this to the client Id in your app settings.

Option 1: Authenticating using a Client Secret

Set the following to authenticate with a client secret:

• AuthScheme: Set this to the AzureServicePrincipal in your app settings.
• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.

Option 2: Authenticating using a JWT Certificate

Set the following to authenticate with a JWT Certificate:

• AuthScheme: Set this to the AzureServicePrincipal in your app settings.
• OAuthJWTCert: Set this to the JWT Certificate store.
• OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.

Connecting to SharePoint Online (REST V1)

SharePoint REST V1 uses the SharePoint REST API v1 (_api/web/) with the sprestv1:// URI scheme. It supports both on-premise and SharePoint Online environments. The following authentication schemes are supported for SharePoint Online:

• AzureAD
• ADFS
• Okta
• OneLogin
• PingFederate

AzureAD

Azure Active Directory (AzureAD / Entra ID) uses OAuth 2.0 to authenticate with Azure AD and obtain an access token for the SharePoint REST API. Set the AuthScheme to AzureAD. The following connection properties are used to connect:

• InitiateOAuth: Set this to GETANDREFRESH to have the connector manage the OAuth token exchange and refresh automatically.
• OAuthClientId: (custom applications only) Set this to the client Id in your Azure AD application settings.
• OAuthClientSecret: (custom applications only) Set this to the client secret in your Azure AD application settings.
• CallbackURL: Set this to the Redirect URL configured in your Azure AD application.
• AzureTenant: (optional) Set this to the Azure AD tenant Id or domain. Defaults to common for multi-tenant applications. Required when connecting to SharePoint On Premise with Entra ID.

The following is an example connection string:

AuthScheme=AzureAD;InitiateOAuth=GETANDREFRESH;ConnectionType='SharePoint REST V1';URI='sprestv1://Documents/';StorageBaseURL=https://subdomain.sharepoint.com/sites/mysite;

ADFS

Set the AuthScheme to ADFS. You need to set the following connection properties:

• User: Set this to the ADFS user.
• Password: Set this to ADFS password for the user.
• SSOLoginURL: Set this to the base URL for your ADFS server.

The following is an example connection string:

AuthScheme=ADFS;User=ADFSUserName;Password=ADFSPassword;ConnectionType='SharePoint REST V1';URI='sprestv1://Documents/';StorageBaseURL=https://subdomain.sharepoint.com/sites/mysite;

Okta

Set the AuthScheme to Okta. The following connection properties are used to connect to Okta:

• User: Set this to the Okta user.
• Password: Set this to Okta password for the user.
• SSOLoginURL: Set this to your Okta application's embed link.

The following is an example connection string:

AuthScheme=Okta;User=oktaUserName;Password=oktaPassword;ConnectionType='SharePoint REST V1';URI='sprestv1://Documents/';StorageBaseURL=https://subdomain.sharepoint.com/sites/mysite;

OneLogin

Set the AuthScheme to OneLogin. The following connection properties are used to connect to OneLogin:

• User: Set this to the OneLogin user.
• Password: Set this to OneLogin password for the user.

The following is an example connection string:

AuthScheme=OneLogin;User=OneLoginUserName;Password=OneLoginPassword;ConnectionType='SharePoint REST V1';URI='sprestv1://Documents/';StorageBaseURL=https://subdomain.sharepoint.com/sites/mysite;

PingFederate

Set the AuthScheme to PingFederate. The following connection properties are used to connect to PingFederate:

• User: Set this to the PingFederate user.
• Password: Set this to PingFederate password for the user.
• SSOLoginURL: Set this to the base URL for your PingFederate server.

The following is an example connection string:

AuthScheme=PingFederate;User=PingFederateUserName;Password=PingFederatePassword;ConnectionType='SharePoint REST V1';URI='sprestv1://Documents/';StorageBaseURL=https://subdomain.sharepoint.com/sites/mysite;

When to Create a Custom OAuth App

You may choose to create your own OAuth Application Credentials when you want to

• control branding of the Authentication Dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

Creating a Custom OAuth App

Follow the steps below to obtain OAuth values for your app, the OAuthClientId and OAuthClientSecret.

1. Log in to the Azure Portal.
2. In the left-hand navigation pane, select Azure Active Directory > App Registrations and click Add.
3. Enter an application name and select Any Azure AD Directory - Multi Tenant. Then set the redirect url to http://localhost:33333, the connector's default or set a different port of your choice and set CallbackURL to the exact reply URL you defined.
4. After creating the app, navigate to the "Certificates & Secrets" section, create a client secret for the application, and select a duration.
5. After you save the key, key value is displayed once. Set OAuthClientSecret to the displayed value. Set OAuthClientId to the Application Id.
6. Select API Permissions and click Add. If your application connects without a user context, select Application Permissions. If your application authenticates on behalf of a signed-in user, choose Delegated permissions.
7. In the API Permissions section, click on Add a permission and select Sharepoint. Choose the permissions you want your app to have. To view and edit lists, you have to select (at least) the AllSites.Manage permission.
8. Save your changes.
9. If you have selected to use permissions that require admin consent (such as the Application Permissions), you can grant them from the current tenant on the API Permissions page. Otherwise, follow the steps under "OAuth: Admin Consent" in Establishing a Connection.

Connecting to SharePoint On Premise

SharePoint On Premise can be accessed using either the SharePoint SOAP or SharePoint REST V1 connection type.

• SharePoint SOAP uses the SharePoint SOAP API with the sp:// URI scheme.
• SharePoint REST V1 uses the SharePoint REST API v1 (_api/web/) with the sprestv1:// URI scheme.

Note: SharePoint REST V1 supports connecting to both on-premise and SharePoint Online environments. The SSO authentication schemes (ADFS, Okta, OneLogin, PingFederate) authenticate through Microsoft's SharePoint Online cloud endpoints. For SharePoint Online-specific guidance, see also Connecting to SharePoint Online.

The following authentication schemes are supported for both connection types:

• User Credentials
• ADFS
• Okta
• OneLogin
• NTLM

Additionally, SharePoint REST V1 supports:

• AzureAD (Azure Active Directory / Entra ID)

User Credentials

Set the AuthScheme to Basic. You need to set the following connection properties:

• User: Set this to the SharePoint user.
• Password: Set this to SharePoint password for the user.

AuthScheme=Basic;User=yourUserName;Password=yourPassword;SharePointEdition=SharePointOnPremise;ConnectionType='SharePoint SOAP';URI='sp://Shared Documents/';StorageBaseURL=http://sharePointServer/;

AuthScheme=Basic;User=yourUserName;Password=yourPassword;SharePointEdition=SharePointOnPremise;ConnectionType='SharePoint REST V1';URI='sprestv1://Documents/';StorageBaseURL=http://sharePointServer/sites/mysite;

ADFS

Set the AuthScheme to ADFS. You need to set the following connection properties:

• User: Set this to the ADFS user.
• Password: Set this to ADFS password for the user.
• SSOLoginURL: Set this to the base URL for your ADFS server.

AuthScheme=ADFS;User=ADFSUserName;Password=ADFSPassword;SharePointEdition=SharePointOnPremise;ConnectionType='SharePoint SOAP';URI='sp://Documents/';StorageBaseURL=http://sharePointServer/;

AuthScheme=ADFS;User=ADFSUserName;Password=ADFSPassword;ConnectionType='SharePoint REST V1';URI='sprestv1://Documents/';StorageBaseURL=https://subdomain.sharepoint.com/sites/mysite;

Okta

Set the AuthScheme to Okta. The following connection properties are used to connect to Okta:

• User: Set this to the Okta user.
• Password: Set this to Okta password for the user.
• SSOProperties:
  • Domain (optional): It may be required to set this property if the domain configured on the SSO domain is different than the domain of the User.

The following are example connection strings:

AuthScheme=Okta;User=oktaUserName;Password=oktaPassword;SharePointEdition=SharePointOnPremise;ConnectionType='SharePoint SOAP';URI='sp://Documents/';StorageBaseURL=http://sharePointServer/;

AuthScheme=Okta;User=oktaUserName;Password=oktaPassword;ConnectionType='SharePoint REST V1';URI='sprestv1://Documents/';StorageBaseURL=https://subdomain.sharepoint.com/sites/mysite;

OneLogin

Set the AuthScheme to OneLogin. The following connection properties are used to connect to OneLogin:

• User: Set this to the OneLogin user.
• Password: Set this to OneLogin password for the user.
• SSOProperties:
  • Domain (optional): It may be required to set this property if the domain configured on the SSO domain is different than the domain of the User.

The following are example connection strings:

AuthScheme=OneLogin;User=OneLoginUserName;Password=OneLoginPassword;SharePointEdition=SharePointOnPremise;ConnectionType='SharePoint SOAP';URI='sp://Documents/';StorageBaseURL=http://sharePointServer/;

AuthScheme=OneLogin;User=OneLoginUserName;Password=OneLoginPassword;ConnectionType='SharePoint REST V1';URI='sprestv1://Documents/';StorageBaseURL=https://subdomain.sharepoint.com/sites/mysite;

NTLM

Set the AuthScheme to NTLM. The following connection properties are used to connect to NTLM:

• User: Set this to the NTLM user.
• Password: Set this to NTLM password for the user.

The following are example connection strings:

AuthScheme=NTLM;User=NtlmUsername;Password=NtlmPassword;SharePointEdition=SharePointOnPremise;ConnectionType='SharePoint SOAP';URI='sp://Documents/';StorageBaseURL=http://sharePointServer/;

AuthScheme=NTLM;User=NtlmUsername;Password=NtlmPassword;SharePointEdition=SharePointOnPremise;ConnectionType='SharePoint SOAP';URI='sp://Documents/mycars.XML';StorageBaseURL=http://sharePointServer/;

AuthScheme=NTLM;User=NtlmUsername;Password=NtlmPassword;SharePointEdition=SharePointOnPremise;ConnectionType='SharePoint REST V1';URI='sprestv1://Documents/';StorageBaseURL=http://sharePointServer/sites/mysite;

AzureAD

Azure Active Directory (AzureAD / Entra ID) authentication is supported for SharePoint REST V1 only. This uses OAuth 2.0 to authenticate with Azure AD and obtain an access token for the SharePoint REST API. This works for both SharePoint Online and SharePoint Server on-premise (when configured with Entra ID as an OIDC identity provider, e.g. SharePoint Server Subscription Edition). Set the AuthScheme to AzureAD. The following connection properties are used to connect:

• OAuthClientId: Set this to the client Id in your Azure AD application settings.
• OAuthClientSecret: Set this to the client secret in your Azure AD application settings.
• CallbackURL: Set this to the Redirect URL configured in your Azure AD application.
• InitiateOAuth: Set this to GETANDREFRESH to have the connector manage the OAuth token exchange and refresh automatically.
• AzureTenant: Set this to the Azure AD tenant Id or domain (e.g. contoso.onmicrosoft.com or a tenant GUID). This is required for SharePoint On Premise.

The following are example connection strings:

AuthScheme=AzureAD;AzureTenant=contoso.onmicrosoft.com;InitiateOAuth=GETANDREFRESH;SharePointEdition=SharePointOnPremise;ConnectionType='SharePoint REST V1';URI='sprestv1://Documents/';StorageBaseURL=https://sharePointServer/sites/mysite;

AuthScheme=AzureAD;InitiateOAuth=GETANDREFRESH;ConnectionType='SharePoint REST V1';URI='sprestv1://Documents/';StorageBaseURL=https://subdomain.sharepoint.com/sites/mysite;

Authenticating with SSO

AzureAD

Azure AD Configuration

The main theme behind this configuration is the OAuth 2.0 On-Behalf-Of flow. It requires two Azure AD applications:

1. An application used for the single sign-on process to a specific service provider.
   • Amazon S3: Please follow this link for detailed instructions on how to create this application. Make sure you test the connection and you are able to login to the AWS console from Azure AD.

     Save the step "Assign the Azure AD test user" until after provisioning so that you can select the AWS roles when assigning the user.

2. A "connector" application with user_impersonation permission on the SSO application you created in the previous step. Go to Azure Active Directory > App registrations and register a new application. After you register this application, you need to allow it to make API calls to the SSO application. Go to the API permissions section of the app you registered and click the "Add a permission" box. Select the API of your SSO application by specifying the API name or Application Id and add the user_impersonation permission.

CData Driver Common Properties

The following SSOProperties are needed to authenticate to Azure Active Directory and must be specified for every service provider.

• Resource: The application Id URI of the SSO application, listed in the Overview section of the app registration.
• Tenant: The Id of the Azure AD tenant where the applications are registered. You can find this value using the instructions found here.

We will retrieve the SSO SAML response from an OAuth 2.0 On-Behalf-Of flow so the following OAuth connection properties must be specified:

• OAuthClientId: The application Id of the connector application, listed in the Overview section of the app registration.
• OAuthClientSecret: The client secret value of the connector application. Azure AD displays this when you create a new client secret (Certificates & secrets section).

Amazon S3

In addition to the common properties, the following properties must be specified when connecting to Amazon S3 service provider:

• AuthScheme: Set the AuthScheme to AzureAD.
• AWSRoleARN: The ARN of the IAM role. Find this on the Summary page of the IAM role.
• AWSPrincipalARN: The ARN of the identity provider. Find this on the identity provider's summary page.

AuthScheme=AzureAD;InitiateOAuth=GETANDREFRESH;OAuthClientId=d593a1d-ad89-4457-872d-8d7443aaa655;OauthClientSecret=g9-oy5D_rl9YEKfN-45~3Wm8FgVa2F;SSOProperties='Tenant=94be7-edb4-4fda-ab12-95bfc22b232f;Resource=https://signin.aws.amazon.com/saml;';AWSRoleARN=arn:aws:iam::2153385180:role/AWS_AzureAD;AWSPrincipalARN=arn:aws:iam::215515180:saml-provider/AzureAD;

OneLogin

OneLogin Configuration

You must create an application used for the single sign-on process to a specific provider.

• Sharepoint SOAP: Please follow this link for detailed instructions on how to create this application. Make sure you test the connection and you are able to login to Office 365 from OneLogin. Make sure you have enabled WS-TRUST in your application. Otherwise, the CData driver will not be able to connect.

Sharepoint SOAP

The following properties must be specified when connecting to Sharepoint SOAP service provider:

• AuthScheme: Set the AuthScheme to OneLogin.
• User: The username of the OneLogin account.
• Password: The password of the OneLogin account.
• SSOProperties:
  • Domain (optional): It may be required to be set this property if the domain configured on the SSO domain is different than the domain of the User.

AuthScheme='OneLogin';User=test;Password=test;SSOProperties='Domain=test.cdata;';

Sharepoint REST V1

The following properties must be specified when connecting to Sharepoint REST V1 service provider:

• AuthScheme: Set the AuthScheme to OneLogin.
• User: The username of the OneLogin account.
• Password: The password of the OneLogin account.
• SSOProperties:
  • Domain (optional): It may be required to be set this property if the domain configured on the SSO domain is different than the domain of the User.

AuthScheme='OneLogin';User=test;Password=test;SSOProperties='Domain=test.cdata;';StorageBaseURL='https://sharepointserver/sites/mysite';

Okta

Okta Configuration

You must create an application used for the single sign-on process to a specific provider.

• Sharepoint SOAP: Please follow this link for detailed instructions on how to create this application and configure SSO. Make sure you test the connection and you are able to login to Office 365 from Okta. Make sure you have configured SSO using WS-Federation in your application. Otherwise, the CData driver will not be able to connect.
• Amazon S3: Please follow this link for detailed instructions on how to create this application and configure SSO. Make sure you test the connection and you are able to login to AWS from Okta. Make sure you have configured SSO with SAML 2.0 in your application. Otherwise, the CData driver will not be able to connect. Ensure that the assigned AWS role in the Okta app has access to the S3 bucket you want to connect.

Sharepoint SOAP

The following properties must be specified when connecting to Sharepoint SOAP service provider:

• AuthScheme: Set the AuthScheme to Okta.
• User: The username of the Okta account.
• Password: The password of the Okta account.
• SSOProperties:
  • Domain (optional): It may be required to be set this property if the domain configured on the SSO domain is different than the domain of the User.

AuthScheme='Okta';User=test;Password=test;SSOProperties='Domain=test.cdata;';

Sharepoint REST V1

The following properties must be specified when connecting to Sharepoint REST V1 service provider:

• AuthScheme: Set the AuthScheme to Okta.
• User: The username of the Okta account.
• Password: The password of the Okta account.
• SSOProperties:
  • Domain (optional): It may be required to be set this property if the domain configured on the SSO domain is different than the domain of the User.

AuthScheme='Okta';User=test;Password=test;SSOProperties='Domain=test.cdata;';StorageBaseURL='https://sharepointserver/sites/mysite';

Amazon S3

The following properties must be specified when connecting to an Amazon S3 service provider:

• AuthScheme: Set the AuthScheme to Okta.
• User: The username of the Okta account.
• Password: The password of the Okta account.
• SSOLoginURL: Set this to the embedded URL of your AWS Okta SSO app.
• AWSRoleARN (optional): The ARN of the IAM role. Find this on the Summary page of the IAM role.
• AWSPrincipalARN (optional): The ARN of the identity provider. Find this on the identity provider's summary page.
• SSOProperties:
  • APIToken (optional): Set this to the API Token that the customer created from the Okta org. It should be used when authenticating a user via a trusted application or proxy that overrides Okta client request context.

AuthScheme=Okta;User=OktaUser;Password=OktaPassword;SSOLoginURL='https://{subdomain}.okta.com/home/amazon_aws/0oan2hZLgQiy5d6/272';

ADFS

ADFS Configuration

You must create an application used for the single sign-on process to a specific provider.

• Sharepoint SOAP: Please follow this link for detailed instructions on how to set up ADFS for Office 365 for Single Sign-On. Make sure you test the connection and you are able to login to Office 365 from ADFS.
• Amazon S3: Please follow this link for detailed instructions on how to set up ADFS for AWS Single Sign-On. Make sure you test the connection and you are able to login to AWS from ADFS.

Sharepoint SOAP

The following properties must be specified when connecting to a Sharepoint SOAP service provider:

• AuthScheme: Set the AuthScheme to ADFS.
• User: The username of the ADFS account.
• Password: The password of the ADFS account.
• SSOProperties:
  • Domain (optional): It may be required to be set this property if the domain configured on the SSO domain is different than the domain of the User.

AuthScheme='ADFS';User=test;Password=test;SSOProperties='Domain=test.cdata;';

Sharepoint REST V1

The following properties must be specified when connecting to a Sharepoint REST V1 service provider:

• AuthScheme: Set the AuthScheme to ADFS.
• User: The username of the ADFS account.
• Password: The password of the ADFS account.
• SSOProperties:
  • Domain (optional): It may be required to be set this property if the domain configured on the SSO domain is different than the domain of the User.

AuthScheme='ADFS';User=test;Password=test;SSOProperties='Domain=test.cdata;';StorageBaseURL='https://sharepointserver/sites/mysite';

Amazon S3

The following properties must be specified when connecting to a Sharepoint SOAP service provider:

• AuthScheme: Set the AuthScheme to ADFS.
• SSOLoginURL: Set this to the URL of your ADFS instance.
• User: The username of the ADFS account.
• Password: The password of the ADFS account.
• AWSRoleARN (optional): The ARN of the IAM role. Find this on the Summary page of the IAM role.
• AWSPrincipalARN (optional): The ARN of the identity provider. Find this on the identity provider's summary page.

AuthScheme=ADFS;User=username;Password=password;SSOLoginURL='https://sts.company.com';

The ADFS Integrated flow indicates you are connecting with the currently logged in Windows user credentials. To use the ADFS Integrated flow, simply do not specify the User and Password, but otherwise follow the same steps in the ADFS guide above.

The CData Python Connector for XML enables the kind of granular control that is useful in more complex deployments or network topologies; you can use the following connection properties to fine-tune data access, connect through a firewall, or troubleshoot connections.

Resource location

• An empty value automatically assigns the URI to a reference to the current directory, "./". The explicit path to the XML folders depends on the environment of the running application.
• A path to a folder.
• A path to a .zip, .tar. or .gz archive file.
  • Include the file, not just the containing directory. For example: C:\Users\Public\Documents\CSVdata.zip
• A path to a file or stream - in this case you can query the file by executing SELECT * FROM streamedtable.

Modeling tables

Set the following properties to control how the connector models XML as tables:

• IncludeFiles: Set this to a comma-separated list of file extensions to include into the set of files modeled as tables. (By default, .xml and .txt files are modeled.)
  • Specify files by their file extensions in all-caps, without the '.'. For example: "XML,TXT".
  • Archive files are supported (ZIP, TAR, and GZ) and are modeled as if they were folders.
• RowScanDepth: Set this to automatically determine data types by scanning rows up to the specified depth.

Parsing and Merging Multiple Files Into A Single Table

The connector supports reading and parsing multiple files within a single directory and merging the data into a single result set.

To enable this feature, the URI property can be set to a directory with a file mask (e.g. C:\MyDataFiles\*.xml) or a directory (e.g. C:\MyDataFiles). When a directory is specified as the URI, IncludeFiles will be used to identify the file types to include.

This functionality is also available on cloud based service providers such as Google Drive (e.g. gdrive://remotepath/*.xml), Amazon S3 (e.g. s3://remotepath/*.xml), FTP (ftp://server:port/remotepath/*.xml), etc.

When setting URI to a directory, be sure to include an ending forward slash (e.g. s3://remotepath/) to denote that its a directory.

A comma-separated list of URIs is supported to include multiple files.

To retrieve all files (including those without a file extension), you can use a file mask of '*' (e.g. s3://remotepath/*) or set IncludeFiles to include a '*' entry.
To include just files without an extension, you can set IncludeFiles to include a 'NOEXT' entry.

When parsing a batch of files, the files are put into a queue.
Each file will be retrieved and parsed in a streaming fashion with each row pushed as it is parsed.
Therefore files will never be stored in memory or stored in a temporary location on disk, thus limiting the amount of memory usage required.

Metadata Discovery

When reading multiple files, the connector will use the first file identified to discovery metadata.
A single file is used for metadata discovery for performance reasons.
In cases where the first identified file contains partial XML data (as compared to the other files in the directory), columns/data for the other files may not be returned.
This is due to the connector not being able to properly identify all the columns available in the selected files from the single file used for metadata discovery.

To work around these cases, a "MetadataDiscoveryURI" option can be set via the Other property (e.g. MetadataDiscoveryURI=file:///C:\MyDataFiles\main.xml).
When a MetadataDiscoveryURI is specified, the connector will use the specified URI to discover tables and columns.
Once the metadata has been discovered, the URI value will be used to retrieve and parse the XML data.

Error Handling

When parsing multiple files, there may be cases where a file is not able to be parsed (such as invalid XML, a network connectivity issue, etc.).
In such cases, the connector will not return an exception message but rather will log the error in a temporary table called ErrorInfo#TEMP.
This is done so that failures don't occur in the middle of reading a large batch of files and having to start over.
Instead the temporary table (ErrorInfo#TEMP) can be queried after the initial query has finished.
This will allow you to identify if there were any files that failed to parse, thus allowing you to retry the failed requests and merging them with your primary result set.

To retrieve the error list, you can issue the following query: SELECT * FROM ErrorInfo#TEMP.
This table contains two columns: URI and Description.
URI contains the URI for the single file that failed (which can be set via the URI property to retry).
Description contains the description as to why the file failed to parse. In the case that no errors occurred, an empty result set will be returned.

Navigating Subdirectories

The connector supports navigating subdirectories when parsing local files.
This functionality is exposed by using the '*' wildcard character in the directory name of the URI value.

For example, suppose you have a 'Data' directory that contains folders with unique names (such as a date value) each of which contain similar XML data files.
You can read all these files into a single table by setting URI to 'file:///C:\Data\*\*.xml' or you can set it to the directory without a file mask 'file:///C:\Data\*'.

Partial directory matching is also supported.
For example, to retrieve all XML files within folders starting with '2018' the following URI value can be used: file:///C:\Data\2018*\*.xml.

Note: Using wildcards in directory names is not applicable when connecting to cloud resources.

Kerberos

To authenticate to XML with Kerberos, set AuthScheme to NEGOTIATE.

Authenticating to XML via Kerberos requires you to define authentication properties and to choose how Kerberos should retrieve authentication tickets.

Retrieve Kerberos Tickets

The connector provides three ways to retrieve the required Kerberos ticket, depending on whether or not the KRB5CCNAME and/or KerberosKeytabFile variables exist in your environment.

MIT Kerberos Credential Cache File

This option enables you to use the MIT Kerberos Ticket Manager or kinit command to get tickets. With this option there is no need to set the User or Password connection properties.

This option requires that KRB5CCNAME has been created in your system.

To enable ticket retrieval via MIT Kerberos Credential Cache Files:

1. Ensure that the KRB5CCNAME variable is present in your environment.
2. Set KRB5CCNAME to a path that points to your credential cache file. (For example, C:\krb_cache\krb5cc_0 or /tmp/krb5cc_0.) The credential cache file is created when you use the MIT Kerberos Ticket Manager to generate your ticket.
3. To obtain a ticket:
   1. Open the MIT Kerberos Ticket Manager application.
   2. Click Get Ticket.
   3. Enter your principal name and password.
   4. Click OK.

   If the ticket is successfully obtained, the ticket information appears in Kerberos Ticket Manager and is stored in the credential cache file.

The connector uses the cache file to obtain the Kerberos ticket to connect to XML.

Note: If you would prefer not to edit KRB5CCNAME, you can use the KerberosTicketCache property to set the file path manually. After this is set, the connector uses the specified cache file to obtain the Kerberos ticket to connect to XML.

Keytab File

If your environment lacks the KRB5CCNAME environment variable, you can retrieve a Kerberos ticket using a Keytab File.

To use this method, set the User property to the desired username, and set the KerberosKeytabFile property to a file path pointing to the keytab file associated with the user.

User and Password

If your environment lacks the KRB5CCNAME environment variable and the KerberosKeytabFile property has not been set, you can retrieve a ticket using a user and password combination.

To use this method, set the User and Password properties to the user/password combination that you use to authenticate with XML.

Enabling Cross-Realm Authentication

To enable this kind of cross-realm authentication, set the KerberosRealm and KerberosKDC properties to the values required for user authentication. Also, set the KerberosServiceRealm and KerberosServiceKDC properties to the values required to obtain the service ticket.

General Changes

In this section we will show how to control the various schemes that the connector offers to bridge the gap with relational SQL and XML services. The CData Python Connector for XML provides a managed way for you to use the two prevailing techniques for dealing with nested XML data:

• Parsing the data structure and building a relational model based on the existing hierarchy.
• Drilling down into the nested elements using horizontal and vertical flattening.

Parsing Hierarchical Data

By default, the connector automatically detects the rows in a document, so that you do not need to know the structure of the XML to query it with SQL. Set the DataModel property to choose a basic configuration of how the connector models the rows into tables.

Flattening Objects and Arrays into Rows

To flatten data, you only need to be familiar with two data structures:

• Object: Any parent element that does not repeat at the same height.
• Array: Any element that repeats at the same height.

In the following example from the people collection, maintenance is an object array, since each maintenance node has child elements.

<maintenance>
  <date>07-17-2017</date>
  <desc>oil change</desc>
</maintenance>
<maintenance>
  <date>01-03-2018</date>
  <desc>new tires</desc>
</maintenance> 

Configuring Automatic Schema Discovery

The connector discovers columns and data types by scanning the RowScanDepth count of XML objects in XML arrays. Set the FlattenObjects and FlattenArrays properties to configure how nested data is flattened into columns; see Automatic Schema Discovery for examples.

Executing SQL to XML

Any relation you can access through flattening you can also access with an ad-hoc SQL query. The connector enables you to query nested data with the following capabilities:

• Vertical Flattening: Access nested object arrays as separate tables.
• Free-Form Queries: Query any nested structure without flattening the data.
• XML Functions: Manipulate the data returned to perform client-side aggregation and transformations.

Customizing Schemas

Customizing Schemas enables you to project your chosen relational structure on top of an XML document. This allows you to choose the names of columns, their data types, and the locations of their values in the document.

System Catalog

The System Tables reflect the schemas you configured, custom schemas or dynamically discovered. The Stored Procedures surface additional functionality in the connector's data processing operations that cannot be modeled as SELECT, INSERT, UPDATE, or DELETE. You can find the reported stored procedures defined in .rsb files in the folder specified by Location -- if Location is not specified, the db subfolder of the installation directory.

Below is the raw data used throughout this chapter. The data includes entries for people, the cars they own, and various maintenance services performed on those cars:

<?xml version="1.0" encoding="UTF-8" ?>
<root>
  <rootAttr1>rootValue1</rootAttr1>
  <people>
    <personal>
      <age>20</age>
      <gender>M</gender>
      <name>
        <first>John</first>
        <last>Doe</last>
      </name>
    </personal>
    <jobs>support</jobs>
    <jobs>coding</jobs>
    <vehicles>
      <type>car</type>
      <model>Honda Civic</model>
      <insurance>
        <company>ABC Insurance</company>
        <policy_num>12345</policy_num>
      </insurance>
      <features>sunroof</features>
      <features>rims</features>
      <maintenance>
        <date>07-17-2017</date>
        <desc>oil change</desc>
      </maintenance>
      <maintenance>
        <date>01-03-2018</date>
        <desc>new tires</desc>
      </maintenance>
    </vehicles>
    <vehicles>
      <type>truck</type>
      <model>Dodge Ram</model>
      <insurance>
        <company>ABC Insurance</company>
        <policy_num>12345</policy_num>
      </insurance>
      <features>lift kit</features>
      <features>tow package</features>
      <maintenance>
        <date>08-27-2017</date>
        <desc>new tires</desc>
      </maintenance>
      <maintenance>
        <date>01-08-2018</date>
        <desc>oil change</desc>
      </maintenance>
    </vehicles>
    <addresses>
      <type>work</type>
      <zip>12345</zip>
    </addresses>
    <addresses>
      <type>home</type>
      <zip>12357</zip>
    </addresses>
    <source>internet</source>
  </people>
  <people>
    <personal>
      <age>24</age>
      <gender>F</gender>
      <name>
        <first>Jane</first>
        <last>Roberts</last>
      </name>
    </personal>
    <jobs>sales</jobs>
    <jobs>marketing</jobs>
    <source>phone</source>
    <vehicles>
      <type>car</type>
      <model>Toyota Camry</model>
      <insurance>
        <company>Car Insurance</company>
        <policy_num>98765</policy_num>
      </insurance>
      <features>upgraded stereo</features>
      <maintenance>
        <date>05-11-2017</date>
        <desc>tires rotated</desc>
      </maintenance>
      <maintenance>
        <date>11-03-2017</date>
        <desc>oil change</desc>
      </maintenance>
    </vehicles>
    <vehicles>
      <type>car</type>
      <model>Honda Accord</model>
      <insurance>
        <company>Car Insurance</company>
        <policy_num>98765</policy_num>
      </insurance>
      <features>custom paint</features>
      <features>custom wheels</features>
      <maintenance>
        <date>10-07-2017</date>
        <desc>new air filter</desc>
      </maintenance>
      <maintenance>
        <date>01-13-2018</date>
        <desc>new brakes</desc>
      </maintenance>
    </vehicles>
    <addresses>
      <type>home</type>
      <zip>98765</zip>
    </addresses>
    <addresses>
      <type>work</type>
      <zip>98753</zip>
    </addresses>
  </people>
  <rootAttr2>rootValue2</rootAttr2>
  <rootAttr3>rootValue3</rootAttr3>
  <rootAttr3>rootValue4</rootAttr3>
</root>

The connector offers three basic configurations to model nested data in XML as tables.

• Flattened Documents Model: Implicitly join nested object arrays into a single table.
• Relational Model: Model object arrays as individual tables containing a primary key and a foreign key that links to the parent document.
• Top-Level Document Model: Model a top-level view of an XML document. Nested object arrays are returned as XML aggregates.

For users who simply need access to the entirety of their XML data, flattening the data into a single table is the best option. The connector will use streaming and only parses the XML data once per query in this mode.

Joining Object Arrays into a Single Table

With DataModel set to "FlattenedDocuments", the connector returns a separate table for each object array, but implicitly JOINed to the parent table. Any nested sibling XPath values (child paths at the same height) will be treated as a SQL CROSS JOIN.

Example

Below is a sample query and the results, based on the sample document in Raw Data and parsing based on the XPaths /root/people, /root/people/vehicles, and /root/people/vehicles/maintenance. This implicitly JOINs the people element with the vehicles element and implicitly JOINs the vehicles element with the maintenance element.

Connection String

Use the following connection string to query the Raw Data in this example.

URI=C:\people.txt;DataModel=FlattenedDocuments;XPath='/root/people;/root/people/vehicles;/root/people/vehicles/maintenance;'

Query

The following query drills into the nested elements in each people element. Since the XPath property included the vehicles node as an XML path, you can query an element of a vehicle explicitly.

SELECT
  [personal.age] AS age,
  [personal.gender] AS gender,
  [personal.name.first] AS name_first,
  [personal.name.last] AS name_last,
  [source],
  [type],
  [model],
  [insurance.company] AS ins_company,
  [insurance.policy_num] AS ins_policy_num,
  [date] AS maint_date,
  [desc] AS maint_desc
FROM
  [people]

Results

With horizontal and vertical flattening based on the described paths, each vehicle element is implicitly JOINed to its parent people element and each maintenance element is implicitly JOINed to its parent vehicle element.

See Also

• Automatic Schema Discovery: Configure the columns reported in the table schemas.
• Free-Form Queries: Use dot notation to select nested data.
• Vertical Flattening: Query nested data as separate tables.
• XML Functions: Manipulate the data returned to perform client-side aggregation and transformations.

Using a top-level document view of the data provides ready access to top-level elements. The connector returns nested elements in aggregate, as single columns.

One aspect to consider is performance. You forego the time and resources to process and parse nested elements -- the connector parses the returned data once, using streaming to read the data. Another consideration is your need to access any data stored in nested parent elements, and the ability of your tool or application to process XML.

Modeling a Top-Level Document View

With DataModel set to "Document" (the default), the connector scans only a single element, the top-level element by default. The top-level elements are available as columns due to default object flattening. Nested elements are returned as aggregated XML.

You can set XPath to specify an element other than the top-level one.

Example

Below is a sample query and the results, based on the sample document in Raw Data. The query results in a single "people" table based on the XPath "/root/people".

Connection String

Set the DataModel connection property to "Document" to perform the following query and see the example result set. The connector will scan only the XPath value below:

URI=C:\people.txt;DataModel=Document;XPath='/root/people';

Query

The following query pulls the top-level elements and the subelements of the vehicles element into the results.

SELECT
  [personal.age] AS age,
  [personal.gender] AS gender,
  [personal.name.first] AS name_first,
  [personal.name.last] AS name_last,
  [source],
  [vehicles]
FROM
  [people]
  

Results

With a document view of the data, the personal element is flattened into 4 columns and the source and vehicles elements are returned as individual columns, resulting in a table with 6 columns.

  <vehicles><type>car</type><model>Honda Civic</model><insurance><company>ABC Insurance</company><policy_num>12345</policy_num></insurance><features>sunroof</features><features>rims</features><maintenance><date>07-17-2017</date><desc>oil change</desc></maintenance><maintenance><date>01-03-2018</date><desc>new tires</desc></maintenance></vehicles><vehicles><type>truck</type><model>Dodge Ram</model><insurance><company>ABC Insurance</company><policy_num>12345</policy_num></insurance><features>lift kit</features><features>tow package</features><maintenance><date>08-27-2017</date><desc>new tires</desc></maintenance><maintenance><date>01-08-2018</date><desc>oil change</desc></maintenance></vehicles>

  <vehicles><type>car</type><model>Toyota Camry</model><insurance><company>Car Insurance</company><policy_num>98765</policy_num></insurance><features>upgraded stereo</features><maintenance><date>05-11-2017</date><desc>tires rotated</desc></maintenance><maintenance><date>11-03-2017</date><desc>oil change</desc></maintenance></vehicles><vehicles><type>car</type><model>Honda Accord</model><insurance><company>Car Insurance</company><policy_num>98765</policy_num></insurance><features>custom paint</features><features>custom wheels</features><maintenance><date>10-07-2017</date><desc>new air filter</desc></maintenance><maintenance><date>01-13-2018</date><desc>new brakes</desc></maintenance></vehicles>

See Also

• Automatic Schema Discovery: Configure column discovery with horizontal flattening.
• Free-Form Queries: Use dot notation to select nested data.
• Vertical Flattening: Query nested data as separate tables.
• XML Functions: Manipulate the data returned to perform client-side aggregation and transformations.

The CData Python Connector for XML can be configured to create a relational model of the data in the XML file or source, treating each XPath as an individual table containing a primary key and a foreign key that links to the parent document. This is particularly useful if you need to work with your XML data in existing BI, reporting, and ETL tools that expect a relational data model.

Joining Nested Arrays as Tables

With DataModel set to "Relational", any JOINs are controlled by the query. Any time you perform a JOIN query, the XML file or source will be queried once for each table included in the query.

Example

Below is a sample query against the sample document in Raw Data, using a relational model based on the XML paths "/root/people", "/root/people/vehicles", and "/root/people/vehicles/maintenance".

Connecting String

Set the DataModel connection property to "Relational" and set the XPath connection property to "/root/people;/root/people/vehicles;/root/people/vehicles/maintenance;" to perform the following query and see the example result set.

URI=C:\people.txt;DataModel=Relational;XPath='/root/people;/root/people/vehicles;/root/people/vehicles/maintenance;'

Query

The following query explicitly JOINs the people, vehicles, and maintenance tables.

SELECT 
  [people].[personal.age] AS age, 
  [people].[personal.gender] AS gender, 
  [people].[personal.name.first] AS first_name, 
  [people].[personal.name.last] AS last_name, 
  [people].[source], 
  [vehicles].[type], 
  [vehicles].[model], 
  [vehicles].[insurance.company] AS ins_company, 
  [vehicles].[insurance.policy_num] AS ins_policy_num, 
  [maintenance].[date] AS maint_date, 
  [maintenance].[desc] AS maint_desc
FROM 
  [people]
JOIN 
  [vehicles] 
ON 
  [people].[_id] = [vehicles].[people_id]
JOIN 
  [maintenance] 
ON 
  [vehicles].[_id] = [maintenance].[vehicles_id]

Results

In the example query, each maintenance element is JOINed to its parent vehicle element, which is JOINed to its parent people element to produce a table with 8 rows (2 maintenance entries for each of 2 vehicles each for 2 people).

See Also

• Automatic Schema Discovery: Configure the columns reported in the table schemas.
• Free-Form Queries: Use dot notation to select nested data.
• Vertical Flattening: Query nested data as separate tables.
• XML Functions: Manipulate the data returned to perform client-side aggregation and transformations.

By default, the connector automatically infers a relational schema by inspecting a series of XML objects in an array. This section describes the connection properties available to configure these dynamic schemas.

Discovering Tables

This section shows how to fine-tune the discovered schemas by fine-tuning the row scan. The rows detected depend on the RowScanDepth, DataModel, and XPath properties.

• RowScanDepth: This setting determines the number of object arrays to scan to find the rows and discover the columns. The process follows nested objects, counting 1 object array as 1 row.
• DataModel: Select how you want to represent the object arrays as tables. This setting also affects the objects that are scanned: the Relational and FlattenedDocuments settings will find all object arrays (including nested ones) in RowScanDepth items. The Document setting will only find the first object array.

• XPath: Set this to explicitly specify the object arrays you want to expose. Note that if DataModel is set to Document, you can only set one, top-level XPath. See Parsing Hierarchical Data for examples of accessing a sample document with different XPath values.