Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

SQL Server Always Encrypted and Alteryx

7 - Meteor

I was wondering if anyone had experience using SQL Server's Always Encrypted feature with Alteryx? I Googled this but couldn't find anything. If so, do you have any advice on how to set it up so Alteryx can access the encrypted columns? 

Alteryx
Alteryx

@dcoelho-nhld To use Always Encrypted with ODBC, I believe you have to use the SQL server driver version 13.1 or above. Also, your database connection string must contain ColumnEncryption=Enabled. This setting instructs ODBC to encrypt data targeting encrypted columns and decrypt data retrieved from encrypted columns. If you select data from encrypted columns without this setting, you will receive ciphertext values.

ODBC Driver 13 for SQL Server};Server={myServer};Trusted_Connection=yes;ColumnEncryption=Enabled;

ColumnEncryption=Enabled;

Here's more info from microsoft. 

 

Digan
Alteryx
7 - Meteor

@DiganP Thank you for the reply. How do I set a custom connection string in Alteryx? When I try to set up a new data source I don't see an option to enter my own connection string. I've tried both ODBC and Quick Connect.

Alteryx
Alteryx

hi @dcoelho-nhld,

 

this is done at the ODBC driver configuration level (step before Alteryx)

 

As @DiganP mentioned, you need to install a new driver (in case you are using a version older than 13.1)

Go to your windows browser and search for ODBC Data sources. there within the configuration you define the database connection string 

 

VianneyM_1-1578587571870.png

 

When creating the  database connection string follow the information provided by Digan

 

Best,

Vianney

Best,
Vianney
7 - Meteor

@DiganP @VianneyM 

 

I'm using version 14.0.800.90 of the ODBC driver. 

driver.PNG

 

Using the ODBC Data Source Administrator, the only way I see to edit the connection settings is by creating a File DSN and adding the settings on the following screen:

fileDSN.PNG

 

Is there another way to edit the connection string that I'm missing or is this it? 

 

Also, how would I use a File DSN in Alteryx? I'm not seeing it come up when I select ODBC connection. 

Alteryx
Alteryx

@dcoelho-nhld You would have to create a user or system DSN NOT a file DSN. 

 

DiganP_0-1578594502430.png

Digan
Alteryx
7 - Meteor

@DiganP 

When I click "Add" under User DSN or System DSN, these are the following screens I get:

 

1.PNG2.PNG3.PNG4.PNG5.PNG

 

 

I don't see an option to add a connection string. At which point in these screens can I add the connection string? 

Alteryx
Alteryx

hi @dcoelho-nhld,

 

Actually in the background you are creating a Connection string, by following and completing all the information requested to create a successful connection.

 

Make sure you select 

VianneyM_1-1578667170915.png

 

ans also this 

VianneyM_0-1578667138374.png

 

At the end you should have a connection string like this 

ODBC Driver 13 for SQL Server};Server={myServer};Trusted_Connection=yes;ColumnEncryption=Enabled;

 

Please review the details that Microsoft has

https://docs.microsoft.com/en-us/sql/connect/odbc/using-always-encrypted-with-the-odbc-driver?view=s...

 

Best,

Vianney

 

Best,
Vianney
Labels