SSL between web and SQL server

[soky]

Application: eCommerce: Windows (shared) Hosted + Active Server Pages + SQL

I was going to email this to our resident yet down under Microsoft SQL Server™ .expert, but I thought I would post the question here for the benefit of us all.

I am concerned about information security between the web server and the SQL server. I have back up servers on separate hosting companies for redundancy and my concern lies between the server farms but also within the farm itself.

I have purchased a SSL cert from VeriSign and tested the encryption of the site. Where I am concerned is the data transfer through the connection to the database.

I have tried to specify the server with https:// but as expected, that failed. I am thinking the encrypted method might work but am hoping someone has experience
----------------------------------
Syntax
object. Encrypted

Parts
object
Expression that evaluates to an object in the Applies To list

Data Type
Boolean

Modifiable
Read-only

Prototype (C/C++)
HRESULT GetEncrypted(LPBOOL pRetVal);

Remarks
The Encrypted property returns TRUE if a stored procedure was created with encryption. This is useful when determining whether a stored procedure can be replicated, because encrypted stored procedures cannot be replicated.

Note__Encrypted can be used with Microsoft® SQL Server™ 2000 and SQL Server version 7.0, except when used with the UserDefinedFunction object.
-----------------
Has anyone experience with this? I think I have a brain vacuum or something at the moment because I recall there is a way to specify encryption in the data connection... but I just can't think of it. (Or I am hallucinating all together.)

[Brangwyn]

Your possibly thinking of Trusted_Connection = YES This isn't available in this shared environment though

[soky]

Originally posted by Brangwyn
Your possibly thinking of Trusted_Connection = YES This isn't available in this shared environment though

Yes sir, I kinda figured that would not work... but I was kind of hoping someone had experience with applying the encrypted method to the connection object.

[Brangwyn]

Beween Database Server and the webserver the only way to intercept data is basically to be physically at the datacentre if its an application you really need to be paranoid about then I'd be asking myself if shared hosting was really appropriate

Anyway, I should have given you more info when I posted earlier. Firstly the SQL Server has to be setup to allow SSL, part of this involves the actual SQL Server having its own SSL Certificate installed (something I'd be confident in saying Vortech doesnt have setup currently).

Once the certificate is installed correctly on the SQL Server you should then be able to use a connection string like this

"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=Northwind;Data Source=sql01;Use
Encryption for Data=True"

[soky]

Originally posted by Brangwyn

Anyway, I should have given you more info when I posted earlier. Firstly the SQL Server has to be setup to allow SSL, part of this involves the actual SQL Server having its own SSL Certificate installed (something I'd be confident in saying Vortech doesnt have setup currently).

Thank you very much sir! This would explain a good number of things. Yeah... good question about the shared hosting decision and it one I hope to resolve by years end.

Based upon your answer and a number of articles I have read on the subject... I am opting to encrypt only the sensitive field information. That is CC and ID key information. Vortech and most shared hosts provide components that allow for string encryption and when all else fails there is Mike Shaffer's solution. ( http://www.4guysfromrolla.com/webtech/010100-1.shtml )

It is good to know that it is difficult to "sniff" the data... my main concern was the times I was using a SQL server at one farm and a web server at another.

The solution appears (for us paranoid types) an IBM blade server with the Web server/s and SQL server in the same box... then setting the trusted connection would be a cinch. If I continue to require redundancy at that point, then the back up location should mirror the hardware, application and set up by the main box.

Thanks again my friend. You are most helpful.

[soky]

I am thinking these issues are more urgent than my other concerns... (Shared for everyone benefit)

Security Tips
Defend Your Code with Top Ten Security Tips Every Developer Must Know
Michael Howard and Keith Brown

http://msdn.microsoft.com/msdnmag/is...s/default.aspx

[Brangwyn]

You might want to have a look at Frez's code here actually, theres some good SHA and MD5 vbScripts that I've used for encypting (well hashing) passwords

http://www.frez.co.uk/freecode.htm