Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2005
    Posts
    4

    Unanswered: Using EncryptByCert in Stored Proc

    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

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am not sure, but don't you need to open the cert first?

  3. #3
    Join Date
    Aug 2005
    Posts
    4
    I created a key and modified the proc to open and close the key, and still getting the same results. Again, everything inserts fine when I run it through SQL Server Management Studio, but when I have .NET code call the proc, a NULL value gets entered into the table where it should be an encrypted value.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by santmj
    I created a key and modified the proc to open and close the key, and still getting the same results. Again, everything inserts fine when I run it through SQL Server Management Studio, but when I have .NET code call the proc, a NULL value gets entered into the table where it should be an encrypted value.
    Shot in the dark...since I am not using 2005 right now...but what about the user context? Presumably the .NET code is running on a web server?

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Aug 2005
    Posts
    4
    I just figured out the problem, I had to grant CONTROL permissions to the certificate for the SQL User that I was using in the connection string from the web server. Thanks for the reply!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •