Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ENCRYPTED FIELD NOT SHOWING UP IN SEARCH

Posted on 2012-03-22
3
Medium Priority
?
470 Views
Last Modified: 2012-03-28
I have a table with a few encrypted fields using a Encryption function. This has worked for years (since 2006) but today I did a search using the encrypt function

eg select * from table1 where encrypted_field = dbo.data_encrypt('1111111111111111')
but it brings back no results but if I do
select dbo.data_encrypt('1111111111111111')
which returns XYXRE!dr257itcsWEE
and then search
select * from table1 where encrypted_field = XYXRE!dr257itcsWEE
it brings back over 400 records.
We have recently changed from SQL2005 to SQL 2008. other than that I can't think of anything else. It also seems that the records are all from before we did the changeover
0
Comment
Question by:Niall292
  • 2
3 Comments
 
LVL 80

Expert Comment

by:arnold
ID: 37755176
What is the data type for the column?
Try using cast to enclosed the encrypt function to make sure that the data types match.
0
 

Accepted Solution

by:
Niall292 earned 0 total points
ID: 37756693
We finally found the problem for some reason when we moved to SQL 2008 the encrytion was putting a line feed after the encryption, I can't explain why and don't know if this is a bug or not but that was my issue and maybe it might give somebody some help if they have the same problem.
0
 

Author Closing Comment

by:Niall292
ID: 37775642
We found the solution after playing with the function.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Copy Database Wizard 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.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

877 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