asked on

EncryptByPassPhrase issue

Hi,

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

Nem Schlecht

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

Nem Schlecht

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.

ukerandi

ASKER

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.

ASKER CERTIFIED SOLUTION

Nem Schlecht

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ukerandi

ASKER

ok then how is hide datakey from Database Administrators and Programmers. Only key can have to know HR manager

Nem Schlecht

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. :)

skullnobrains

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

ukerandi

ASKER

Good Logic