How to transfer encrypted data between SQL Server 2005 and SQL Server 2000

Microsoft SQL Server 2000
SQL Server
SQL Server 2005
SQL Server security
SQL Server upgrades
We are a retail/web/phone order store. We process credit cards for all three stores at our retail location. We have our web data stored offsite on the web server, using SQL Server 2005. We have our retail/phone order data stored onsite in SQL Server 2000. I'd like to add a function to our MS Access POS software to retrieve our web orders from SQL Server on the web server. I'd like to transfer the credit card # from our web server onto the retail server in encrypted form (this is important). Then once on our retail store SQL Server, decrypt the credit card # to charge the card. What encryption would I use since the two SQL Servers are different versions? Where do I start? I should also note that our website is built using ColdFusion.

Answer Wiki

Thanks. We'll let you know when a new response is added.

SQL Server 2005 includes some native encryption procedures which you can use to encrypt the data. However decrypting the data on the SQL 2000 server or the Access database will be harder as they don’t include the same encryption procedures. (SQL 2000 doesn’t include any native encryption procedures at all.)

If you have the option of setting up a VPN connection between your store and the co-lo network that would be the easiest way to encrypt the data as all data sent over the VPN would be encrypted.

There are some third party DLLs which can be used to encrypt and decrypt data within SQL Server 2000. Your best bet would be to find one that uses an encryption level which you are happy with and install this third party DLL on both the SQL 2000 and SQL 2005 systems (so that you can use the same code on both systems) and use this DLL as an extended stored procedure to encrypt and decrypt the data.

If you don’t have the option of installing a third party DLL on the hosting companies SQL Server you may want to look to upgrading your local system to SQL Server 2005 so that you can use the native SQL Server 2005 encryption on both ends of the system.

Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: