Any query closes my symmetric key

Question

Steps to reproduce:

var g_conn = new ActiveXObject ("ADODB.Connection"), an_ix = 0
g_ans = Array (02)
g_conn .Open ("DSN=greenfield_ms", "sa", "***********")
g_conn .Execute ("OPEN SYMMETRIC KEY K1 DECRYPTION BY PASSWORD = 'T0p-s3cr3T';")
for (; an_ix < 2; ++an_ix)
g_ans [an_ix] = g_conn .Execute ("SELECT COUNT(*) FROM sys.openkeys") .Fields (0) .Value
WScript .Echo (g_ans)

Expected result: 1,1

Actual result: 1,0

It looks like the 1st query closes the symmetric key.  Why?

---

Christopher Yeleighton

Answer 1

Hi Yecril,

I don't know why its doing that. I suggest you to open symmetric key with in your code as follow:

var g_conn = new ActiveXObject ("ADODB.Connection"), an_ix = 0
g_ans = Array (02)
g_conn .Open ("DSN=greenfield_ms", "sa", "***********")
for (; an_ix < 2; ++an_ix)
g_conn .Execute ("IF NOT EXISTS (select * from sys.openkeys where key_name='K1')
					BEGIN OPEN SYMMETRIC KEY K1 DECRYPTION BY PASSWORD = 'T0p-s3cr3T' END;")
g_ans [an_ix] = g_conn .Execute ("SELECT COUNT(*) FROM sys.openkeys") .Fields (0) .Value
WScript .Echo (g_ans)

The above code will always return you correct information.

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 2

I suspect that what is going on is that ADO opens a second connection behind the covers, and in the second connection, there are of course no open keys. ADO opens a second connection when not all rows from the previous command has been processed. I don't really see why it happens here, but it was a while since I worked with ADO.

You can test the hypothesis, by changing the query to

SELECT @@spid, COUNT(*) FROM sys.openkeys

If you get different values, ADO opened a second connection for you.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

Indeed, @@spid gets incremented each time a select query is issued.  The key returns at the 101th query (and, I suppose, every 100th query).

---

Christopher Yeleighton

Answer 4

I am trying to avoid sending the password along with each query.  I would accept that I have to send it 100 times but your solution requires sending it whether it is needed or not.

---

Christopher Yeleighton

Answer 5

ADODB creates a session for each additional recordset that is open. 

Fix:

g_rs = g_conn .Execute ("SELECT COUNT(*) FROM sys.openkeys") 
g_ans [an_ix] = g_rs .Fields (0) .Value
g_rs .Close ()

---

Christopher Yeleighton