SQL Server 2019 Encryption - without Views or special SQL

Alex Kerezy 1 Reputation point
2021-01-25T19:53:58.373+00:00

Hi,
I want to encrypt various columns on various tables in SQL Server 2019.

However - I want nothing (zero) to change for the current users. In other words I want to achieve this through roles and:

  • Without using views [extra work, and requires users to know where to query]
  • Without users having to write any special words in their SQL like encrypt/decrypt

I want role based encryption, but the users -- up front -- do nothing different, and may not even know some data is encrypted. They just run their queries in SSMS or run their reports and then the data back either encrypted or decrypted depending on their role (and of course, the data).

The users, which are assigned to roles, don't have to do anything special, they don't have to know anything special - they just run their queries and reports like they've always done.

Is this even possible????

Everything I see online uses views and requires special SQL (encrypt or decrypt), so I'm wondering if this is even possible.

Thanks
Vmuisc

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,815 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.9K Reputation points MVP
    2021-01-25T22:27:51.2+00:00

    The requirements are not exactly clear, but this is a general summary of what is available:

    • Cell-level encryption. Has been around since SQL 2005. You can encrypt columns inside stored procedures or in clent code that submits SQL statements. Keys are stored in SQL Server, so the DBA can always get to the data.
    • Transperant Data Encryption. Available since SQL 2008, and starting with SQL 2019 also in Standard Edition. The entire database is encrypted, but if you are connected you will never notice. The aim is to protect data at test.
    • Always Encrypted, introduced in SQL 2016. Encryption occurs client-side, and SQL Server only knows where the keys are stored, but the DBA cannot get to the data. In your case, you would distribute the encryption keys to the users who want to see it.

    Overall, there are many wet dreams about encryption being transperant, but in the end, security often means a lot of hassle. And for a good reason.

    1 person found this answer helpful.
  2. Erland Sommarskog 101.9K Reputation points MVP
    2021-01-26T22:13:59.017+00:00

    Seeing your reply to Cathy, I think the feature that best meets you requirement is Dynamic Data Masking. With this feature data is not stored as encrypted. However, unless the user has the UNMASK permission, the data showed as masked. For instance, in your example, the first user would see something like xxxxxx1234, depending on how you set up the mask.

    You can read more about Dynamic Data Masking here: https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver15

    1 person found this answer helpful.
  3. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-01-26T03:42:27.427+00:00

    Hi @Vmuisc,

    We use symmetric keys on column level SQL Server encryption , please use the following steps for column level encryption:

    1.Create a database master key
    2.Create a self-signed certificate for SQL Server
    3.Configure a symmetric key for encryption
    4.Encrypt the column data
    5.Query and verify the encryption

    If we want decrypt data using the symmetric key, we need provide permissions to the Symmetric key and Certificate to user. If users do not have this permission, they can see the encrypted records, but they cannot understand the data without decrypting it.

    •Symmetric key permission: GRANT VIEW DEFINITION
    •Certificate permission: GRANT VIEW DEFINITION and GRANT CONTROL permissions

    Please refer the blog An overview of the column level SQL Server encryption to get detail steps.

    If I misunderstood your issue, please let me know.
    Best regards,
    Cathy

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.