Query Column With Always Encryption
While recently implementing SQL Always Encrypted feature I encountered a problem. The column that has been encrypted cannot be used with certain operators(RIGHT/LEFT/LIKE and etc). What I did for this is created a SQL/CLR method and passed ID as parameter, this method then queries the table again and decrypts the value if there is valid certificate on the server where this method is being executed. [ SqlFunction (SystemDataAccess = SystemDataAccessKind .Read, DataAccess = DataAccessKind .Read)] public static string GetDecryptedValueByID( int ? id) { if (id == null ) return null ; using ( SqlConnection conn = new SqlConnection ( "context connection=true" )) { conn.Open(); SqlCommand cmd = new SqlCommand ( "SELECT Column from Table where ID =" + id.ToString() " , conn); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read())