ADO.NET and SQL Server security, performance Recommendations

The typical application scenario is an internet web application located in DMZ then submits data via ADO.NET, either in-process or though middle tier.

1. Use windows authentication.
1) The web server should not be in the trusted domain. Create a local NT user account ‘Fred’ with least privileges. This account will be used as the IIS anonymous access account for the web application.
2) Enable IIS anonymous access for the web application.
3) On the Sql Server, mirror the NT account ‘Fred’ created above. Set Sql Server security login mode to be ‘Windows Only’.
4) Create a SQL Server Login for ‘Fred’. Grant ‘Fred’ to ‘Public’, ‘Deny Data Reader’ and ‘Deny Data Writer’ access.
5) Allow application access data via Store Procedures only. Grant ‘Fred’ to all user- defined store procedures exec rights.

2.When you specify a server in an ADO.NET connection string, always use IP address instead of the server's DNS name to cut the overhead on DNS resolution.

3.Specify 'Application Name' in the connection string. It makes SQL Profiler can pick up the connections quite handy, or just for trouble-shooting.

4.Do not use SQL Server application roles, it turns off connection pooling.

5.When possible, use the ExecuteNonQuery method with SQLCommand objects, as this is the most efficient way to execute queries from ADO.NET. Use output parameters with SQLCommand objects if you need to retrieve just a few values, or a single data row, instead of using more expensive techniques, such as a SQLDataAdapter, a SQLDataReader, or a strongly typed DataSet.

6.Avoid using Transact-SQL cursor at all possible, try use correlated sub-query or temporary table instead.