ADO.NET Security

In all database applications you're dealing with a form of some kind that will be accessed by a user who will enter information to be verified against a database or other means, before any access or data is allowed. Protecting information or access in this situation isn't too difficult a concept, it's simply a matter of storing (preferably encrypted info) into a database, then validating this on login. For more info check out - Using MD5 to Encrypt Passwords in a Database. Almost certainly your site will also include a searchable form for users to enter search criteria in obtaining data.

Aside from these somewhat innocent scenarios, and by virtue of creating forms that enables users the ability to submit information to your application, you're left vulnerable to a malevolent technique exploited by hackers known as SQL Injection . This is a security threat whereby SQL statements or other malicious code, through form fields, are injected in addition to your code and executed, and can cause great damage to your data, whether they retrieve, delete confidential information or simply cause havoc among it!

To prevent SQL Injections you should:

1. Validate all entered criteria via regular expressions for any mischievous keywords or text. Furthermore, apply HTML Encoding to any inputted text. Fortunately, version 1.1 of the .NET framework, offers superior validation, whereby if any user enters certain un-encoded HTML syntax or characters, .NET will raise an error. Read Request Validation - Preventing Script Attacks for more info.
   
2. Use parameterized Stored Procedures (Sprocs) for your queries
   
3. Create custom error pages so no one could inadvertently retrieve any data or server information from non-custom error pages.

Some added security measures when working with ADO.NET are:

1. If caching data, store critical info server-side via session state or with the Cache object. I discussed this in light of a real-world example in my article Drilldown Datagrid Searching with ASP.NET, and read more about the various method for storing state.
   
2. SQL server access should be performed through Windows Integrated Security, not "mixed mode," and never leave the default "sa" username and blank password as is, set a good username and password instead, which lead us to number 3.
   
3. If possible create a trusted connection to SQL with the Integrated Security set to SSPI (Security Support Provider Interface), as this eliminates the need in storing any user id's and passwords.

   "Data Source=myDS; Integrated Security=SSPI; Enlist=false; Initial Catalog=myCtlg" 

   
   However, you may encounter - Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON,' where remedying this is to have your Windows Account be a Domain Account and share the same or trusted domains with IIS and SQL Server.
   
4. Encrypt your connection strings in your web.config file, or other critical data elsewhere, and then later decrypt them, via the CryptoStream class in your page code, when you need to use them. Also dependent on the size of your data, whether large or small will determine Symmetric or Asymmetric Encryption.
   
5. Finally, never hard-code anything, like any of the aforementioned, in your client-side pages.