April 10, 2012 at 2:36 pm
Hi Guys,
I created functions to encrypt and decrypt data in SQL Server using AES_256 (symmetric encryption with certificate). The encryption and decryption works fine when ran in Query Analyzer; however, encryption do not work when we run the same function through ColdFusion... Decryption using the Select and decrypt function is running fine...
I even tried creating On Insert trigger to encrypt data which is working fine on query analyzer, but not with ColdFusion as well...
Anyone here have experienced this before? Please suggest...
Thanks,
Laura
April 10, 2012 at 2:47 pm
implementation specifics would be useful to help you. Are you using the same login credentials from coldfusion as you are when you connect via SS management console?
The probability of survival is inversely proportional to the angle of arrival.
April 10, 2012 at 2:52 pm
No it is not using the same logins... so you think it is the permission issue? Thanks!
April 10, 2012 at 2:54 pm
A couple of questions to ask:
1) Are you binding the values with cfqueryparam?
2) What's the JDBC driver set up? Is CLOB enabled for example? How big is the buffer?
Perhaps you could post some code for us.
-mark
April 10, 2012 at 2:57 pm
Laura_SqlNovice (4/10/2012)
No it is not using the same logins... so you think it is the permission issue? Thanks!
yes.
The probability of survival is inversely proportional to the angle of arrival.
April 10, 2012 at 2:59 pm
This is the error we got when using the trigger to encrypt data...
Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot find the symmetric key 'TEST_SYMKEY', because it does not exist or you do not have permission.
This worked fine when I did in Query Analyzer...
April 10, 2012 at 3:08 pm
Whatever login the cold fusion is using needs access to be granted access to the symetric key and/or the certificate you used.
The probability of survival is inversely proportional to the angle of arrival.
April 10, 2012 at 3:14 pm
As a test use the same user in CF as you used in Query analyzer. And make sure you test with "standard" (not integrated AD) permissions.
April 10, 2012 at 3:41 pm
Thanks sturner and mkruger I will try that. Thanks!
April 10, 2012 at 8:46 pm
Do you know what is the minimum privilege required to encrypt data so that I can make sure CF user role has that... Thanks!
I need to grant control on certificate and grant view definition on the symmetric key...
using example below:
GRANT CONTROL ON CERTIFICATE::TEST_CERT TO myUser
GRANT VIEW DEFINITION ON SYMMETRIC KEY::TEST_SYMKEY TO myUser
July 16, 2018 at 10:29 am
Hi. This has been happening for me as well. I've tried a number of things.
Any new eyes/ideas on this?
July 16, 2018 at 10:46 am
danfarmer1973 - Monday, July 16, 2018 10:29 AMHi. This has been happening for me as well. I've tried a number of things.Any new eyes/ideas on this?
See the GRANT permissions possible for CERTIFICATEs here:
https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-certificate-permissions-transact-sql?view=sql-server-2017
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply