SQL Server Advice Wanted - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Server Advice Wanted

Author

Topic

kwilliams

194 Posts

Posted - 2006-06-27 : 10:21:06

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
-------------------
It's the end of the world as we know it...and I feel fine

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 10:31:51

[code]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[/code]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.

Peter Larsson
Helsingborg, Sweden

kwilliams

194 Posts

Posted - 2006-06-27 : 10:36:59

Thanks for your quick reply:

That sounds like a good solution, except for the user not being able to get their password if they forget it. That's what we use this setup for mostly. So is there a solution in which you can decrypt and get the password back? Thanks for any advice.

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 16:47:16

quote:
Originally posted by kwilliams

Thanks for your quick reply:

That sounds like a good solution, except for the user not being able to get their password if they forget it. That's what we use this setup for mostly. So is there a solution in which you can decrypt and get the password back? Thanks for any advice.

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

Use the function.

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)

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-27 : 17:11:08

quote:
Originally posted by Peso

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.

Peter Larsson
Helsingborg, Sweden

When you post in code tags,
could you put some line returns in?
It makes it very hare to read posts when you
have a whole paragraph on one line.

CODO ERGO SUM

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-27 : 17:21:28

quote:
Originally posted by Michael Valentine Jones
When you post in code tags,
could you put some line returns in?
It makes it very hare to read posts when you
have a whole paragraph on one line.

Makes me wonder, what screen resolution do you have???

rockmoose

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-28 : 00:57:35

quote:
Originally posted by rockmoose

quote:
Originally posted by Michael Valentine Jones
When you post in code tags,
could you put some line returns in?
It makes it very hare to read posts when you
have a whole paragraph on one line.

Makes me wonder, what screen resolution do you have???

1440 x 1050.
And also I was lazy by just copy & paste yesterday. I have gotten some replies before when inserting line-breaks and the sender didn't get where to remove line-breaks.

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-28 : 03:34:53

I had no problems reading Peter, 1600x1200
I was wondering about MVJ.

rockmoose

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-28 : 05:29:35

I think MVJ mean that my CODE tag extends the rows so much that the ordinary text in all other posts are widened, and harder to read.

Peter Larsson
Helsingborg, Sweden

kwilliams

194 Posts

Posted - 2006-06-28 : 09:20:16

Ok, I've done a bit more research on the subject, and found a great article on MD5 at http://aspnet.4guysfromrolla.com/articles/103002-1.aspx. It explained the differences between one-way and two-way encryption. Although I definitely appreciate the secure nature of one-way encryption, that wouldn't work for our situation, as we are constantly having to email user their forgotten passwords.

So I was thinking of another possible solution:
1) The encrypt code would exist in a separate secure directory on the web server.
2) On form submittal, the encrypt code would work on the password field value, and it would be inserted into the DB table
3) In the DB table, only encrypted passwords would be stored. There would be no key for SQL Server, since it's done on the application side.
4) The decrypt code would exist in a separate secure directory on the intranet server.
5) Decryption of the password would only be done on an internal server through our network intranet.
6) Editing of the password could only be done on the intranet side.

Ok, that's it. I'd love to get some advice on this, including pros & cons. Thanks for all of your help.

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-28 : 09:39:47

quote:
Originally posted by kwilliams

Ok, that's it. I'd love to get some advice on this, including pros & cons. Thanks for all of your help.

Looking good to me.
I can see only one caveat with this. Mailing forgotten passwords back to users has to be done from a web page, since it no longer will be possible to do that from database. The web page has to decide which user, fetching the encrypted password, decrypt it, and send to user.

Peter Larsson
Helsingborg, Sweden

kwilliams

194 Posts

Posted - 2006-06-28 : 09:46:59

quote:
Looking good to me.
I can see only one caveat with this. Mailing forgotten passwords back to users has to be done from a web page, since it no longer will be possible to do that from database. The web page has to decide which user, fetching the encrypted password, decrypt it, and send to user.

That would definitely be true if I was to create a "Forgot my Password" page, but since our membership is so small, my boss just wants to continue to have staff manually email a member their password upon request. So it would only need to be decrypted on the intranet side. Does this sound fairly secure? I appreciate your advice.

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources