Lesson Learned #15: Securing our Azure SQL Datawarehouse

Published Mar 13 2019 06:18 PM 444 Views
First published on MSDN on Jan 12, 2017
Hello Everyone,

Nowdays, the security is a key element for all databases, for this reason, in Azure SQL we have several options depending on our needs. In this picture you could see the different available options that we have right now.


In this post, I would like to share some points about two important topics for our applications that are running on Azure:

  • Data Encryption .

  • Data Protection .


Although, all these options are available on Azure SQL Database , unfortunately, in Azure SQL Datawarehouse are not available these ones: Always Encrypted, Data Masking and Row Level Security.



  • TDE ( Transparent Data Encryption ) encrypts the data files, protecting data on SQL database physical storage from unauthorized access.

    • Server-side encryption of the data on physical disk.

    • Zero application changes.

    • Support for all database operations (ex. joins) on data.

    • SQL Database service manages your keys.






  • Always Encrypted protects the highly sensitive data in-use from high privilege SQL users.

    • Client-side encryption of sensitive data using keys that are never given to the database system.

    • Support for equality comparison, incl. join, group by and distinct operators.

    • Minimal application changes via server and client library enhancements.






  • Data Masking that limit the exposure of sensitive data by obfuscating query results for application users.

    • Protects against unauthorized access to sensitive data in the application, using built-in or custom masking rules. Privileged users can still see unmasked data.

    • Data is masked on-the-fly, underlying data in the database remains intact. Transparent to the application and applied according to user privilege






  • Row Level Security , centralize your row access logic within the database.

    • Control both read/write-access to specific rows of data.

    • Flexible access criteria (user identity, role/group memberships, etc).

    • Works transparently at query time, no application changes needed and reduces application maintenance and code complexity.



%3CLINGO-SUB%20id%3D%22lingo-sub-368843%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2315%3A%20Securing%20our%20Azure%20SQL%20Datawarehouse%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-368843%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Jan%2012%2C%202017%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20Hello%20Everyone%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Nowdays%2C%20the%20security%20is%20a%20key%20element%20for%20all%20databases%2C%20for%20this%20reason%2C%20in%20Azure%20SQL%20we%20have%20several%20options%20depending%20on%20our%20needs.%20In%20this%20picture%20you%20could%20see%20the%20different%20available%20options%20that%20we%20have%20right%20now.%20%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F88905iE21D9AF2C404B5CC%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%20In%20this%20post%2C%20I%20would%20like%20to%20share%20some%20points%20about%20two%20important%20topics%20for%20our%20applications%20that%20are%20running%20on%20Azure%3A%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3EData%20Encryption%20%3C%2FSTRONG%3E%20.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CSTRONG%3EData%20Protection%20%3C%2FSTRONG%3E%20.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20Although%2C%20%3CSTRONG%3E%20all%20these%20options%20are%20available%20on%20Azure%20SQL%20Database%20%3C%2FSTRONG%3E%20%2C%20unfortunately%2C%20%3CSTRONG%3E%20in%20Azure%20SQL%20Datawarehouse%26nbsp%3Bare%20not%20available%20these%20ones%3A%20Always%20Encrypted%2C%20Data%20Masking%20and%20Row%20Level%20Security.%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fdn948096.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ETDE%20%3C%2FA%3E%20(%20Transparent%20Data%20Encryption%20)%20encrypts%20the%20data%20files%2C%20protecting%26nbsp%3Bdata%20on%20SQL%20database%20physical%20storage%20from%20unauthorized%20access.%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EServer-side%20encryption%20of%20the%20data%20on%20physical%20disk.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EZero%20application%20changes.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ESupport%20for%20all%20database%20operations%20(ex.%20joins)%20on%20data.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ESQL%20Database%20service%20manages%20your%20keys.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fmt163865.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAlways%20Encrypted%20%3C%2FA%3E%20protects%20the%20highly%20sensitive%20data%20in-use%20from%20high%20privilege%20SQL%20users.%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EClient-side%20encryption%20of%20sensitive%20data%20using%20keys%20that%20are%20never%20given%20to%20the%20database%20system.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ESupport%20for%20equality%20comparison%2C%20incl.%20join%2C%20group%20by%20and%20distinct%20operators.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EMinimal%20application%20changes%20via%20server%20and%20client%20library%20enhancements.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fmt130841.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EData%20Masking%20%3C%2FA%3E%20that%20limit%20the%20exposure%20of%20sensitive%20data%20by%20obfuscating%20query%20results%20for%20application%20users.%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EProtects%20against%20unauthorized%20access%20to%20sensitive%20data%20in%20the%20application%2C%20using%20built-in%20or%20custom%20masking%20rules.%20Privileged%20users%20can%20still%20see%20unmasked%20data.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EData%20is%20masked%20on-the-fly%2C%20underlying%20data%20in%20the%20database%20remains%20intact.%20Transparent%20to%20the%20application%20and%20applied%20according%20to%20user%20privilege%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fdn765131.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ERow%20Level%20Security%20%3C%2FA%3E%20%2C%20centralize%20your%20row%20access%20logic%20within%20the%20database.%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EControl%20both%20read%2Fwrite-access%20to%20specific%20rows%20of%20data.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EFlexible%20access%20criteria%20(user%20identity%2C%20role%2Fgroup%20memberships%2C%20etc).%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EWorks%20transparently%20at%20query%20time%2C%20no%20application%20changes%20needed%20and%20reduces%20application%20maintenance%20and%20code%20complexity.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-368843%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jan%2012%2C%202017%20Hello%20Everyone%2CNowdays%2C%20the%20security%20is%20a%20key%20element%20for%20all%20databases%2C%20for%20this%20reason%2C%20in%20Azure%20SQL%20we%20have%20several%20options%20depending%20on%20our%20needs.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-368843%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ealways%20encrypted%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eazure%20sql%20datawarehouse%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Edata%20encryption%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Edata%20masking%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EData%20Protection%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Erow%20level%20security%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esecure%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esecuring%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etde%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etransparent%20data%20encryption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 13 2019 06:18 PM
Updated by: