Solved

ENCRYPTED FIELD NOT SHOWING UP IN SEARCH

Posted on 2012-03-22
3
463 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
[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
  • 2
3 Comments
 
LVL 78

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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…
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.

751 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