1

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?

José Pedro
  • 1,097
  • 3
  • 14
  • 24
Teja
  • 11
  • 1
  • Hi and welcome to Stack overflow- You might find someone is able to help you if you could focus your questions into tighter, specific questions (as this post has two - one about datasource feature, and another about the network-related issue connecting to your SQL server. For the latter, it would be helpful if you shared your 'minmal non-working example' of the code you've tried, so we can look and try to see if this is a code error, or a configuration error. – Jmons May 05 '17 at 21:44
  • 1
    I will. Thank you. – Teja May 08 '17 at 07:01

2 Answers2

2

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.

Community
  • 1
  • 1
Michael Rys
  • 6,684
  • 15
  • 23
0

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.

wBob
  • 13,710
  • 3
  • 20
  • 37
  • Actually, the data in encrypted colums can be decrypted in very limited ways and stored proc is not one of them. I am currently using a .NET based solution. The reason could be that the application trying to decrypt the data has to make trips to key vault for crypto operations. – Teja May 08 '17 at 07:00