Post reply

Using EncryptByCert in Stored Proc

  • November 14, 2006 at 10:02 pm

    #121566

    Hi I'm trying to use EncryptByCert in a stored procedure to insert encrypted data into a column in SQL Server 2005 through .NET.  When I run the stored procedure in SQL Server Management Studio the data is inserted as expected, encrypted.  But when I attempt to insert the data through my .NET code the encrypted column does not get inserted, just a null value...any ideas?  The column in my table is set to a varbinary(300).  My proc looks similar to below. 

    CREATE

    PROCEDURE [dbo].[EncryptionProc]

    @Column1 smallint,

    @Column2

    varchar(255)

    AS

    BEGIN

    Insert into TableName

    (Column1, Column2)

    values (@Column1, EncryptByCert(Cert_ID('CertificateName'), @Column2)

    END

     

  • November 15, 2006 at 6:02 am

    #671918

    whats the error that you get when you execute the query using Dot net

    Kindest Regards,

    Sureshkumar Ramakrishnan

  • November 15, 2006 at 7:41 am

    #671955

    I don't get an error, it inserts a NULL value into the column.  Thanks!

  • November 15, 2006 at 5:05 pm

    #672169

    I also tried creating a Symmetric Key on the certificate and modified the Proc to open the key before the insert is called and then close it after, but still getting the same results.  If ran in SQL Server Management Studio data is entered encrypted, when running from .NET a NULL value gets entered into the encrypted column.

  • November 15, 2006 at 10:00 pm

    #672194

    In case anyone is interested, I figured out the problem, I didn't grant CONTROL permission to the SQL Server User for the Certificate that I was using in my connection string. 

  • November 15, 2006 at 10:15 pm

    #672195

    thanks for the info

    "Keep Trying"

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply