SQL Server Always Encrypted and Alteryx

[dcoelho-nhld]

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? 

[DiganP]

@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. 

[dcoelho-nhld]

@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.

[VianneyM]

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 

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

Best,

Vianney

[dcoelho-nhld]

@DiganP @VianneyM 

I'm using version 14.0.800.90 of the ODBC driver. 

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:

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. 

[DiganP]

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

[dcoelho-nhld]

@DiganP 

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

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

[VianneyM · Accepted solution]

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 

ans also this 

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