CODEWITHSUNDEEP
c#azure-data-lakeu-sql
Teja
Teja

Reputation: 11

U-SQL data source as SQL Server inside a UDF

I need to extract rows from a SQL table where some columns are encrypted using SQL Server's new 'Always Encrypted' feature. I see that I cannot use the 'AZURESQLDB' DataSource feature and there needs to be decryption done before reading the data in plain text. Are there plans to add this capability?. Meanwhile, I tried to write a user defined function that will do the same operation(connect, decrypt data and return object) in a registered assembly but when it runs, I get the following error:

Inner exception from user expression: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I have checked the code and everything seems correct. The connection string is used by the SqlConnection object and works fine in all other applications. I am guessing that the connectivity to external data sources from within a UDF is blocked. Is there any way around this?

Upvotes: 1

Views: 109

Answers (2)

Michael Rys
Michael Rys

Reputation: 6684

Are you using the DATA SOURCE in U-SQL for representing your SQL Server instance and you cannot get it to read encrypted data? If so, please file a feature request at http://aka.ms/adlfeedback.

You cannot call out to network resources directly from within U-SQL user code for the reasons explained here.

Upvotes: 2

wBob
wBob

Reputation: 14389

One way around this might be to create a stored procedure which does the hard work, the decryption then renders the data. Then use Azure Data Factory with a Stored Proc Task to access the decrypted data and move what you need to the Data Lake - not including the secure data. From there you could then access it using a U-SQL script. One idea? Let me know if you need me to work up more of an example.

Upvotes: 0

Related Questions