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
• SqlConnection pubsConn new SqlConnection(
  "serverdbserver databasepubs Integrated
  SecuritySSPI")
• If you use SQL authentication, use strong
  passwords
• SqlConnectionString "ServerYourServer\Instance
  DatabaseYourDatabase uidsa 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
• ltconnectionStringsgt
• ltadd name"MyDatabaseConnection"
  connectionString"Persist Security
  InfoFalseIntegrated SecuritySSPIdatabaseNorth
  windserver(local)" providerName"System.Data.Sq
  lClient" /gt
• lt/connectionStringsgt
• 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_Secu
  rity_Guidelines
• http//msdn.microsoft.com/en-us/library/ms971481.a
  spx
• http//msdn.microsoft.com/en-us/library/bb669058.a
  spx

17

• Thank You