Running Function under SQL ID returns NULL columns

TheCoder 91 Reputation points
2023-10-27T14:03:52.4866667+00:00

I have a function that is encrypting and decrypting columns. When I run the function under a windows ID, all of the values are returned, however, when I query the function using a SQL Server ID, the decrypted columns are NULL.

The function is using DECRYPTBYKEYAUTOCERT(CERT_ID('certname'),

I'm thinking a permission level issue with the SQL Server ID, but not 100% how to verify that or change it. I did log into SQL using that SQL ID and ran the function (select * from getMyDate() ) and the decrypted fields are NULL.

thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,947 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 112.5K Reputation points MVP
    2023-10-27T20:51:35.32+00:00

    If you look at https://learn.microsoft.com/en-us/sql/t-sql/functions/decryptbykeyautocert-transact-sql?view=sql-server-ver16#permissions, you will find that it says:

    Requires VIEW DEFINITION permission on the symmetric key, and CONTROL permission on the certificate.

    Apparently you Windows account has these permissions, while the SQL ID has not.

    1 person found this answer helpful.
    0 comments No comments

  2. PercyTang-MSFT 12,501 Reputation points Microsoft Vendor
    2023-10-30T06:45:58.9133333+00:00

    Hi @TheCoder

    Using the Windows account gets the result, while the SQL Server ID returns null.

    As expert says, the SQL Server ID does not have sufficient permissions to access the certificate or the symmetric key.

    You can use the GRANT statement to assign these permissions to the SQL Server ID.

    https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-transact-sql?view=sql-server-ver16

    Best regards,

    Percy Tang

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.