Transcript ADO.NET and Stored Procedures

ADO.NET AND STORED
PROCEDURES
- Swetha Kulkarni
RDBMS
ADO.NET Provider





SqlClient
OracleClient
OleDb
ODBC
SqlServerCE
Application
Dataset
•
•
•
•
•
System.Data.SqlClient
System.Data.OracleClient
System.Data.OleDb
System.Data.Odbc
System.Data.SqlServerCe
RDBMS
ADO.NET Provider
Connection
Application
Dataset
RDBMS
ADO.NET Provider
Connection
Dataadapter
Dataadapter
Application
Dataset
Datatable
Datatable
ADO.NET Objects
System.Data
DataSet
DataTable



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

DataRow
DataColumn
DataRelation
Contains the “main” classes of ADO.NET
In-memory cache of data
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
Security Overview – ADO.NET

Design for Security
-

Threat Modeling
The Principle of Least Privilege
Authentication

If possible, use Windows authentication
 SqlConnection
pubsConn = 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
Ownership chain
Authorization

Restrict Unauthorized Code

Restrict Application Access to the Database
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
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
Secure Data Access




Authentication, Authorization and Permissions
Parameterized Commands and SQL Injection
Script Exploits
Probing Attacks
Privacy and Data Security

Cryptography and Hash Codes

Encrypting Configuration Files

Securing String Values in Memory
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
References



http://www.guidanceshare.com/wiki/ADO.NET_2.0
_Security_Guidelines
http://msdn.microsoft.com/enus/library/ms971481.aspx
http://msdn.microsoft.com/enus/library/bb669058.aspx
Thank You