?
Solved

EncryptByPassPhrase issue

Posted on 2014-08-07
8
Medium Priority
?
711 Views
Last Modified: 2014-08-08
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
0
Comment
Question by:ukerandi
  • 4
  • 3
8 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 40247569
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

0
 
LVL 23

Expert Comment

by:nemws1
ID: 40247572
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.
0
 
LVL 10

Author Comment

by:ukerandi
ID: 40247573
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 23

Accepted Solution

by:
nemws1 earned 2000 total points
ID: 40247580
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'.
0
 
LVL 10

Author Comment

by:ukerandi
ID: 40247593
ok then how is hide datakey from Database Administrators and Programmers. Only key can have to know HR manager
0
 
LVL 23

Expert Comment

by:nemws1
ID: 40247609
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. :)
0
 
LVL 27

Expert Comment

by:skullnobrains
ID: 40248065
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
0
 
LVL 10

Author Closing Comment

by:ukerandi
ID: 40248128
Good Logic
0

Featured Post

Automating Your MSP Business

The road to profitability.
Delivering superior services is key to ensuring customer satisfaction and the consequent long-term relationships that enable MSPs to lock in predictable, recurring revenue. What's the best way to deliver superior service? One word: automation.

Question has a verified solution.

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

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 shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 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