locked
Any query closes my symmetric key RRS feed

  • 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



    • Edited by yecril Thursday, August 23, 2012 9:40 AM
    Thursday, August 23, 2012 9:34 AM

Answers

  • 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

    • Marked as answer by yecril Friday, August 24, 2012 8:53 AM
    Friday, August 24, 2012 8:53 AM

All replies

  • 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.

    • Proposed as answer by Basit Farooq Thursday, August 23, 2012 5:42 PM
    Thursday, August 23, 2012 5:41 PM
  • 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, esquel@sommarskog.se
    Thursday, August 23, 2012 9:42 PM
  • 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


    • Edited by yecril Friday, August 24, 2012 7:38 AM
    Friday, August 24, 2012 7:08 AM
  • 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

    Friday, August 24, 2012 7:42 AM
  • 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

    • Marked as answer by yecril Friday, August 24, 2012 8:53 AM
    Friday, August 24, 2012 8:53 AM