Create a trigger after insert to encrypt columns in a table

Question

Create a trigger after insert to encrypt columns in a table

SSRS Newbie

Hall of Fame

Points: 3244
More actions
May 30, 2017 at 7:40 am

#338010

Hello All,
I'm trying to create a trigger that will encrypt a column when an insert operation is done on table. Has anyone tried this before? I'm new to triggers. Any help is greatly appreciated.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

so much of encryption depends on the data type of the destiantion column, and the type of encryption you care to use.
most encryption methods expect a varbinary column, so a FOR/AFTER INSERT command would fail/error due to wrong data types; you really are looking for an INSTEAD OF trigger, i think.
AES keeps the text as varchar/nvarchar, i wrote an example five years ago just showing how to encrypt and decrypt here:https://qa.sqlservercentral.com/Forums/FindPost1266705.aspx

an instead of trigger would insert all the other values as is, but change the encrypted columns. you can run into permissions and other problems too, you would want to sign the trigger with a certificate., since plain-old-users would not have permissions to open keys and encrypt under the calling user security context.

if you provide a table structure, we could probably build a sample trigger as an example.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SSRS Newbie Hall of Fame Points: 3244 More actions · Answer 2

Hi Lowell,
I appreciate your prompt response. I do have all the encryption columns and data types set up with Symmetric and Asymmetic keys required for encryption. Here is the table structure:
USE [Test01] GO

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

SET ANSI_PADDING ON GO

CREATE TABLE [dbo].[Input_table]( [recID] [int] IDENTITY(1,1) NOT NULL, [BatchID] [int] NULL, [BatchDescription] [varchar](80) NULL, [JNumber] [varchar](31) NOT NULL, [ShipReference] [varchar](50) NOT NULL, [CustReference] [varchar](50) NOT NULL, [Terms] [varchar](50) NULL, [ShipDate] [datetime] NULL, [Shipper] [varchar](10) NULL, [Enc_Consignee_Company] [varbinary](256) NULL, [Enc_Consignee_Contact] [varbinary](256) NULL, [Enc_Consignee_Address1] [varbinary](256) NULL, [Enc_Consignee_Address2] [varbinary](256) NULL, [Enc_Consignee_Address3] [varbinary](256) NULL, [Enc_Consignee_City] [varbinary](256) NULL, [Enc_Consignee_State] [varbinary](256) NULL, [Enc_Consignee_PostalCode] [varbinary](256) NULL, [Enc_Consignee_Phone] [varbinary](256) NULL [Enc_Notification_Email_To1] [varbinary](256) NULL, [CreateDate] [datetime] NOT NULL, [Creator] [varchar](180) NOT NULL, CONSTRAINT [PK_Input_tab] PRIMARY KEY CLUSTERED ( [recID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

Lowell SSC Guru Points: 323482 More actions · Answer 3

if the columns are already varbinary, are you using procedures to return unencrypted data for selects? if you are already doing that, you should use procs for insert/update/delete as well.
you are not using a view to auto-decrypt, right? you use procedures?

a trigger is not going o help much, because the datatype will prevent a classic insert command anyway, right?
i cannot do a basic command like you might expect due to data types:
INSERT INTO [dbo].[Input_table]([Enc_Consignee_Company]) SELECT 'Amazon' UNION ALL SELECT 'Alphabet'

Msg 257, Level 16, State 3, Line 42
Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SSRS Newbie Hall of Fame Points: 3244 More actions · Answer 4

Hi Lowell,
I'm using views to auto-decrypt the select statements. Can you please give me an example as to how I can use procs for insert/update/delete statements. I made some progress with the trigger. Please take a look at it. Am I missing something in this trigger? Appreciate your help on this.
USE [Test01] GO

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE TRIGGER [dbo].[Trig_Enc_Test] ON [Test01].[dbo].[Input_tab] INSTEAD OF INSERT AS BEGIN OPEN SYMMETRIC KEY SymKeyTest01 DECRYPTION BY ASYMMETRIC KEY ASymKeyTest01 WITH PASSWORD = 'TestPass'; INSERT INTO [Test01].[dbo].[Input_tab] ([JNumber] ,[ShipReference] ,[CustReference] ,[Enc_Consignee_Company] ,[Enc_Consignee_Contact] ,[Enc_Consignee_Address1] ,[Enc_Consignee_Address2] ,[Enc_Consignee_Address3] ,[Enc_Consignee_City] ,[Enc_Consignee_State] ,[Enc_Consignee_PostalCode] ,[Enc_Consignee_Phone] ,[Enc_Notification_Email_To1] ) select [JNumber] ,[ShipReference] ,[CustReference] ,ENCRYPTBYKEY(KEY_GUID('SymKeyTest01'),[Enc_Consignee_Company]) ,ENCRYPTBYKEY(KEY_GUID('SymKeyTest01'),[Enc_Consignee_Contact]) ,ENCRYPTBYKEY(KEY_GUID('SymKeyTest01'),[Enc_Consignee_Address1]) ,ENCRYPTBYKEY(KEY_GUID('SymKeyTest01'),[Enc_Consignee_Address2]) ,ENCRYPTBYKEY(KEY_GUID('SymKeyTest01'),[Enc_Consignee_Address3]) ,ENCRYPTBYKEY(KEY_GUID('SymKeyTest01'),[Enc_Consignee_City]) ,ENCRYPTBYKEY(KEY_GUID('SymKeyTest01'),[Enc_Consignee_State]) ,ENCRYPTBYKEY(KEY_GUID('SymKeyTest01'),[Enc_Consignee_PostalCode]) ,ENCRYPTBYKEY(KEY_GUID('SymKeyTest01'),[Enc_Consignee_Phone]) ,ENCRYPTBYKEY(KEY_GUID('SymKeyTest01'),[Enc_Notification_Email_To1])

from inserted end

GO