SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Scramble database for vendor
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lane0618
Posting Yak Master

134 Posts

Posted - 08/24/2006 :  12:59:42  Show Profile  Visit lane0618's Homepage  Reply with Quote
We need to send our database to a vendor who does development for us, but we can't allow them to have all of the real data. Are there any tools out there that would allow us to scramble to the data before sending it? any other options?

Thanks!

Create a Family Website! Share Photos, News, Polls, Calendar, Address Book and more! Visit www.familydetails.com for a free 30-day trial.

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 08/24/2006 :  13:45:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Encryption is better than obfuscation. There are commercial tools for this.

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

lane0618
Posting Yak Master

134 Posts

Posted - 08/24/2006 :  14:10:42  Show Profile  Visit lane0618's Homepage  Reply with Quote
But they can't use encrypted data for development...


Thanks

Create a Family Website! Share Photos, News, Polls, Calendar, Address Book and more! Visit www.familydetails.com for a free 30-day trial.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 08/24/2006 :  14:20:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
So scrambling the data is different?

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

lane0618
Posting Yak Master

134 Posts

Posted - 08/24/2006 :  14:29:48  Show Profile  Visit lane0618's Homepage  Reply with Quote
I see your point, I should have been more clear. We need to "scramble" only certain fields that contain sensitive data such as SSN, b-day, etc.

Create a Family Website! Share Photos, News, Polls, Calendar, Address Book and more! Visit www.familydetails.com for a free 30-day trial.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 08/25/2006 :  01:02:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Encrypt/scramble only those columns necessary. Many commercial tools do this efficiently.
If you want to obfuscate only, there are function posted here on SQLTeam to do that.

cmdr_skywalker has a obfuscation function here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67752&SearchTerms=encrypt

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Westley
Posting Yak Master

229 Posts

Posted - 08/25/2006 :  04:09:00  Show Profile  Reply with Quote
Or can't you just delete those column? or just update those column with some dummy records? as development shouldn't care about the data itself, all they required will be fields and make sure their function works?
Go to Top of Page

KenW
Constraint Violating Yak Guru

USA
391 Posts

Posted - 08/25/2006 :  13:17:58  Show Profile  Reply with Quote
I have a similar problem where I work because of HIPAA. I wrote a Delph front-end application that loops through each row of the data and uses a combination of either a random-number generator (for street numbers, SSN's, phone numbers, DOB) or a list of phony first names, phony last names, and a randomly grabbed first/last name pair. This obfuscates the data while still leaving it realistic enough for development purposes.

Ken
Go to Top of Page

dbadeb
Starting Member

1 Posts

Posted - 12/28/2006 :  09:39:44  Show Profile  Reply with Quote
Lane,
Were you able to find something to scramble the database for the vendor. I have the exact same situation.
quote:
Originally posted by lane0618

We need to send our database to a vendor who does development for us, but we can't allow them to have all of the real data. Are there any tools out there that would allow us to scramble to the data before sending it? any other options?

Thanks!

Create a Family Website! Share Photos, News, Polls, Calendar, Address Book and more! Visit www.familydetails.com for a free 30-day trial.

Go to Top of Page

FR33D0M_F1GHTR
Starting Member

USA
3 Posts

Posted - 03/27/2008 :  09:18:35  Show Profile  Click to see FR33D0M_F1GHTR's MSN Messenger address  Reply with Quote
I, too, have the same problem, with additional details being the database I need to scramble has 107 tables with a combined total of 2100 columns. I need something that I can use to quickly replace data in all 2100 columns with similar, yet different data. I looked at the functions posted in this thread. They were excellent. I need something for a much larger scale.

And another question, I have been a DBS for 8 years and I have never come across this before. Is this a common situation where a vendor would need a clients database?

As always, any help would be appreciated.

Thanks.

quote:
Originally posted by dbadeb

Lane,
Were you able to find something to scramble the database for the vendor. I have the exact same situation.
quote:
Originally posted by lane0618

We need to send our database to a vendor who does development for us, but we can't allow them to have all of the real data. Are there any tools out there that would allow us to scramble to the data before sending it? any other options?

Thanks!

Create a Family Website! Share Photos, News, Polls, Calendar, Address Book and more! Visit www.familydetails.com for a free 30-day trial.





-The Universe is naturally cold. It takes the power of a star to change that.
Go to Top of Page

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 03/28/2008 :  15:26:46  Show Profile  Click to see tripodal's MSN Messenger address  Reply with Quote
I know that our front end uses md5 encryption freeware, all of the sensitive data is encrypted before it hits the server. I highly reccomend this to anyone storing sensitive data. We have a custom .dll writting which the application's call inorder to read / write said data. It was not an immensely difficult undertaking, it just requires likeminded dedication from everyone involved in development.
Go to Top of Page

info-Orbium
Starting Member

1 Posts

Posted - 07/23/2009 :  15:47:33  Show Profile  Reply with Quote
Hi.

Disclaimer: answering the question posted above about a scrambling product.

Orbium Software has recently released a database scrambling product called JumbleDB.
You can read about it and download a free trial from our web site: http://www.orbiumsoftware.com/products/jumbledb-express/

Best regards,
Mike
Orbium Software
Go to Top of Page

EMarkM
Starting Member

United Kingdom
16 Posts

Posted - 07/24/2009 :  08:17:38  Show Profile  Reply with Quote
When faced with a similar challenge, we just spent a little development time writing a stored procedure (one of each database in question) that scrambled all of the data in specific fields. It basically turns text like "Mr.|Kermit The|Frog|1|High Street|Congleton|" into "Mr.|Txmrxk Xht|Gxrf|N|Hgxh Txxrs|Cxtxlgnxc|".

In real life examples the data is much more difficult to read than what I wrote above.

The sproc takes around half an hour to run on a 150GB database with several tables/fields that need to be scrambled.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000