Solved

How to manage encyps queries mssql when sending conditioning values

Posted on 2016-11-20
10
22 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 76

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 76

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 76

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 76

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 76

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach 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.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 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

15 Experts available now in Live!

Get 1:1 Help Now