Snowflake connector for MuleSoft using Azure Oauth Client Credentials

Summary

This video tutorial guides viewers on integrating MuleSoft with Snowflake using the client credential grant pattern for machine-to-machine communication. It covers creating an application in Azure, setting up application ID URI, app roles, and permissions. The video demonstrates generating a token, creating a security integration in Snowflake, and verifying the token. It concludes with a Java client program example, illustrating how to fetch data from Snowflake using MuleSoft, highlighting the process of setting up a custom connector due to limitations with existing connectors.

Takeaways

• 🔧 The video demonstrates how to connect Mulesoft to Snowflake using the client credentials method for machine-to-machine communication.
• 🚀 The process involves creating an application in Azure and configuring it for client credentials rather than user-based access.
• 🛠️ The video shows the steps for registering an application in Azure, setting the application ID URI, and assigning roles.
• 🔐 Snowflake requires security integration with Azure by using JWT tokens, which are created and verified through Azure endpoints.
• 📝 After obtaining a valid token, the token's role is verified, and it maps to a user account in Snowflake.
• 💡 A custom Mulesoft connector is built because the standard Mulesoft connector doesn't fully support OAuth or JDBC for Snowflake.
• 💻 The developer uses a Java client program to generate tokens and establish the connection, which is then integrated with Mulesoft.
• 🔄 The Snowflake connection is demonstrated by running SQL queries using a database and schema defined in the configuration.
• 📊 The program successfully returns results from Snowflake, converting them to JSON and executing scheduled tasks.
• 🛠️ The custom connector is available on GitHub for others who may need to integrate Mulesoft with Snowflake via Azure's external authentication.

Q & A

What is the main topic of the video?

The main topic of the video is about the options available for connecting MuleSoft to Snowflake using client credential pattern.

Why is the client credential pattern used in this context?

The client credential pattern is used for machine-to-machine communication, allowing the application to authenticate itself with Snowflake on behalf of the user.

What is the role of Azure in the process described in the video?

Azure is used to create and manage application registrations, which are necessary for provisioning applications and obtaining tokens for use with Snowflake.

How does the video demonstrate the creation of an application in Azure?

The video demonstrates the creation of an application in Azure by navigating to 'Application Registration', then 'New Registration', and adding an Application ID URI.

What is the significance of the application ID URI in the process?

The application ID URI is significant as it identifies the application to Azure and Snowflake, and is used when creating app roles and setting permissions.

Why is it necessary to add app roles in the Azure application?

App roles are added to define the permissions and roles that the application will have within Azure, which is essential for controlling access and security.

What is the purpose of creating a token in the video?

The purpose of creating a token is to authenticate the application with Snowflake, allowing it to access and interact with Snowflake resources on behalf of the user.

How is the security integration created with Snowflake in the video?

The security integration is created by providing the necessary configuration details such as the audience, issuer, and JWKS URI, which are obtained from Azure, and then creating a security integration in Snowflake.

What is the role of the 'system verify external browser token' command in Snowflake?

The 'system verify external browser token' command is used in Snowflake to verify the token obtained from Azure, ensuring it is valid and can be used for authentication.

How does the video address the creation of a Java client program for MuleSoft?

The video addresses the creation of a Java client program by showing how to convert the obtained token and credentials into a format that can be used by MuleSoft connectors, and then demonstrating how to implement this in a custom connector.

What is the significance of the 'username extractor' mentioned in the video?

The 'username extractor' is significant as it extracts the username from the token, which is then used to create a user in Snowflake that corresponds to the application for authentication purposes.

Outlines

00:00

🔗 Setting Up Azure for Snowflake Integration

This paragraph explains the process of connecting MuleSoft to Snowflake using client credential flow. It details the creation of an application in Azure, adding an application ID URI, and setting up app roles. The speaker demonstrates how to configure API permissions and scopes, and how to create a token using client ID and secret. The process involves setting up a security integration in Snowflake with the necessary configurations, including the audience and issuer URIs, and verifying the token's validity.

05:02

🛠️ Creating a Custom MuleSoft Connector

The speaker discusses creating a system security integration in Snowflake and the need to map the 'aud' claim from the token to the 'sub' claim in Snowflake. They also mention creating a username and password for Snowflake, which is derived from the token's 'sub' claim. The paragraph further explains the process of creating a Java client program to connect to Snowflake and converting it into a MuleSoft connector. The speaker highlights the need to use client ID and secret instead of username and password for authentication.

10:04

💻 Debugging and Testing the Custom Connector

In this paragraph, the speaker demonstrates how to debug and test the custom MuleSoft connector. They show the process of generating a token, extracting the 'aud' claim, and using it to establish a connection with Snowflake. The speaker also explains how to execute SQL commands using the connector and how to transform the results into a JSON format. The debugging process includes setting breakpoints and stepping through the code to ensure that the connector is functioning correctly.

15:05

📝 Finalizing the MuleSoft Connector and Code Review

The final paragraph covers the completion of the MuleSoft connector and a review of the code. The speaker discusses attaching the source code to the connector for easier debugging and maintenance. They also mention the need for additional JAR files to support JSON and login functionalities. The speaker provides a walkthrough of the code, explaining the connection configuration, the execution of SQL queries, and the conversion of results into a byte array. The paragraph concludes with the speaker's intention to check in the code to GitHub for further collaboration and improvement.

Keywords

💡Mulesoft

Mulesoft is a platform that provides integration software to connect applications, data, and devices. In the video, the focus is on using Mulesoft to create a custom connector for Snowflake, as the existing connectors are inadequate for certain authentication methods like OAuth.

💡Snowflake

Snowflake is a cloud-based data warehousing platform. The video demonstrates how to connect Mulesoft to Snowflake, focusing on secure machine-to-machine communication via client credentials and token-based authentication.

💡Client Credentials

The client credentials flow is an OAuth 2.0 mechanism used for machine-to-machine communication. In the video, it is highlighted as a secure method for integrating Snowflake with Mulesoft, where the machine (rather than a user) authenticates and procures tokens for communication.

💡Azure Application Registration

Azure Application Registration allows users to register and manage applications within Microsoft's Azure environment. In the video, this process is essential for setting up Snowflake integration, where an application is registered to generate client credentials and procure tokens.

💡Token

A token in OAuth-based authentication systems is a credential that represents authorization to access a particular resource. In this case, the video discusses the steps to generate and use tokens to securely communicate with Snowflake, replacing traditional username-password authentication.

💡OAuth 2.0

OAuth 2.0 is a widely adopted authorization framework that allows secure access to resources without sharing credentials. The video explains how Mulesoft integrates with Snowflake using OAuth 2.0, specifically focusing on the client credentials flow for machine-to-machine communication.

💡API Permissions

API permissions refer to the permissions granted to applications to interact with resources on behalf of a user or machine. In the video, setting proper API permissions in Azure is a critical step to ensure that the registered application can access Snowflake resources securely.

💡Custom Connector

A custom connector is a user-developed solution that facilitates communication between systems when no out-of-the-box option is available. In the video, the speaker develops a custom connector in Mulesoft for Snowflake due to the limitations of the default connectors provided by Mulesoft.

💡JWT (JSON Web Token)

JWT is a compact, URL-safe means of representing claims between two parties. The video demonstrates how JWT tokens are used to authenticate and authorize actions between Mulesoft and Snowflake, and how they are generated via Azure.

💡Security Integration

Security integration refers to the setup of secure communication between different systems. In the video, Snowflake's security integration is created using Azure, and it's key to ensuring that tokens generated in Azure can be properly verified by Snowflake, allowing access to resources.

Highlights

Introduction to connecting MuleSoft to Snowflake using machine-to-machine communication.

Explanation of using client credential pattern for connecting to Snowflake.

Guide to creating application registrations in Azure for Snowflake integration.

Adding Application ID URI to the Azure application registration.

Setting up app roles for secure access control.

Best practices for managing permissions and roles in Azure.

Creating a token using client ID and secret in Azure.

Verifying the token's role and permissions.

Creating a system security integration with Snowflake.

Mapping the audience list from Azure to Snowflake for security integration.

Using the system verify external work token command in Snowflake.

Creating a username and password for Snowflake based on token extraction.

Building a Java client program to connect MuleSoft with Snowflake.

Using custom connector in MuleSoft for Snowflake connection.

Explanation of using client ID and secret instead of username and password.

Debugging and attaching source code for the custom connector.

Executing a query and returning results as a string or byte array in MuleSoft.

Demonstration of the custom connector's functionality with a live example.

Conclusion and summary of the process for connecting MuleSoft to Snowflake.

Transcripts

oil welcome to this mules of video in

this video we are going to see what are

the options available for connecting

mulesoft into snowflake

so since it is a machine to machine

communication we are going to use client

credential

or pattern

so snowflake has provided to a couple of

the links one is to create

applicator applications in azure

which I have executed here

so

so you can actually

provision applications for user on

behalf of the user you can procure a

token for behalf of the user or the

client itself so we are going to use it

for client credential meaning mission to

Mission it is for client so

I am going to implement this one in

Azure for example go to application

registration

new registration

once you have this one you can add

application

ID URI to it so this this URLs we have

given us

to create two applications but I have

done it in a single application so in

first initial thing is go to

applicationization click on new

registration Snowflake and created that

one so I'll go to eBay permission

so before that you need to go here and

set this application ID URI this random

it creates itself and it's a random

create ID

then

you need to add app role

this is the approach

you can use online with generator and

I'll prepare this one and put in yours

this is explained here here

so once you set the application URI

then go to manages add this you add this

role and create an another application

you don't have to

this is just best practice if you want

to just play around with your

application is enough and add a role to

this new new application that you have

created

then

this is just trying to client security

creation before that once you create a

second application you won't go to the

API add a scope the scope is coming from

the other previous application that you

have created in azure

so once you have that let me show you

that how it looks like in here once you

add this role go back to the AP

permission add a permission

since we have one application it's

showing the same application again it's

Snowflake and I have this system admin

role here

that is what we have added here so

that's why this system admin and this

value is coming over

to the Token that we generate so once

you do that go to

heavy permissions and make sure that

this is all banned that means it's both

are approved by the Admin right

then this is the time to create a token

and see what's inside right so go to

client ID and secret put everything it's

decline through the chills okay

button then you would get hit open make

sure the token is having

this value in it the roles that you have

right then you can make sure that until

now you're good

so once you've done this

information

Grant the permission it's all saying the

same thing go to end point and create

take the token URL and like this you can

make a call right and procure the token

once you procreate the token

switch over to this URL

from here this is both this is common

for both using plantar and children user

username and password

so we're coming to this page and say

this is what I have said here

I'm talking

so this is this is the role that that I

was talking about once you generate the

token you can put an objective I want to

see it

after that you need to create a system a

security integration with the snowflake

here I have my domain demo account which

is free I have created a

by providing few results from you I

officially from here

so this is nothing but Nashua you can

message it from here we sure

this is

goes there there's no click sorry it's

just not flick and the jwtu are a token

you should take it from azure

endpoints

take the alternative configuration

that gives you the URL the jws URI

this UI you should take it as private

into snowflake right that is what this

this URL is and then you need to provide

the mapping climax sub and that's this

audience list is nothing but this is

coming from the APA permissions

sorry The Xpose API and copy this

so I think we are ready then create this

one you will get the security

integration created now we need to

provide the role uh record for this one

and then

using the same token you can verify it

from Snowflake using

system verify external work token

once you do that you will have a

username extractor

which you would need here so this is

explained and uh

in here once you create the integration

system security integration then you

would see it as failing because you

would need you would need SUV

once you execute that one

verify the token you will get the

or you once you end up okay this is what

you have so system verify external token

this will pass you this username after

that you need to create a username this

user in

the snowflake the system is not click

account where it goes I have is viewed

it here

user the ID that I have extracted from

this should become the username

and the password can be anything

and the login as well the same ID

and once you create that the

so you this should be successful as well

right then

go to go back to the link

then you're done you will be able to

procure this token and use it and in

this topic right so for that I'm going

to create a Java client program then I

will convert them into a

new soft connector right so mulesoft

does have a connector list of huge

connectors or connecting with snowflake

but it is useless because

it's not supporting jdbc is not oauth

right so we are Keen to use that one so

username and password we are not going

to provide it instead we want to provide

this is my minimum viable product so of

this connector we are going to provide

planetarium secret instead of username

and password right so the username which

this one you have seen it that that we

have cleared here

right this is coming from the token this

never changes even if you create n

number of tokens

that

climb is going to be the same all the

time so once you've created

you can create a number of

yeah so say this would be same cc87 that

the once you create the username you can

use it for the number of application

and that is goes here the client ID

which I have used it in

yeah three seven

so and on the client Secret

scope

scope is the same I'm going to give it

in the browser then it's a client finish

then

this is my account name and from this

coming from straight snowflake you can

go to

it's an application

account you can copy it here

over here

and then the default Warehouse name

compute w h

and this is this one and my database is

API

km1

demo one

API testing tables and I have a table

for demo

so

we will run this one go to worksheet and

open it and

run

demo right good sky

it has 15 rows

and

again this is

this is not needed

so we are going to create one let me

show this demo

and then I'll go to the code as well

so mules are provided one this country

is useless Finance because we are using

jdbc one supporter so that's why I have

created this Custom Custom connector and

mule 4 right let me run this program and

see what it what it gets to us I mean

otherwise let me debug the time

I didn't care

I want to save this one

okay so

save this guy

so the trick to when you create a custom

committer you

generate the sources as well attach the

sources and better debug it so that

would be easiest thing I have attached

my by clicking the class I have attached

the source code as well

then

of course I have a good breakpoint

that goes straight to that

okay I'm not reloading

let's see what I heard

and so

so I have breakpoint here

between so first what I'm doing is in

this connector I'm going to get the

token which is this this part right so

let me go to that one

step four step forward so I'm adding

length to control ID that you provided

in the connector then

I'm taking a parameter again generating

the token so token is this one then then

from the token I'm extracting

the odds token

which is this one

this works

exactly the same

this will also have this right now a

role that we have added then I'm

procuring a connection using the

clientele

once I have it I'm going to create the

statement first I'll use the I'll

execute use database command and that's

schema

after that I'm going to execute this

query which is coming straight from

sorry

straight from the goal let me show you

that

particularly

so here I have the selection from demo

that is coming to here

so it's just about this guys then see

how many rows now right now I'm

returning them as a string uh I'm

creating this scenario and create

returning as a string but I'm going to

change it into byte array so that it

will be

will be quite equivalent to

mules are given one but once mail sort

is supporting this connect this way of

connecting snowflake I think that's a

way going forward right because many

people will use that then the news I've

got the bugs closed so that is reliable

but right now this is the only shot we

have if we are using otherwise you can

use wrist based authentication if you

don't prefer the connector

if you don't prefer the hunter right so

let me

inflates it it reads all it reads all

the things and

it drove right to the Intel

yes okay let's keep running because

so this transform component tool is it's

convert them into a Json is because it's

just it's right now this component is

threatening string

right so permission to machine

communication this is the

let me stop it

see this transform component has got

something just this one here 15 areas

then

go through this

you can be working then fix the code

this is going to be in a GitHub burn one

to one and that's fine

okay I will check in there

I will add it here so I can get that

and it repeats things because it's a

scheduler so here you see it

you have all the rows

my fingers

and start by every second but uh

see so it runs perfectly so now I'm

going to go through this

uh the code where we came

the code is this one so

this is pump file where I have added

attached sources so let's let's create

the jar and there we can attach your

source and debug it

and you would need a snowflake provided

jar file

and this is to support Json and this is

for login

now I'm going to this operation straight

away

this is where I have

all the operations is scheduled so the

execute query that you see it in here

this is very pretty simple now I have

not

changed icons as well so execute query

that is coming from here so that's

expecting a connection but connection

contributes in this here so where is

this connection configuration this is

actually if you press F12

so this connection having a

configuration as well so this

configuration you should go and see it

which is this one so here I have all the

listed username client ID Secret

this one is 90 weeks if it's called

account where else

so I have but I don't see it and

database

everywhere right so now

in the

in this place that will become

the connected level kind of creation so

you would you would want to give

a SQL text here I would need I would

make it I'll do it now later so this is

the what client this client is actually

is this file I am making a HTTP

connection and preparing the token that

you have seen it while debugging right

so we are adding this details

running this do post is actually making

HTTP call for this I haven't text

analyzed it but I have text analyze it

if I want to use it so that it will come

in the connection

celebration itself so

then what I'm doing is I'm once I

prepare the token extract the out token

from there then I use use database and

use schema from

from the config I'm getting schema

once I do that I execute okay get the

results here and convert them into Json

and return them as finely as a string

bit which is which should be a wide

array

which I'm gonna work further this is

just 10 MBP for me

yeah

okay

so this I'm going to check in as I said

like in GitHub you can see it

and so if you are using Azure as your

what provider

external load provider and mulesoft and

snowflake

this is uh

I have shown almost everything

and that's it thank you very much for

your time