I'm stuck on Column Encryption

Transact-SQL
1

I was going through the examples for SQL column encryption as our Databases do store some sensitive data. I was able to create s Symmetric Key and a Certificate. Then using the function EncryptByKey, I encrypted a column. Good.

Here's my concern. I run these 2 queries:
select * from sys.symmetric_keys
select * from sys.certificates

Now my key name and certificate name appear. And I can use these to decrypt the column. So, it seems to me, the sensitive data is safe from the basic SQL User. However, any user with a little more SQL knowledge should be able to decrypt the data fairly easily.

So if anyone can explain what I'm missing or provide a good link on the topic, that would be helpful, thankyou.

2

I'm not 100% but I believe you can see the key and cert info because you created it.

MS Docs says "The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission"

Try logging in as different user and see if you can still see it, maybe??

1 Like
4

Oh my goodness, good point! thankyou

You're right when I log in as a different user, I don't see it.

5

Glad I could help.

6

Also, does anyone have any idea if the expectation of column encryption is to keep sensitive data away from the developers of the application?

For example, weather this is coded in the Application or in a Stored Procedure, a developer will have access to something like this from the code:

OPEN SYMMETRIC KEY KeyABD
   DECRYPTION BY CERTIFICATE CertABC
GO  

...
SELECT  CONVERT(varchar, DecryptByKey(NameEncrypted))

So they would be able to decode the sensitive data in Production.

... hopefully it's good enough that the sensitive data is kept safe from the Basic Users, who don't have access to the programming code nor the sys.symmetric_keys and sys.certificates tables.

7

Can you restrict access to the PRODUCTION Database / server?

We don't have a big enough outfit, or those sorts of worries, so our people can "go anywhere", but I presume "Only DBA(s) have access to Production" might be the status in bigger Shops?

1 Like
8

Thankyou! In our world, Developers to have access to Production. However, it is not all Developers, only the ones that developed the Application for that Database. And, they have read-only access. It's required in order to investigate Production issues. So, they can select from tables, but not modify. And they can open Stored Procedures to see the code, but not make changes to them.

I think part of the requirement is in case there is a hacker - to prevent the hacker from seeing sensitive data.

1 Like
9

I know it was only an example, but watch out for that default/implicit size on varchar definition (where no explicit size specified). SQL decides that ONE is suitable in some circumstances, and its a nasty side-effect to then have to find.

No easy way of preventing that in your code though (no "STRICT" mode in SQL ...)

10

hmmm interesting. I looked up 3 column encryption examples just now. 2 used implicit and 1 used explicit.

It would be great if I could have an example or a link to an example of the nasty side-effect.

11 
DECLARE @TEMP VARCHAR
SET @TEMP = REPLICATE('a', 255)
SELECT LEN(@TEMP)

SELECT LEN(CAST(REPLICATE('a', 255) AS VARCHAR))

CREATE PROCEDURE dbo.TEST01
	@bar VARCHAR
AS
BEGIN
	SET NOCOUNT ON

	SELECT	[Len]=LEN(@bar)
END
GO

EXEC dbo.TEST01 @bar = '123456789.'

DROP PROCEDURE dbo.TEST01
12

The other big benefit (and challenge) is restoring this database to another server. If that server doesn't have the proper encryption chain, the data can't be decrypted unless you enter the password and re-establish the chain. Which also applies in DR scenarios.

1 Like