EncryptByPassPhrase issue


My sql server 2008 database - Emp Salary table has Salary field Decimal(18,2)

I need to Encrypt this Values

I used EncryptByPassPhrase('123456', Salary)

But Error
Error : Argument data type int is invalid for argument 2 of EncryptByPassPhrase function.

yes i undertsand the error ,But problem is i can't change the Salary field type. so  Can any one come up with good option
or how to deal with this issue, if i change this field all the report will effect more than 100 reports.
so i don't want salary field Type. and Not Allowed also.(No permission)
Any idea will highly appriciate.thx
LVL 10
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nemws1Database AdministratorCommented:
Did try casting the DECIMAL value into a VARCHAR?

select ENCRYPTBYPASSPHRASE('Do not tell anybody this', CAST(Salary AS VARCHAR(100)))
FROM your_table

Open in new window

nemws1Database AdministratorCommented:
Be careful with this - when you decrypt you do *NOT* get VARCHAR() data back - you get VARBINARY() data back which you'll have to convert.
ukerandiAuthor Commented:
yes,but Table field is Decimal, so if i convert to varchar its not possible to Insert to Salary field becuase salary field is decimal.
INSERT INTO EmpSal (Salary) values(EncryptByPassPhrase('123456', Salary))
Error : Argument data type int is invalid for argument 2 of EncryptByPassPhrase function.
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

nemws1Database AdministratorCommented:
Yes, you cannot place encrypted data into a DECIMAL field.  It will *not* work.  You could write a trigger that when somebody tries to insert something into the Salary field that it instead inserts '0.00' into the Salary field and inserts the encrypted data into a new field, say 'EncryptedSalary'.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ukerandiAuthor Commented:
ok then how is hide datakey from Database Administrators and Programmers. Only key can have to know HR manager
nemws1Database AdministratorCommented:
Yeah, pretty difficult there if you want to *do* anything with the data other than store it (ie - you wouldn't be able to do any payroll, cost analysis, etc.).  You'd have to ask for the key programmatically and pass the key to every INSERT and SELECT on this table.  Do-able, but makes things pretty complex.

Hire DBAs you can trust. :)
can't you just take the output of encryptbypassphrase and cast the hex value to an integer ? would the result be too long ?

if not there are quite a few symmetric encryption that can preserve the data type and produce short output, but they are easy to break
ukerandiAuthor Commented:
Good Logic
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.