locked
Always Encrypted not working with aspnet database RRS feed

  • Question

  • I have encrypted some columns like aspnet_Users(UserName, LoweredUserName) and aspnet_Membership(Email, LoweredEmail) in the Aspnet DB with Always Encrypted for the security reason.

    Now I am getting issues throughout the application, as Always Encrypted doesn't allow like operator, lower function etc. And aspnet db is using these operators thoughout the database stored procs. So I am stuck now.  Is there any other method to encrypt this database or is there any fix microsoft is providing to overcome this situation.

    Thanks in Advance

    Ankur Sangal

    Friday, November 3, 2017 7:55 AM

All replies

  • So can you please give more information when you say not working. Can you show us any error message you are getting. There are some predefined restriction for always encrypted, are you aware about that ?

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, November 3, 2017 8:13 AM
  • I have encrypted some columns like aspnet_Users(UserName, LoweredUserName) and aspnet_Membership(Email, LoweredEmail) in the Aspnet DB with Always Encrypted for the security reason.

    Now I am getting issues throughout the application, as Always Encrypted doesn't allow like operator, lower function etc. And aspnet db is using these operators thoughout the database stored procs. So I am stuck now.  Is there any other method to encrypt this database or is there any fix microsoft is providing to overcome this situation.

    ....

    Encryption encrypts things. With random Encryption you cannot even derive equality - a very vital part of encryption.

    If you can live with a less strong encryption you could chose "deterministc".

    This is all documented: Always Encrypted (Database Engine)

    My advise would be to make a plan of which columns need to support which operations and never blindly just encrypt everything the same way.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Friday, November 3, 2017 10:31 AM
  • The aspnet_Membership application does not support database level encryption.

    I suggest you read:

    https://msdn.microsoft.com/en-us/library/ms178398.aspx

    Friday, November 3, 2017 12:51 PM
  • Always Encrypted is *not* transparent to the application. SQL Server doesn't know what is stored in the table (the clear-text value), which is the whole point of AE. But that also means that you can at best to equal matchings (does the encrypted value of this match the encrypted value stored in the database?) and similar (GROUP BY, DISTINCT). 

    Start with what the application supports. Also, define exactly what you want to protect yourself from.

    For instance a DBA reading sensitive info? Then the data inside the database sould be encrupted and SQL Server shouldn't have the key. Just like AE. But your app doesn't support AE so you'd need to check what options the app has.

    Or from somebody stealing backup files or database files? Then backup encryption or Transparent Database Encryption could be an option.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Proposed as answer by Teige Gao Tuesday, November 7, 2017 1:43 AM
    Saturday, November 4, 2017 6:01 PM