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! 

[Vinod28]

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;

[Vinod28]

Relevant Alteryx Community Discussions

1. Executing Encrypted Stored Procedures in SQL from Alteryx

   • This discussion highlights the challenge of encrypted stored procedures not appearing in Alteryx's Input Data tool and provides solutions such as creating a non-encrypted wrapper procedure.

   • Executing Encrypted Stored Procedures in SQL from ... - Alteryx Community

   • Troubleshooting Stored Procedures

     • This article offers a comprehensive guide on handling stored procedures in Alteryx, including tips for encrypted procedures, using wrapper procedures, and ensuring that procedures return data appropriately.

     • Troubleshooting Stored Procedures - Alteryx Community

     • Run SQL Stored Procedure with Parameter in Alteryx

       • This thread discusses executing stored procedures with parameters in Alteryx and addresses issues related to encrypted procedures not appearing in the tool's interface.

       • Solved: Run SQL Stored Procedure with parameter in Altery... - Alteryx Community

       • How to Call Stored Procedure (SQL Server) in Alteryx

         • This discussion provides insights into calling stored procedures in Alteryx, including handling encrypted procedures by using wrapper procedures and ensuring proper execution.

         • Solved: How to call stored procedure (SQL Server) in alter... - Alteryx Community