December 28, 2009 at 5:45 pm
Hi all,
I am migrating from Sql Server 2000 to Sql Server 2008 and from Windows 2000 Server to Windows 2008 server.
I used to use a a third party .dll for encryption / decryption on Sql Server 2000 which has worked for several years and continues to work; but now I need to use built in encryption / decryption in SqlServer 2008 no problem. I created as set of asymmetric keys and wrote a udf to call from Sprocs as needed. Tested by running a sproc in query Analyzer and it works great.
I have an application that creates a report which includes the column that is encrypted of course it goes through decryption within the stored procedure; but the column returns null values when viewing this report. The report displays values using Sql Server 2000 and Server 2000 with .net 2.0.
However when I run the sproc in test mode from the query Analyzer it returns the values just fine. I use local application user no domain users and it has the proper permissions as far as I can see. It has execute permissions on the sproc that calls the udf and execute permissions on the udf. I can't find any place to assign right to the keys themselves.
Any ideas what I am missing here I have to get this up and running in a couple of weeks and I am stumped. I found nothing by google or through SqlCentral lists.
Thanks in advance for any help.
Pamela Reinskou
Pamela Reinskou
December 29, 2009 at 8:18 am
Are you sure the report is referencing the correct column name?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 30, 2009 at 1:04 pm
Can you please post your SQL, changing relevant parts of course...
January 6, 2010 at 10:45 am
are you using the same login that the report uses when you are testing with tsql?
The probability of survival is inversely proportional to the angle of arrival.
February 5, 2010 at 5:36 pm
Thankfully there was a delay in the project caused by another group; but now I am back with the same problem.
The reason the report has null values for that column is because the value for the column is null.
A web application using a sql server local account executes stored procedure to input a record into a table. The code for the insert is
Insert into myTable(receiptId, dtmTrans, transAmount,acctName,transType, acctNumber,acctExpDate, SubscriberId, ActivityId)Values(@ReceiptId, @dtmTrans, @transAmount, @AcctName, @TransType, newFrontier.dbo.PYRFN_EncryptThis512(@AcctNumber), @ExpDate, @SubscriberId,@ActivityCode)
If I run this using query analyzer or whatever it is called in 2008 the column is filled with the encrypted value; but if I call it from the application the column is null. The udf has permssions set to execute for the user. The code for the udf is
Declare @NewString varbinary(512)
Set @NewString = (Select EncryptByAsymKey(AsymKey_ID('mykey512'),@OrigString))
All the other columns are set correctly just this one column is null. No errors are raised, I tried to code it separately just so I could test for an error and none was raised.
Pamela Reinskou
February 5, 2010 at 6:22 pm
Seems like a permissions issue on the EncryptByAsymKey but I can't find anywhere where permissions required are mentioned.
Have you run this using the application login?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 5, 2010 at 10:58 pm
Problem solved and yes it was permissions the account used to call the key needs to have control permissions; but there is nowhere to actually add the permissions. If you go back to my original message I eluded to that; but never resolved the problem.
Turns out the only way you can grant permissions to certain objects is to code it (don't get me started)
GRANT CONTROL To ASYMMETRIC KEY :: asymmetric_key
That took care of the problem.
Thanks for the help! Now where is that bottle of Dewar's? :satisfied:
Pamela Reinskou
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply