Executing Encrypted Stored Procedures in SQL from Alteryx

mgrasser

I'm trying to execute an encrypted stored procedure in a SQL DB from Designer, but it seems that the Input Data tool isn't able to find the SP at all.  I'm using the same credentials from SQL Server Management Studio (SSMS) to log into the DB as in Alteryx Designer with setting up my SQL DB connection.  

On the left I'm able to see 3 SPs starting with FTAP when viewing the Stored Procedure in SSMS, with the one I'm attempting to execute being encrypted (with the padlock), but I'm not able to find that same SP in the Input Data tool (Stored Procedure tab) in Alteryx.  

Also, when I try to execute the encrypted stored procedure via SQL code, I receive an error saying the SP can't be found:

Any suggestions on what I can try?  Thanks in advance! 

VinodKammula28

Manually Enter the Stored Procedure SQL

Since Alteryx cannot introspect encrypted SPs, bypass the dropdown:

1. Drag an Input Data tool onto your canvas.

2. In the configuration panel, choose Write a Query.

3. Enter your procedure execution command manually: EXEC dbo.FTAP_YourSPName @param1 = 'value1';

Wrap the Encrypted SP in a Non-Encrypted Wrapper

This is the most reliable fix. Ask a DBA (or do it yourself if you have permission) to create a wrapper SP like this:

CREATE PROCEDURE dbo.FTAP_WrapperSP
AS
BEGIN
SET NOCOUNT ON;
EXEC dbo.FTAP_EncryptedSP;
END

Validate Permissions

The Alteryx SQL connection must have EXECUTE permissions on the SP.

Run this in SSMS to check:

SELECT HAS_PERMS_BY_NAME('dbo.FTAP_YourSPName', 'OBJECT', 'EXECUTE');

GRANT EXECUTE ON dbo.FTAP_YourSPName TO [YourUserOrRole];

Confirm Return Structure

Alteryx supports only a single result set from stored procedures. If your SP:

• Returns multiple result sets

• Uses PRINT statements

• Has conditional SELECTs

You must refactor the SP or ensure it ends with:

SELECT col1, col2 FROM your_table;