SQL Server Always Encryption Query Stuck in Executing state

Question

We are on - Microsoft SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64) Jan 25 2021 20:16:12 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

Configured Always Encryption successfully on the DB server. As part of testing, we removed the cert from the windows cert store and ran the select query with the "Column Encryption Setting=enabled" setting, the query is throwing an error in the message as expected but the query keeps running until I kill it. This is happening in the DB server and from other servers too. Has anyone seen this issue before? If yes, what was the resolution? I am only running a select * and the default select top 1000 rows queries. Nothing fancy.Thanks in Advance!

Answer 1

Unable to submit my Answer, so posting it here - This was happening because we had symmetric Key Column level Encryption on the same table. We had multiple Symmetric keys, certs from previous testing. Once we removed all of those, the above issue disappeared. I am not sure what the old Symmetric keys had to do with the above issue as it was on another column on the same table but cleaning them up it resolved it.

Answer 2

Hi @Krishna Kamal Mandava ,

> we removed the cert from the windows cert store

Did you delete the certificate in the client machine? If so, you can export this certificate in SQL server machine, then import the certificate to the client machine. Refer to Exporting and Importing SQL Server Always Encrypted Certificates for Client Access.

If I misunderstood your issue, please let me know.

---

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