UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Encrypting Tables, Fields And Data In SQL, SQL Server 2008    
 
   
SomekindaVB
post May 23 2019, 07:27 PM
Post#1



Posts: 300
Joined: 15-December 16



Hi All.

I have an access front end and an SQL backend. SQL server is 2008.

I am looking to encrypt data. The requirement is only to do so for specific tables, a specific field and a specific record. There is no requirement for perfect encryption, provided that the data is unreadable after the data have been entered into SQL, such that it's difficult to see the data in it's entirety.

SQL contains the Keys and certificates. I considered using a store procedure to do the heavy lifting. From VBA, I'm calling this stored procedure and supplying the table, the field, the record ID and and the string I need to encrypt.

My SP looks like this.

CODE
USE [MyDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SP_EncryptField](
    @strTableName [nvarchar](255),
    @strFieldName [nvarchar](255),
    @strStringToEncrypt [nvarchar](255),
    @intID [Int]
)
WITH EXECUTE AS Owner --Required to give permissions in order to allow the decryption.  Would rather not do this if I can help it.
AS
BEGIN
    DECLARE @SQLQuery AS NVARCHAR(500)
    OPEN SYMMETRIC KEY MyKEY
    DECRYPTION BY CERTIFICATE MyCert
    SET @SQLQuery = N'UPDATE dbo.AAA_' + @strTableName + ' SET [' + @strFieldName + '] = EncryptByKey(Key_GUID(''EncryptPIIKEY''), ''' + @strStringToEncrypt + ''') Where [ID] = ' + @intID
    EXECUTE @SQLQuery
    EXECUTE sp_executesql @SQLQuery, N'@intID INT OUTPUT', @intID OUTPUT
    
    CLOSE SYMMETRIC KEY MyKEY
END

GO


Obviously, it's not working.

The error in VBA is: Conversion failed when converting the nvarchar value 'Etc, etc' where [ID] = to data type int (research say that SQL is trying to convert the entire string to an int)

So if I convert the IntID to string the error is the name is not a valid identifier.

Other things I have tried is banging my head against a wall, but that has also not worked.

I need to encrypt this data and I need to use the keys and certificate supplied by my SQL server admin team. It will not be upgraded to 2016 in the near future.

Any other info, please ask.

Any help appreciated.

Cheers
Go to the top of the page
 
 
Start new topic
Replies
PhilS
post May 24 2019, 01:48 AM
Post#2



Posts: 581
Joined: 26-May 15
From: The middle of Germany


Just focusing on this part of your question:
QUOTE
So if I convert the IntID to string the error is the name is not a valid identifier.


Let's look at your code triggering this error:

OPEN SYMMETRIC KEY MyKEY
DECRYPTION BY CERTIFICATE MyCert
SET @SQLQuery = N'UPDATE dbo.AAA_' + @strTableName + ' SET [' + @strFieldName + '] = EncryptByKey(Key_GUID(''EncryptPIIKEY''), ''' +


Now, considering the highlighted key names, the error makes sense, doesn't it?

Edit/PS:
Well, on second thought, while the issue highlighted above would definitely cause the error quoted, it might not be the only reason.
You do not execute the SQL right away but store it in a variable an execute it dynamically. The opened key might not be available in the execution context of the dynamic SQL anyway.

Edit2/PPS:
If the potential issue mentioned in the PS is actually a problem, maybe a simple solution would be to include the OPEN ... KEY Statement in the dynamic SQL.

This post has been edited by PhilS: May 24 2019, 01:57 AM

--------------------
Go to the top of the page
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    21st July 2019 - 07:47 AM