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

Welcome to UtterAccess! Please ( Login   or   Register )

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



Posts: 299
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
 
nvogel
post May 23 2019, 11:52 PM
Post#2



Posts: 956
Joined: 26-January 14
From: London, UK


I suggest you test your procedure in SQL Server Management Studio before you call it from VBA. It's easier to identify and fix problems in Management Studio. You can download and install Management Studio for free if you need to.

One significant point here is that you should not name user stored procedures with names beginning with SP_. SP_ is reserved for system stored procedures and may alter the way your proc behaves. spr_, prc_ or usp_ are more usual prefixes for procs.

The specific error you are getting appears to be caused by concatenating an integer with a string. This part:

+ @intID

ought to be:

+ CAST(@intID AS NVARCHAR(10))

Another problem is that you are executing the @SQLQuery twice. You only need to do EXECUTE once.

Your encryption method will only work for VARCHAR, NVARCHAR and VARBINARY columns and it will only work if the column is wide enough to accommodate the encrypted value, which will always be larger than the unencrypted value. I would think there's a real possibility that data can get overwritten and lost that way. Bear in mind that if this proc is called for columns that are numbers or dates or if it is used for strings that are close to the maximum size of the column then the data could be permanently lost. It's unusual in my experience to have a column that contains both encrypted and unencrypted data and you will need to test this very carefully. My recommendation would be that you create a new binary column to hold the encrypted data instead of trying to encrypt values in the same column.

Hope this helps.

This post has been edited by nvogel: May 23 2019, 11:58 PM
Go to the top of the page
 
PhilS
post May 24 2019, 01:48 AM
Post#3



Posts: 575
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
 


Custom Search


RSSSearch   Top   Lo-Fi    25th June 2019 - 09:11 AM