ADO.NET and Stored Procedures

1. ADO.NET and Stored Procedures - Swetha Kulkarni

2. RDBMS ADO.NET Provider • SqlClient • OracleClient • OleDb • ODBC • SqlServerCE • System.Data.SqlClient • System.Data.OracleClient • System.Data.OleDb • System.Data.Odbc • System.Data.SqlServerCe Application Dataset

3. RDBMS ADO.NET Provider Connection Application Dataset

4. RDBMS ADO.NET Provider Connection Dataadapter Dataadapter Application Dataset Datatable Datatable

5. ADO.NET Objects System.Data • Contains the “main” classes of ADO.NET • In-memory cache of data • In-memory cache of a database table • Used to manipulate a row in a DataTable • Used to define the columns in a DataTable • Used to relate 2 DataTables to each other DataSet DataTable DataRow DataColumn DataRelation

6. Benefits of Stored Procedures • Stored procedures pass less information over the network on the initial request. Hence faster • Parameterized stored procedures that validate all user input can be used to thwart SQL injection attacks • Errors can be handled in procedure code without being passed directly to client applications • Stored procedures can be written once, and accessed by many applications

7. Security Overview – ADO.NET • Design for Security • Threat Modeling • The Principle of Least Privilege

8. Authentication • If possible, use Windows authentication • SqlConnectionpubsConn = new SqlConnection( "server=dbserver; database=pubs; Integrated Security=SSPI;"); • If you use SQL authentication, use strong passwords • SqlConnectionString = "Server=YourServer\Instance; Database=YourDatabase; uid=sa; pwd=;" • Consider Which Identity to Use to Connect to the Database

9. Ownership chain

10. Authorization • Restrict Unauthorized Code • Restrict Application Access to the Database

11. Configuration and Connection Strings • Avoid Credentials in Connection Strings • Store Encrypted Connection Strings in Configuration Files <connectionStrings> <add name="MyDatabaseConnection" connectionString="Persist Security Info=False;Integrated Security=SSPI;database=Northwind;server=(local);" providerName="System.Data.SqlClient" /> </connectionStrings> • Do Not Use Persist Security Info="true" or "yes" • Avoid Connection Strings Constructed With User Input

12. Exception Management • Use Finally Blocks to Make Sure that Database Connections Are Closed • Consider Employing the Using Statement to Make Sure that Database Connections Are Closed • Avoid Propagating ADO.NET Exceptions to Users • In ASP.NET, Use a Generic Error Page , Log exceptions on the server

13. Secure Data Access • Authentication, Authorization and Permissions • Parameterized Commands and SQL Injection • Script Exploits • Probing Attacks

14. Privacy and Data Security • Cryptography and Hash Codes • Encrypting Configuration Files • Securing String Values in Memory

15. Best Practices – Stored Procedures • Grant EXECUTE permissions for database roles • Revoke or deny all permissions to the underlying tables for all roles and users in the database • Do not add users or roles to the sysadmin or db_owner roles • Disable the guest account. This will prevent anonymous users from connecting to the database

16. References • http://www.guidanceshare.com/wiki/ADO.NET_2.0_Security_Guidelines • http://msdn.microsoft.com/en-us/library/ms971481.aspx • http://msdn.microsoft.com/en-us/library/bb669058.aspx

17. Thank You