Working with Secured Data Sources
To write secure ADO.NET code, you have to understand the security mechanisms available in the underlying data store, or database. This section discusses security topics for SQL Server 2000, SQL Server 2005, and Microsoft Office applications.
Accessing SQL Server Databases
To connect to a SQL Server database, an application must supply authentication information in a connection string. Creating and storing connection string information is covered in Securing Connection Strings. The credentials used for authentication need to be handled differently based on the type of application. For example, in a Windows Forms application, the user can be prompted to supply authentication information, or the user's Windows credentials can be used. However, a Web application often accesses data using credentials supplied by the application itself rather than by the user. Connection options for Web applications are explored in Accessing SQL Server from a Web Application.
Using Integrated Security with SQL Server
When connecting to Microsoft SQL Server, you also have the option to use Integrated Security, which uses the identity of the current active Windows user rather than passing a user ID and password. Using Integrated Security is highly recommended because user credentials are not exposed in the connection string.
Note
ASP.NET developers must consider configuration issues with the ASP.NET process model settings when using Integrated Security. For information on controlling the identity of the current active user in an ASP.NET application, see ASP.NET Impersonation, Authentication and ASP.NET Impersonation, How to: Access SQL Server Using Windows Integrated Security, and ASP.NET Web Site Configuration.
SQL Server Security
SQL Server 2005 security is described in detail in "Security Considerations for SQL Server" in SQL Server 2005 Books Online. SQL Server 2000 security is described in "Managing Security" in SQL Server 2000 Books Online, which provides information about the security tools built into Microsoft SQL Server 2000, as well as tips about planning for security. This topic is also available on MSDN at this location.
Authentication, Roles, and Permissions
There are two aspects to controlling access to data on a SQL Server: authentication and permissions. Authentication determines the identity of the client user or process is authenticated on the server. Permissions determine the level of access to database resources for an authenticated client user or process. These are discussed at "Security Levels" in SQL Server 2000 Books Online and at "Principals" and "Permissions" in SQL Server 2005 Books Online. Both versions of SQL Server use role-based security, supporting both fixed server roles and database roles. Permissions are typically assigned to database roles rather than to individual users; the users in turn belong to one or more roles.
Working with Permissions
Each type of database object has a specific set of permissions associated with it. These permissions can be granted, revoked, or denied. For more information, see "Managing Permissions" in SQL Server 2000 Books Online and "Permissions" in SQL Server 2005 Books Online.
When multiple database objects access each other sequentially, the sequence is known as a chain. SQL Server evaluates permissions for the links in an object chain differently than if it were accessing the objects separately. For more information on how these differences are handled, see "Using Ownership Chains" in SQL Server 2000 Books Online and "Ownership Chains" in SQL Server 2005 Books Online.
Application Roles and Connection Pooling
After a SQL Server application role has been activated by calling the sp_setapprole system stored procedure, the security context of that connection cannot be reset. When pooling is enabled, the connection is returned to the pool and an error occurs when the pooled connection is reused. For more information, see Using Connection Pooling.
SQL Server Security Resources and Best Practices
Security resources for SQL Server can be found on MSDN at this Microsoft Web Site. The SQL Server Security Center on Tech Net is located at this Microsoft Web site and has additional resources for securing your SQL Server databases.
SQL Server 2005
SQL Server 2005 can host the Common Language Runtime (CLR) and developers can create stored procedures, user-defined functions and user-defined types using one of the supported .NET Framework languages, such as C# or Visual Basic. The security model used for CLR integration combines the SQL Server security model, which is user-based, and the CLR security model, which is a code-access security model. This combined approach takes advantage of the most useful features from both the SQL Server and CLR models. These topics are discussed in "Security and CLR Database Objects" and "Security Considerations for Databases and Database Applications" in SQL Server 2005 Books Online. Assembly security is discussed in "Managing Assembly Security."
User-Schema Separation in SQL Server 2005
In SQL Server 2000, there is no distinction between an object's owner and its schema. For example, Marcy, who is also a user in the database, owns an object named Marcy.Table1. Marcy must explicitly grant permissions for other users to access her Table1. Another problem with this approach is that you cannot drop users who also own objects. If Marcy moves on, an administrator must change ownership of the Table1 object before her account can be removed. In SQL Server 2005, you can define a schema that contains objects and then assign users to the schema. For example, you can create a schema named Developers that contains an object named Table1, and the object is referred to as Developers.Table1 in a query. Schemas can be owned by multiple users, roles, or Windows groups and allow for permissions that are more granular. You can also assign a default schema that will be used for name resolution in queries where a schema is not specified. The distinction between users and schema simplifies administration in SQL Server 2005. For more information, see "User-Schema Separation" in SQL Server 2005 Books Online.
Securing Other Data Sources
Server-based data stores offer more robust security features than desktop data stores. For information regarding non-Microsoft server products, consult the applicable product documentation.
Microsoft Access, Microsoft Excel, or even Microsoft Word can act as a data store for an ADO.NET application when security requirements are minimal or non-existent. Non-server based products have security features that can be effective for deterrence, but should not be relied upon for sensitive data. All have fallen victim to security exploits, worms, and viruses, and you should assume that they will continue to be vulnerable to future attacks even when patches and service packs are rigorously applied.
Security Resources for Access and Excel
The following resources supply information on securing applications that rely on Office data.
| Resource | Description |
|---|---|
"Help protect an Access database with user-level security (MDB)" |
Applies to Microsoft Access. Provides instruction for implementing user-level security to protect data. |
"Understanding the role of workgroup information files in Access security" |
Applies to Microsoft Access. Explains the role and relationship of the workgroup information file in Microsoft Access security. |
Applies to Microsoft Access. Answers many Access security questions. The security model has not significantly changed over the years. |
|
Learn about security fundamentals in Microsoft Office programs and what you can do to help protect your computer and documents. Find out about digital signatures, some basic information about macros and viruses, and how to create strong passwords to help protect your documents. |
|
Applies to Microsoft Excel. Discusses the importance of setting and maintaining appropriate macro security levels. |
|
Applies to Microsoft Excel. Discusses features to keep data secure in Microsoft Excel, such as password protection and digital certificates. |
|
Applies to Microsoft Excel. Discusses macro security levels and digital signatures. |
|
Applies to Microsoft Excel. Presents solutions to common problems with security. |