question

KrishnaKamalMandava-1599 avatar image
0 Votes"
KrishnaKamalMandava-1599 asked Cathyji-msft commented

SQL Server Always Encryption Query Stuck in Executing state

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!

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KrishnaKamalMandava-1599 avatar image
0 Votes"
KrishnaKamalMandava-1599 answered Cathyji-msft commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KrishnaKamalMandava-1599,

Thanks for sharing the solution for this issue. Please make your solution as answer, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft converted comment to answer

Hi @KrishnaKamalMandava-1599,

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.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, thank you for your time. The issue is both on client server and the database server. We are testing a negative test scenario where if we delete the certificate from windows cert store, any query run on the table with encrypted column should throw an error message saying the cert is unavailable. Now, it is throwing an error message but the query keeps executing indefinitely. I checked the session in the backend and it is in sleeping state and is not consuming any resources.

0 Votes 0 ·