Configure a Connection Profile

From the CData ribbon, click Get Data and select From Azure Data Lake Storage connection/s to launch the CData Query window. To setup a new connection, you will have to click the New Azure Data Lake Storage Connection button. Here you can set the connection settings, test the connection, and save the connection profile.

Connecting to CData Excel Add-In for Azure Data Lake Storage Gen 2

To connect to a Gen 2 DataLakeStorage account, set the following properties:

• Account: The name of the storage account.
• FileSystem: The file system name used for this account. For example, the name of an Azure Blob Container.
• Directory (Optional): The path to the location where the replicated file should be stored. If no path is specified, the file is stored in the root directory.

Authenticating to CData Excel Add-In for Azure Data Lake Storage Gen 2

CData Excel Add-In for Azure Data Lake Storage supports four different ways to authenticate: using an AccessKey, using a Shared Access Signature, Azure Active Directory OAuth (AzureAD), and Managed Service Identity (AzureMSI).

Access Key

To connect using an access key, you must first obtain an available access key for the ADLS Gen2 storage account.

At the Azure portal:

1. Go to your ADLS Gen2 Storage Account.
2. Under Settings, select Access keys.
3. Copy the value for one of the available access keys to the AccessKey connection property.

When you are ready to connect, set these properties:

• AuthScheme: AccessKey.
• AccessKey: The access key value you just retrieved from the Azure Portal.

Shared Access Signature (SAS)

To connect using a Shared Access Signature, you must first generate one using the Azure Storage Explorer tool.

When you are ready to connect, set these properties:

• AuthScheme: SAS.
• SharedAccessSignature: The value of the Shared Access Signature you just generated.

Azure AD

Azure AD is Microsoft’s multi-tenant, cloud-based directory and identity management service. It is user-based authentication that requires that you set AuthScheme to AzureAD.

Authentication to Azure AD over a Web application always requires the creation of a custom OAuth application. For details, see Creating an Azure AD Application.

Desktop Applications

CData provides an embedded OAuth application that simplifies connection to Azure AD from a Desktop application.

You can also authenticate from a desktop application using a custom OAuth application. (For further information, see Creating an Azure AD Application.) To authenticate via Azure AD, set these parameters:

• AuthScheme: AzureAD.

• Custom applications only:

  • OAuthClientId: The client Id assigned when you registered your custom OAuth application.
  • OAuthClientSecret: The client secret assigned when you registered your custom OAuth application.
  • CallbackURL: The redirect URI you defined when you registered your custom OAuth application.

When you connect, the add-in opens Azure Data Lake Storage's OAuth endpoint in your default browser. Log in and grant permissions to the application.

The add-in completes the OAuth process, obtaining an access token from Azure Data Lake Storage and using it to request data. The OAuth values are saved in the path specified in OAuthSettingsLocation. These values persist across connections.

When the access token expires, the add-in refreshes it automatically.

Managed Service Identity (MSI)

If you are running Azure Data Lake Storage on an Azure VM and want to leverage MSI to connect, set AuthScheme to AzureMSI.

User-Managed Identities

To obtain a token for a managed identity, use the OAuthClientId property to specify the managed identity's "client_id".

When your VM has multiple user-assigned managed identities, you must also specify OAuthClientId.

Connection Properties

The Connection properties describe the various options that can be used to establish a connection.

Managing Connections

After successfully authenticating to Azure Data Lake Storage you will be able to customize the data you are importing. To learn more about this, see Managing Connections.

See Also

• Querying Data: Use the data selection wizard to pull data into a spreadsheet. You can also configure scheduled data refresh here.
• Using the Excel Add-In: Find other ways to interact with Azure Data Lake Storage data, such as using the available CData Excel Functions.