sql server - Enforcing a unique constraint on a column encrypted with cell level encryption -


i want enforce unique constraint on column must encrypted in mssql 2005 using cell level encyption (cle).

create master key encryption password = 'itsasecret!!!3£3£3£!!!' create certificate ercert subject = 'a cert use procs'  create symmetric key erkey  algorithm = aes_256 encryption certificate ercert  -- illustrates point.  -- results differ declare @temptable table (     [email] [varbinary](254) unique not null )  open symmetric key erkey decryption certificate ercert      insert @temptable(email) values(encryptbykey(key_guid('erkey'), n'duplicate'))     insert @temptable(email) values(encryptbykey(key_guid('erkey'), n'duplicate'))     insert @temptable(email) values(encryptbykey(key_guid('erkey'), n'duplicate')) close symmetric key erkey     select * @temptable

the output makes obvious why constraint has 'not' been enforced. (please excuse awesomeness of ascii-art.)

    email    ------- 1 | 0x00703529af46d24ba863a3534260374e01000000328909b51ba44a49510f24df31c46f2e30977626d96617e2bd13d9115eb578852eebae326b8f3e2d422230478a29767c 2 | 0x00703529af46d24ba863a3534260374e01000000773e06e1b53f2c57f97c54370fecbb45bc8a154fea5ceeb9b6bb1133305282328aafad65b9bdc595f0006474190f6482 3 | 0x00703529af46d24ba863a3534260374e01000000c9edb1c83b52e60598038d832d34d75867ab0abb23f9044b7ebc76832f22c432a867078d10974dc3717d6086d3031bdb

but, how work around this?

normally, use "deterministic encryption", example, aes ecb mode, or aes-siv. since within limitations of sql server encryption, you'll have find way. here old post discusses issue: http://blogs.msdn.com/b/raulga/archive/2006/03/11/549754.aspx. here newer post mentions sql server 2016 support deterministic encryption: http://www.brentozar.com/archive/2015/05/sql-server-2016-security-roadmap-session-notes-msignite/, looking for.


Comments

Post a Comment

Popular posts from this blog

IF statement in MySQL trigger -

this query code in mysql trigger. if 1=2 select 'yes'; else select 'no'; end if; but has following error: if '1'='2' select 'yes' error code: 1064. have error in sql syntax; check manual corresponds mysql server version right syntax use near 'if '1'='2' select 'yes'' @ line 1 0.000 sec what solution?
Read more

c++ - What does MSC in "// appease MSC" comments mean? -

i run across // appease msc in c++ comments, e.g. here : const int& array::operator[](int offset) const { int mysize = getitssize(); if (offset >= 0 && offset < getitssize()) return ptype[offset]; throw xboundary(); return ptype[offset]; // appease msc! } what msc stand ? the author means microsoft's compiler, more formally known msvc.
Read more

javascript - Blogger related post gadget image Resize s72-c [ Need Expert Help ] -

i run blogger blog , use follow code javascript code (requires no jquery) show related post labels/categories of post. <script type='text/javascript'> var defaultnoimage=&quot;http://1.bp.blogspot.com/-m72rpguntq0/vuokijudn_i/aaaaaaaaboi/lq18sceunsg/w72/favicon-tik.png&quot;; var maxresults=16; var splittercolor=&quot;#d4eaf2&quot;; var relatedpoststitle=&quot;related posts&quot;; </script> <script type='text/javascript'>//<![cdata[ var relatedtitles = new array(); var relatedtitlesnum = 0; var relatedurls = new array(); var thumburl = new array(); function related_results_labels_thumbs(json) { (var = 0; < json.feed.entry.length; i++) { var entry = json.feed.entry[i]; relatedtitles[relatedtitlesnum] = entry.title.$t; try { thumburl[relatedtitlesnum] = entry.media$thumbnail.url } catch (error) {...
Read more