SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Safenet encryption: queries return null


Safenet encryption: queries return null

Author
Message
Indianrock
Indianrock
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1944 Visits: 2371
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



Indianrock
Indianrock
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1944 Visits: 2371
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search