Solved

How to manage encyps queries mssql when sending conditioning values

Posted on 2016-11-20
10
52 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 78

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 78

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 78

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 78

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 78

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

707 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