[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to manage encyps queries mssql when sending conditioning values

Posted on 2016-11-20
10
Medium Priority
?
58 Views
Last Modified: 2016-11-23
we are working with encrpted sp and wanted to know EE advice when needing a store procedure to have a "where" clause in the querey.  Is it treated as regular SP?
0
Comment
Question by:rayluvs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 80

Expert Comment

by:arnold
ID: 41895252
Your encryption is database level or application level?
One you can use straight where since the db will decrypt and search the index, the other you will be looking for the encrypted hash pattern in the index. Though usually, application level encrypted columns are not searchable I.e passwords, personally identifying entries that should not be exposed/visible......
0
 

Author Comment

by:rayluvs
ID: 41895283
Query level (we set our SP with 'with encryption') but called from VB apps.  We have created a series of SP with encryption and works fine, but
we have come upon that certain queries needed to include conditions when executed from a VB apps.  For example SELECT * FROM TABLE WHERE SALES_CODE='VALUE-FROM-VB'; in vb

cmd.CommandText = "select SALES_CODE from TABLE where SALES_CODE='" & SalesCodeField.Value & "'"
Set rst = cmd.Execute

Open in new window


Our concern, how to handle that value being sent from VBA?
0
 
LVL 80

Expert Comment

by:arnold
ID: 41895347
Is encryption enabled on the DB in question?
If you merely encrypt the connection between the client and the SQL server, the where clause is fine since there is no encryption on the DB itself. I.e. You are securing the query from being captured/analyzed.

Possibly similar to those who host websites behind https, the data is only encrypted during transport. It is not encrypted on the server nor is it encrypted when displayed in the clients, browser.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:rayluvs
ID: 41897633
Sorry for the delay.

No DB encryption enabled in server, we created out SP with the clause or argument "with encryption".

So you're saying if we need to run an encrypted SP based on condition, use the  "where" clause?
0
 
LVL 80

Expert Comment

by:arnold
ID: 41898405
The encryption if I understand your response deals with securing the communication channel rather than the data.

Have you tried using the where clause and got an error no data while direct query using the same parameters returns info?
0
 

Author Comment

by:rayluvs
ID: 41899114
No we haven't.   That why we placed the question.  We thought that being encrypted the SP would not work with "where", that is, sending arguments to the SP to get a "resulting" response from the store procedure.
0
 
LVL 80

Expert Comment

by:arnold
ID: 41899529
IMHO, testing when there is no impact to the data is the best way to get an answer.

The only thing that is encrypted in this scenario is the SP iteself. i.e. its logic, process can not be analyzed from the SP listing.

so to your point, you can add where clause, etc. but you should use conditional/optional where clause to use the SP in more than one scenario.
0
 

Author Comment

by:rayluvs
ID: 41899541
We definitely are doing something wrong.  Below is the SP created.  We want to run it, send the value of the column we are looking for and have the result displayed back (or retrieved in our VBA)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE __testSP
(@Param1 char(15)
,@Param2 char(15) OUTPUT)
with encryption as BEGIN
	SET NOCOUNT ON;
	select Col1 from TableName
	WHERE Col1= @Param1
	SELECT @Param2 = @Param1
	RETURN 
END
GO

Open in new window


What are we doing wrong?

Note:
We are running it as "exec __testSP('pp1000')
0
 
LVL 80

Accepted Solution

by:
arnold earned 2000 total points
ID: 41899565
What is the point of assigning the @param1 you provide when calling the SP and assigning it to PARAM2?

Presumably you want the table which is the result of the select Col1 from TableName
      WHERE Col1= @Param1

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE __testSP
(@Param1 char(15)
,@Param2 char(15) OUTPUT)
with encryption as BEGIN
	SET NOCOUNT ON;
	select @Param1=Col1 from TableName
	WHERE Col1= @Param1
	RETURN 
END
GO

Open in new window

@variable=exec sp
@variable defined as table will get the resulting dataset.
0
 

Author Comment

by:rayluvs
ID: 41899597
Thanx!
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question