Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Safenet encryption: queries return null Expand / Collapse
Author
Message
Posted Saturday, May 4, 2013 7:30 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 12:14 PM
Points: 596, Visits: 1,688
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 parallelism 8 0
max server memory (MB) 356352 2147483647
min server memory (MB) 356352 0
Ole Automation Procedures 1 0
remote admin connections 1 0
show advanced options 1 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




Post #1449422
Posted Wednesday, May 8, 2013 6:17 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 12:14 PM
Points: 596, Visits: 1,688
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.


Post #1450520
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse