decrypting table contents

Question

decrypting table contents

Shahrukh Khan

SSC-Addicted

Points: 430
More actions
March 7, 2009 at 8:54 pm

#135273

Hello,
I have a few tables in SQL Server 2005 that are encrypted. I would like to decrypt them to see the contents. These tables do not have sensitive data. How do I decrypt these tables?
Thank you,
SRK:)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 1

Ummmm.... if they don't have sensitive information in them, why would someone encrypt them? Also, if you really need to have them decrypted, see the person with the encryption key. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 2

I think that strongly depends on how they were encrypted? Keys? Pass phrase? certificates? SQL Server 2005 has nice set of encrypt/decrypt functions. But in all cases (someone correct me if I am wrong) .. you need to know the original encryption method.

Here is a brief overview for it .. http://www.databasejournal.com/features/mssql/article.php/3483931/SQL-Server-2005-Security---Part-3-Encryption.htm

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Were they encrypted in the database or in the application?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Shahrukh Khan SSC-Addicted Points: 430 More actions · Answer 4

Our application developer/consultant encrypted them in the application.

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 5

Shahrukh Khan (3/9/2009)
Our application developer/consultant encrypted them in the application.

So no problem, right? Just ask that person for the encryption key. If (s)he doesn't give it to you, sue for faulty services.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 6

Yaa Jeff go hunt down the 3rd party vendor :). Jeff likes dealing with them hehe.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 7

Mohit (3/9/2009)
Yaa Jeff go hunt down the 3rd party vendor :). Jeff likes dealing with them hehe.

Man, it just amazes me that people hire consultants/3rd parties without a contract depicting expected performance and restrictions. For example, there should be a stipulation that all code will be open source and included in the deliverables... not encrypted, etc, etc. I feel bad for the folks that end up like the OP on this thread, but if it's good encryption, you're just gonna need the keys to get in and that's all there is too it. If it's simple encryption by SQL Server, that's breakable and there are dozens of posts on Google as to how to do that.

But, even if I could break the higher levels of encryption, I sure wouldn't tell anyone how to do it on this forum because I don't know what it will actually be used for.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 8

We had a consultant who had developed an app in outlook forms. Because we didn't want to pay him to support it; we had to pay him $$$ after his contract was over to get the passwords so we can support the app ourselves. Made me soo mad .. I was temped to "figure" it out .. but was talked out of it hehe.

😉

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Ross McMicken SSCarpal Tunnel Points: 4438 More actions · Answer 9

A developer who encrypts data, uses passwords to hide code, etc. will never get another engagement from me. That's just evil.

Jeff is correct, as usual, the duties of the contractor should be spelled out in the contract, including the use of encryption, ownership of keys, the fact that the product is the property of the purchaser, not the developer, etc.

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 10

Thanks for the kudo, Ross :blush:

I also understand that there are times when the Consultant or other 3rd party wants to protect proprietary code through encryption... that, too, should be spelled out in the contract.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 11

The real fault most probably lies with management in this instance. To me it sounds like a question of either the 'old boy' network or just plain 'cutting corners'.

The bottom line is someone did not do it right, their homework that is. And as usual support staff and the user community, not to mention the business, will suffer to some degree in one form or another.

It sounds like we are back to the maxim:

- There is never time to do it right, but always time to do it again !

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."