Click here to Skip to main content
15,397,324 members
Articles / Web Development / ASP.NET
Posted 28 May 2010


16 bookmarked

Protecting ADO.NET Applications - Part I

Rate me:
Please Sign up or sign in to vote.
3.40/5 (4 votes)
28 May 2010CPOL5 min read
Protecting ADO.NET applications


This article is based on concepts I have acquired on Microsoft Virtual Academy (MVA).

Writing a secure ADO.NET application involves more than avoiding common coding pitfalls such as not validating user input. An application that accesses data has many potential points of failure that an attacker can exploit to retrieve, manipulate, or destroy sensitive data, so we need to understand all security aspects.

.NET Framework provides many classes, services and tools that are useful to protect and manage database applications. The common language runtime (CLR) provides a type-safe environment for code to run in, with code access security (CAS) to further restrict the permissions of managed code.

Note that secure code does not guard against self-inflicted security holes when working with unmanaged resources such as databases.

Securing an application is an ongoing process. There will never be a point where a developer can guarantee that an application is safe from all attacks, because it is impossible to predict what kinds of future attacks new technologies will bring about. Conversely, just because nobody has yet discovered (or published) security flaws in a system does not mean that none exist or could exist. You need to plan for security during the design phase of the project, as well as plan how security will be maintained over the lifetime of the application.

Design for Security

One of the biggest problems in developing secure applications is that security is often an afterthought, something to implement after a project is code-complete. Not building security into an application at the outset leads to insecure applications because little thought has been given to what makes an application secure.

Last minute security implementation leads to more bugs, as software breaks under the new restrictions or has to be rewritten to accommodate unanticipated functionality. Every line of revised code contains the possibility of introducing a new bug. For this reason, you should consider security early in the development process so that it can proceed in tandem with the development of new features.

Principle of Least Privilege

When you design, build, and deploy your application, you must assume that your application will be attacked. Often these attacks come from malicious code that executes with the permissions of the user running the code.

One counter-measure you can employ is to try to erect as many walls around your code as possible by running with least privilege. The principle of least privilege says that any given privilege should be granted to the least amount of code necessary for the shortest duration of time that is required to get the job done.

The best practice for creating secure applications is to start with no permissions at all and then add the narrowest permissions for the particular task being performed. By contrast, starting with all permissions and then denying individual ones leads to insecure applications that are difficult to test and maintain because security holes may exist from unintentionally granting more permissions than required.

Security Database

The principle of least privilege also applies to your data source. Some general guidelines for database security include:

  • Create accounts with the lowest possible privileges.
  • Do not allow users access to administrative accounts just to get code working.
  • Do not return server-side error messages to client applications.
  • Validate all input at both the client and the server.
  • Use parameterized commands and avoid dynamic SQL statements.
  • Enable security auditing and logging for the database you are using so that you are alerted to any security breaches.

Protecting Connection Information (ADO.NET)

Using Windows Authentication

To help limit access to your data source, you must secure connection information such as user ID, password, and data source name. In order to avoid exposing user information. A recommended action is the use of Windows mode authentication; Windows authentication is specified in a connection string by using the Integrated Security or Trusted_Connection keywords, eliminating the need to use a user ID and password.

When using Windows authentication, users are authenticated by Windows, and access to server and database resources is determined by granting permissions to Windows users and groups.

However if we have some reason for not using Window Mode Authentication, then we need to extreme precautions. Using ASP.NET, you can enable impersonation in web.config.

Let's see an example:

<identity impersonate="true" 
 password="*****" /> 

Don't Use Universal Data Link (UDL) Files

The reason is so simple, UDL files cannot be encrypted.

Preventing Injection Attacks with Connection Strings Compiles

A connection string injection attack can occur when dynamic string concatenation is used to build connection strings based on user input. If the user input is not validated and malicious text or characters not escaped, an attacker can potentially access sensitive data or other resources on the server.

To prevent this, here are some compiler classes that you can use:


ConnectionStringBuilder Class
System.Data.SqlClient System.Data.SqlClient.<br />SqlConnectionStringBuilder
System.Data.OleDb System.Data.OleDb.<br />OleDbConnectionStringBuilder
System.Data.Odbc System.Data.Odbc.<br />OdbcConnectionStringBuilder
System.Data.OracleClient System.Data.OracleClient.<br />OracleConnectionStringBuilder

Here is an example, that shows how to use SqlConnectionStringBuilder.

Dim builder As New System.Data.SqlClient.SqlConnectionStringBuilder
builder("Data Source") = "(local)"
builder("Integrated Security") = True
builder("Initial Catalog") = "Mybd;NewValue=Bad"

The result is:

data source=(local);Integrated Security=True;
initial catalog=" Mybd;NewValue=Bad" 

Use Persist Security Info=False

The default value for Persist Security Info is false; and you should use this default in all connection strings. Setting Persist Security Info to true or yes allows security-sensitive information, including the user ID and password, to be obtained from a connection after it has been opened. When Persist Security Info is set to false or no, security information is discarded after it is used to open the connection, ensuring that an untrusted source does not have access to security-sensitive information.

Create Connection Strings from Configuration Files

If we know some connection parameters, we can use config files to store and recover these parameters for building a complete string connection.


Private Sub BuildConnectionString(ByVal dataSource As String, _
    ByVal userName As String, ByVal userPassword As String)

    ' Retrieve the partial connection string named databaseConnection
    ' from the application's app.config or web.config file.
    Dim settings As ConnectionStringSettings = _

    If Not settings Is Nothing Then
        ' Retrieve the partial connection string.
        Dim connectString As String = settings.ConnectionString
        Console.WriteLine("Original: {0}", connectString)

        ' Create a new SqlConnectionStringBuilder based on the
        ' partial connection string retrieved from the config file.
        Dim builder As New SqlConnectionStringBuilder(connectString)

        ' Supply the additional values.
        builder.DataSource = dataSource
        builder.UserID = userName
        builder.Password = userPassword

        Console.WriteLine("Modified: {0}", builder.ConnectionString)
    End If
End Sub

Encrypt Configuration Files

You can also store connection strings in configuration files, which eliminates the need to embed them in your application's code. Configuration files are standard XML files for which the .NET Framework has defined a common set of elements.

In the next articles, I will write about users, database and more...


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Systems Engineer
Costa Rica Costa Rica
No Biography provided

Comments and Discussions

Generalgood article Pin
Donsw31-May-10 17:34
MemberDonsw31-May-10 17:34 
well done
My Recent Article : CDC - Change Data Capture

GeneralRe: good article Pin
jaullo17-Aug-10 15:20
Memberjaullo17-Aug-10 15:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.