Skip to Content

Configuring the destination as Snowflake

To configure the destination as Snowflake for BMC Helix Data Connector, you must perform a set of tasks both in the BMC Helix Data Connector UI and in Snowflake Marketplace. 

Process overviewEdit

Perform the tasks in the following order to configure Snowflake as the destination for BMC Helix Data Connector:

TaskActionReference
1Configure Snowflake as the destination on the BMC Helix Data Connector UI.To configure Snowflake as the destination in BMC Helix Data Connector UI
2Configure BMC Helix Data Connector in Snowflake by obtaining the necessary details from BMC Helix Support and completing the steps in Snowflake Marketplace.
3Check the Snowflake destination details to confirm your destination is successfully created.To check the Snowflake destination details
4(Optional) Revoke the permissions on the BMC Helix Data Connector application after the installation and destination configuration are complete.To revoke the permissions on BMC Helix Data Connector application
5(Optional) Migrate an existing Snowflake destination from username and password authentication to key-pair authentication.To migrate an existing Snowflake destination to key-pair authentication 
6(Optional) Rotate the keys for a user configured with key-pair authentication.To rotate keys for key-pair authentication

To configure Snowflake as the destination in BMC Helix Data Connector UIEdit

  1. On the Helix Data Connector UI, navigate to the Administration tab and select Configure destination.
    Configure destination
  2. On the Destination UI screen, click Configure destination.

  3. On the Destination configuration screen, select the destination type as Snowflake from the list.
    The following message is displayed:
    Please login to Snowflake Marketplace and search for the BMC Snowflake Connector to begin your destination configuration there.
    Your Snowflake account will be connected to your Helix Data Connector after you have completed the steps in Snowflake Marketplace.

  4. Navigate to the Snowflake Marketplace and specify the destination configuration details for Snowflake.
    https://www.snowflake.com/en/data-cloud/marketplace/
  5. Install BMC Helix Data Connector for Snowflake and configure the application.
  6. After configuring BMC Helix Data Connector for Snowflake, the details are registered in the BMC Helix Data Connector UI as shown in the following image:

    Snowflake destination

To configure BMC Helix Data ConnectorEdit

Obtain the necessary RFC details from BMC Helix Support and complete the following steps in Snowflake Marketplace.

Step 1: Obtain Egress IPs from BMC Helix Support

  • Submit a Request for Change (RFC) to BMC Helix Support or your Service Desk. 
    For more information, see the Request for Change process in the BMC Helix Subscriber Information online documentation.
  • If you are syncing data to Snowflake, mention clearly in your RFC that you are configuring Snowflake as the destination.
  • After the RFC is complete, you will receive the BMC Egress Network address, which is required for setting up network rules in Snowflake.
  • This Egress address must be added to Snowflake’s allowlist to enable successful data synchronization.

Step 2: Configure network rules in Snowflake

  1. Navigate to SnowSight.
  2. Navigate to Admin > Security > Network Rules.
  3. Click + Network Rule.
    Network Rules
  4. In the Create Network Rule dialog box, provide the following information:
    1. Provide a name for the network rule.
    2. Set the type to IPv4.
    3. Set the mode to Ingress.
    4. Enter the identifier (Egress IP) provided by BMC Support and press the Enter key.
      The identifier is added to the network rule.
    5. Click Create Network Rule.
      NetworkRules1
  5. (Optional) If you have existing network policies, you can add this rule to the relevant policy.

Step 3: Access the BMC Helix Data Connector in Snowflake Marketplace 

Complete the following steps in Snowflake Marketplace:

  1. Navigate to Snowflake Marketplace and complete the following steps:
    1. Search for BMC Helix Data Connector and select the BMC Helix Data Connector product from the list.
      Snowflake Configuration details
    2. Install BMC Helix Data Connector by clicking GET. 
      Snowflake configuration details
    3. After BMC Helix Data Connector is installed successfully, click Done.
      Snowflake Configuration details
  2. Click Apps on the left pane and click BMC Helix Data Connector.

    SF5_Updated.png
  3. On the dialog box, click Grant Privileges.
    Grant Privileges
  4. Click Connect to connect to an external endpoint and provide the required connection details to push configuration settings to BMC Helix Innovation Suite.
    Grant Privileges
  5. Review the prerequisites and click Start Configuration.
  6. Provide the following information to configure the BMC Helix Connector application:
    FieldDescription
    BMC Helix URLThe BMC Helix URL for this account.
    BMC Helix usernameThe BMC Helix username for this account.
    Private Key A secure, encrypted string used in key-pair authentication to verify the identity of a user or system when connecting to Snowflake.
    Pass PhraseA secret string used to unlock or decrypt the Private Key. It adds an extra layer of security to the authentication process.
  7. Click Configure. 

Step 4: Configure Snowflake destination with encrypted key-pair authentication 

  1. Generate an encrypted private key by using OpenSSL.
    openssl genpkey -algorithm RSA -pkeyopt rsa_keygen_bits:2048 |
    openssl pkcs8 -topk8 -v2 aes-256-cbc -v2prf hmacWithSHA256   -out rsa_key_aes256.p8
  2. Generate the associated public key by running the following command:
    openssl pkey -in rsa_key_aes256.p8 -pubout -out rsa_key_aes256.pub
  3. Run the following MS SQL commands in Snowflake to create the user, assign roles, and grant privileges: 
    CREATE USER key_pair_user WITH LOGIN_NAME='key_pair_user', DISPLAY_NAME='Key Pair User',
    type=Service, RSA_PUBLIC_KEY='<public-key>';
    CREATE ROLE IF NOT EXISTS <user-defined-role>;
    GRANT ROLE <user-defined-role> TO USER <user-defined-name>;
    GRANT APPLICATION ROLE BMC_HELIX_DATA_CONNECTOR_INSTANCE.HELIX_APP_ROLE  TO ROLE <user-defined-role> ;
    Replace the name of the application instance from BMC_HELIX_DATA_CONNECTOR_INSTANCE with the value you specify during installation.
    GRANT USAGE ON WAREHOUSE <user-selected-warehouse> TO ROLE <user-defined-role> ;
  4. [Optional] To create a New Warehouse for storing your BMC Helix data, create the new warehouse and grant access to the warehouse before performing the Snowflake / BMC Helix configuration steps. 

Step 5: Enter Snowflake destination configuration details

  1. Enter the following details to create the corresponding objects in your Snowflake environment:
    FieldDescription
    WarehouseA new warehouse of size Large (8 credits/hour) is created to run the connector.
    You can modify these settings later if needed.
    Destination databaseThe selected database that stores the ingested data.
    Destination schemaA new schema is created to store the ingested data.
    HostThe host address of the server you are connecting to, in the form of an IP address or domain name.
    PortThe port number that specifies the communication channel to use when connecting to the host.
    RoleThe role required to authenticate and access the system or service.
    AccountThe username or credentials required to authenticate and access the system or service.
    Snowflake usernameThe Snowflake username to configure BMC Helix Data Connector. 
    Snowflake passwordThe Snowflake password to configure BMC Helix Data Connector.
  2. Click Configure. 
    The configuration details are saved in BMC Helix Innovation Studio. 
  3. After completing the Snowflake configuration details in Snowflake Marketplace, access BMC Helix Innovation Studio to see if the Snowflake destination has been created successfully.

You must configure network rules in your Snowflake account to allow connections from BMC Helix SaaS. If your Snowflake instance is not publicly accessible, include BMC’s Egress IPs in your allowlist for a successful connection. To proceed with this configuration, raise a support case with BMC Helix Support.

To check the Snowflake destination detailsEdit

Perform the following steps to confirm your destination is successfully created:

  1. Log into BMC Helix Innovation Studio.
  2. Navigate to the Administration tab and select Configure destination.
  3. Check if the Snowflake details have been populated in read-only mode.
    Destination_Snowflake

To revoke the permissions on the BMC Helix Data Connector applicationEdit

The Snowflake application requests permission to create a database, warehouse, and so on. If you have concerns regarding this level of access to BMC Helix Data Connector, revoke the permissions after the installation and destination configuration are complete. To revoke access, log into your Snowflake account and remove the permission.

Perform the following steps to revoke the permission:

  1. Log into your Snowflake Marketplace account.
  2. Click Apps on the left pane.
    SF5_Updated.png
  3. Select BMC Helix Data Connector from the list.
  4. Click Settings SettingsIcon.png on the top of the screen.
  5. On the BMC Helix Data Connector UI, click the Privileges tab and then click the Edit icon.
    Edit Privileges
  6. Review the privileges for BMC Helix Data Connector application and update/revoke them as required.
    Revoke Privileges
  7. To save the changes, click Update Privileges.

To migrate an existing Snowflake destination to key-pair authentication Edit

Information
Important

While new configurations must use key-pair authentication, existing username and password authentication setups will continue to function post-upgrade, provided Snowflake still supports the username and password authentication method.

Perform the following steps to migrate an existing Snowflake destination from username and password authentication to key-pair authentication:

  1. Generate a new public-private key pair by using the following OpenSSL commands:
    1. Generate the encrypted private key:
      openssl genpkey -algorithm RSA -pkeyopt rsa_keygen_bits:2048 |
      openssl pkcs8 -topk8 -v2 aes-256-cbc -v2prf hmacWithSHA256   -out rsa_key_aes256.p8 
    2. Generate the associated public key:
      openssl pkey -in rsa_key_aes256.p8 -pubout -out rsa_key_aes256.pub
  2. Create a new user of type SERVICE and attach the public key you created in the previous step by running the following command:
    CREATE USER key_pair_user WITH LOGIN_NAME='key_pair_user', DISPLAY_NAME='Key Pair User',
    TYPE=Service, RSA_PUBLIC_KEY='MIIBIjANBgk.....wIDAQAB'
  3. Replicate the grants from the old user to the new user.
    • If you granted permissions using the BMC-recommended SQL commands, run the following command:
      GRANT ROLE <existing role configured> TO USER key_pair_user
    • If you did not use the recommended SQL commands, manually replicate any additional grants. You can view the existing grants on the old user by running the following command:
      SHOW GRANTS TO USER <old-user-name>;
  4. In BMC Helix Data Connector, attach the public key to the new user, provide the encrypted private key and passphrase, and save the configuration.
  5. Raise a request with BMC Helix Support to restart the Data Connect Engine.
  6. After the restart, BMC Helix Data Connector starts using key-pair authentication and resumes the jobs from the last committed offset.
  7. After confirming that the configuration works as expected, you can delete the old user if required.

To rotate keys for key-pair authenticationEdit

Perform the following steps to rotate the keys for a user configured with key-pair authentication:

  1. Create a new public-private key pair by using the following OpenSSL commands:
    1. Generate the encrypted private key:
      openssl genpkey -algorithm RSA -pkeyopt rsa_keygen_bits:2048 |
      openssl pkcs8 -topk8 -v2 aes-256-cbc -v2prf hmacWithSHA256  -out rsa_key_aes256.p8       
    2. Generate the associated public key:
      openssl pkey -in rsa_key_aes256.p8 -pubout -out rsa_key_aes256.pub
  2. Attach the new public key to the user by running the following command:
    ALTER USER key_pair_user SET rsa_public_key_2='MIIBIjANB....QIDAQAB'
  3. Provide the new public key and passphrase in the BMC Helix Data Connector for Snowflake and save the configuration.
  4. Raise a request with BMC Helix Support to restart the Data Connect Engine.
  5. After the restart is complete, the BMC Helix Data Connector application uses the new keys and resumes the jobs from the last committed offset.
  6. Switch to the new public key by running the following command:
    ALTER USER key_pair_user UNSET RSA_PUBLIC_KEY;

After this step, the new key-pair authentication takes effect.

Related topicsEdit

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC Helix Innovation Suite 25.4