Solved

How to manage encyps queries mssql when sending conditioning values

Posted on 2016-11-20
10
38 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
  • 5
  • 5
10 Comments
 
LVL 77

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 77

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
 

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 77

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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 77

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 77

Accepted Solution

by:
arnold earned 500 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now