First off, I'm using SQL Server 2k for the backend and ASP.NET/VB.NET for the front-end.

A few questions were posed to me by my boss, and I'd like to get some constructive advice on what he's requesting, and what my answers were to him. Ok, here they are:

1) Data Export from Internet Server to Intranet Server
Current Setup: We have 2 copies of each table. One is on the web server for display-purposes only, and another is on the internal server where editing is done through front-ends.
Boss's Suggested Setup: He wants to have 1 copy of each table that resides on the internal server, and set up jobs to export certain data from the web server to the internal server.
My View: I think that if this is possible, it's not a good idea to do for security reasons. I think that it would just open us up to possible hacking directly onto our internal server.
Questions:
Can you export data from the web server to the internal server when data is edited on the web server?
If so, how secure is it to have that setup?

2) Encryption/Decryption
Description of Situation: We currently have a DB table that contains a password column that's not encrypted. My boss wants to encrypt that column directly in the DB table. I researched the subject at this and other forums, and read several accounts about how difficult it is to use encryption within SQL Server. I read from several sources that the two functions used by some (pwdencrypt and pwdcompare) are not supported by Microsoft, so you're on your own if you have any problems. Also, I read that some developers had problems when they upgraded to a newer version of SQL Server.
Questions:
1) Which setup is better with excryption/decryption:
A) Doing it within SQL Server 2k
B) Doing it on the front-end with the keys stored in a secure directory
My View: I'd only like to use a solution within SQL Server that doesn't run into the previosly mentioned set of problems. If there is no way to accomplish this, I think that it would be better to do the encryption/decryption on the application's side of things.

Ok, that should do it. I hope to hear some good advice. Thanks.

KWilliams

CREATE FUNCTION dbo.fnSimpleEncodeDecode
(
    @Password VARCHAR(8000),
    @CipherText VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS

BEGIN
    DECLARE @Index SMALLINT,
        @Position SMALLINT,
        @Items SMALLINT

    SELECT @Password = REPLICATE(@Password, DATALENGTH(@CipherText) / DATALENGTH(@Password)) + LEFT(@Password, DATALENGTH(@CipherText) % DATALENGTH(@Password)),
        @Index = DATALENGTH(@CipherText)

    WHILE @Index > 0
        SELECT @CipherText = STUFF(@CipherText, @Index, 1, CHAR(ASCII(SUBSTRING(@Password, @Index, 1)) ^ ASCII(SUBSTRING(@CipherText, @Index, 1)))),
            @Index = @Index - 1

    RETURN @CipherText
END
About the password, it is much safer NOT to store the passwords in the database. Just store a hash value such as MD5 and send this value from end-user application. Hash is one-way and cannot be decrypted but that also means that you can't recover a password if user forgets it.

Encode with UPDATE MyTable SET FirstName = dbo.fnSimpleEncodeDecode('pwd1', FirstName), LastName = dbo.fnSimpleEncodeDecode('pwd2', LastName)

Decode with UPDATE MyTable SET FirstName = dbo.fnSimpleEncodeDecode('pwd1', FirstName), LastName = dbo.fnSimpleEncodeDecode('pwd2', LastName)