Safenet encryption: queries return null

  • Something about our production sql cluster is preventing the initial setup for Safenet encryption. The device sends sql to our prod cluster via jdbc driver. We know the sql arrives because we've captured it in profiler. The error returned is: microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set

    The sql sent to sql 2005 enterprise cluster from Safenet via jdbc driver, captured in profiler:

    Note paramater @p1 is set to null but the following call to sp_cursorprepexec places “NULL” as a parameter instead of variable @p1

    Partial profiler capture:

    set quoted_identifier on

    set arithabort on

    set numeric_roundabort off

    set ansi_warnings on

    set ansi_padding on

    set ansi_nulls on

    set concat_null_yields_null on

    set cursor_close_on_commit off

    set implicit_transactions off

    set language us_english

    set dateformat mdy

    set datefirst 7

    set transaction isolation level read committed

    set transaction isolation level read committed

    set implicit_transactions on

    declare @p1 int

    set @p1=NULLdeclare @p2 int

    set @p2=0

    declare @p7 int

    set @p7=0

    exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'declare @database varchar(128), @owner varchar(128), @exitStatus int set @exitStatus = 1 exec master.dbo.ing_get_metadata_info @database out, @owner out, @exitStatus out; select @database, @owner, @exitStatus ',16,8193,@p7 output

    select @p1, @p2, @p7

    NULL

    Prod sql configurations that can be considered non-standard or at least not default.

    name run_value default_value

    Agent XPs 1 0

    clr enabled 1 0

    Database Mail XPs 1 0

    fill factor (%) 90 0

    max degree of parallelism8 0

    max server memory (MB)356352 2147483647

    min server memory (MB)356352 0

    Ole Automation Procedures1 0

    remote admin connections1 0

    show advanced options1 0

    user options 20856 0

    xp_cmdshell 1 0

    User options for my sql connection to prod cluster:

    21880

    ANSI_WARNINGS

    ANSI_PADDING

    ANSI_NULLS

    ARITHABORT

    QUOTED_IDENTIFIER

    ANSI_NULL_DFLT_ON

    CONCAT_NULL_YIELDS_NULL

    XACT_ABORT

  • We found that having user option XACT_ABORT turned on was causing the problem. No one is sure how this got set in production since user options ( in sys.configurations ) is zero on most of our QA sql servers. We'll be turning this off soon -- its dynamic so could be turned back on quickly if there were issues, but it does wipe the execution plan cache when you change it.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply