Encrypt/Decript a column?

[gbaughma]

I'm writing a routine that takes information from a web store and puts it into our accounting system.

One of the fields, however, is 3DES encrypted. I have the decryption key, but I'm a little stuck (as usual).

Is there a built-in function in SQL Server 2008 Pro that allows me to decrypt?

For example, something like:

DECRYPT([ColumnName],KEY=supersecretkey) AS DecryptedColumn

TIA!



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gbaughma]

More information:

Evidently, the original code was using the DESCryptoServiceProvider routines built into .NET.

So, is there a way to call the DESCryptoServiceProvider from SQL?



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

Yes. But I don't know how.

I know you can write a SQL Server [!]CLR[/!] function that has access to the entire .net framework. I've never done it before.

http://msdn.microsoft.com/en-us/library/w2kae45k(VS.80).aspx

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

Interesting.... that might be what I have to do....

I hope not though. (LOL)



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[mrdenny]

What version of SQL Server?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[gbaughma]

2008 Pro.

I've started writing a CLR routine... interesting... whoda thunk that I could essentially write a .NET application that becomes a function in SQL server.

The author sent me the C source for his encryption routine, and of course, I don't write in C, so I'm translating it to VB.NET in CLR. I think I've *ALMOST* got it.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gbaughma]

Ugh.

OK, the original C code had a couple of "Global" variables.

Of course, CLR does NOT like that. It gives me an error about "Cannot refer to an instance member of a class from within a shared method or shared member initializer without an explicit instance of the class."

In retrospect, however, those global variables are just used in a function, and only once... I think I'll just "Un-write" the function and make it part of the main routine, thus eliminating the need for shared globals.

See? Just posting here and I figured out a solution.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gbaughma]

mmm... still stuck.... see thread796-1612944



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[mrdenny]

Be very careful with CLR procedures in SQL Server. They can have major performance problems, and it can cause memory problems.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[gbaughma]

I got the CLR routine written; it's relatively small (about 20 lines), it's "Safe" (I had to re-write the original routine not to use globals).... and seems to be working fine. It cleans up after itself, destroys all created objects... is there anything else I should be aware of?


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[mrdenny]

Make sure that you never try and debug a SQL CLR procedure on the SQL Server. For example if you put a checkpoint in the code and hit it you'll pause the entire SQL Server process.

If there is a crash of .NET then it can take the entire SQL Server down as its running in process.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[gbaughma]

Good advise, Denny. Thank you.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com