This document shows step-by-step instructions for setting up encrypted fields in a SQL database table.<\/p>\n
\/* Create table and insert data in the table *\/<\/strong>\r\nUSE EncryptTest\r\nGO\r\nCREATE TABLE PasswordTable (SEQ INT, AdminPassword VARCHAR(50))\r\nGO\r\nINSERT INTO PasswordTable (SEQ, AdminPassword)\r\nSELECT 1,'First'\r\nUNION ALL\r\nSELECT 2,'Second'\r\nUNION ALL\r\nSELECT 3,'Third'\r\nUNION ALL\r\nSELECT 4,'Fourth'\r\nUNION ALL\r\nSELECT 5,'Fifth'\r\nGO<\/span><\/pre>\n\/* Check the content of the PasswordTable *\/<\/strong>\r\nUSE EncryptTest\r\nGO\r\nSELECT *\r\nFROM PasswordTable\r\nGO<\/span><\/pre>\n\/* Create Database Master Key *\/<\/strong>\r\nUSE EncryptTest\r\nGO\r\nCREATE MASTER KEY ENCRYPTION\r\nBY PASSWORD = 'Password-1234'\r\nGO<\/span><\/pre>\n\/* Create Encryption Certificate *\/<\/strong>\r\nUSE EncryptTest\r\nGO\r\nCREATE CERTIFICATE EncryptTestCert\r\nWITH SUBJECT = 'Password-1234'\r\nGO<\/span><\/pre>\n\/* Create Symmetric Key *\/<\/strong>\r\nUSE EncryptTest\r\nGO\r\nCREATE SYMMETRIC KEY PasswordTableKey\r\nWITH ALGORITHM = TRIPLE_DES ENCRYPTION\r\nBY CERTIFICATE EncryptTestCert\r\nGO<\/span><\/pre>\n\/* Encrypt Data using Key and Certificate\r\nAdd Columns which will hold the encrypted data in binary *\/<\/strong>\r\nUSE EncryptTest\r\nGO\r\nALTER TABLE PasswordTable\r\nADD EncryptAdminPassword VARBINARY(256)\r\nGO<\/span><\/pre>\n\/* Update binary column with encrypted data created by certificate & key *\/<\/strong>\r\nUSE EncryptTest\r\nGO\r\nOPEN SYMMETRIC KEY PasswordTableKey DECRYPTION\r\nBY CERTIFICATE EncryptTestCert\r\nUPDATE PasswordTable\r\nSET EncryptAdminPassword = ENCRYPTBYKEY(KEY_GUID('PasswordTableKey'),AdminPassword)\r\nGO<\/span><\/pre>\n\/* DROP original column which was encrypted to protect the data *\/<\/strong>\r\nUSE EncryptTest\r\nGO\r\nALTER TABLE PasswordTable\r\nDROP COLUMN AdminPassword\r\nGO<\/span><\/pre>\n\/* Check the content of the PasswordTable *\/<\/strong>\r\nUSE EncryptTest\r\nGO\r\nSELECT *\r\nFROM PasswordTable\r\nGO<\/span><\/pre>\n\/* Decrypt the data of the AdminPassword *\/<\/strong>\r\nUSE EncryptTest\r\nGO\r\nOPEN SYMMETRIC KEY PasswordTableKey DECRYPTION\r\nBY CERTIFICATE EncryptTestCert\r\nSELECT SEQ, CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptAdminPassword)) AS DecryptAdminPassword, EncryptAdminPassword, RegStamp, DateStamp\r\nFROM PasswordTable\r\nGO<\/span><\/pre>\n\/* This is an example of adding a record with an encrypted field *\/<\/strong>\r\nOPEN SYMMETRIC KEY PasswordTableKey DECRYPTION\r\nBY CERTIFICATE EncryptTestCert\r\nINSERT INTO PasswordTable\r\n(EncryptAdminPassword, RegStamp, DateStamp)\r\nVALUES(ENCRYPTBYKEY(KEY_GUID('PasswordTableKey'),'DogBreath91'),'test 2014.01','2014-01-15')\r\nGO<\/span><\/pre>\n","protected":false},"excerpt":{"rendered":"This document shows step-by-step instructions for setting up encrypted fields in a SQL database table. \/* Create table and insert data in the table *\/ USE EncryptTest GO CREATE TABLE PasswordTable (SEQ INT, AdminPassword VARCHAR(50)) GO INSERT INTO PasswordTable (SEQ, AdminPassword) SELECT 1,’First’ UNION ALL SELECT 2,’Second’ UNION ALL SELECT 3,’Third’ UNION ALL SELECT 4,’Fourth’ […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[8],"tags":[94,95,70,74],"class_list":["post-290","post","type-post","status-publish","format-standard","hentry","category-sql","tag-database","tag-encryption","tag-sql","tag-tutorial"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9t3oE-4G","_links":{"self":[{"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=\/wp\/v2\/posts\/290","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=290"}],"version-history":[{"count":5,"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=\/wp\/v2\/posts\/290\/revisions"}],"predecessor-version":[{"id":300,"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=\/wp\/v2\/posts\/290\/revisions\/300"}],"wp:attachment":[{"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=290"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=290"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=290"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}