Summary of this unit
Data Access is a process of accessing a database from an ASP.NET web application using a few ADO.NET data providers.
The database is the primary goal of application-level attacks. An attacker uses application-level attacks to use the defects in your data access code to get unauthorized database access. If all other attacked areas are turned off, the front door-port 80 of the application will become an attacker to steal, manipulate and destroy data path selection.
This unit illustrates how to build a secure data access code and avoid common defects and mistakes. This unit proposes a range of countermeasures and prevention technology that can be used to mitigate some of the biggest threats related to data access in data access code.
Back to top
aims
Use this unit:
• Design, build and deploy secure data access code. • Access to unauthorized caller or code using code access security and role-based security restrictions. • Safely authenticate users. • Prevent SQL injection attacks. • Protect database connection strings. • Use encrypted protection to store data in the database. • Protect the cross-network to the database and data from the database. • Safely store the password (with the hash value with the SALT value) in the database. • Implement security exception handling. • Understand how to use code access secure to allow moderate trusted web applications to use OLE DB, ORACLE, and ODBC data providers (these data providers require full trust). • Understand the countermeasure that can be used to deal with common data access threats (including SQL injection, leakage, sensitive application data, leakage, unauthorized access, and network listening).
Back to top
Scope of application
This unit applies to the following products and technologies:
• Microsoft® Windows® Server 2000 and Windows ServerTM 2003 operating system • Microsoft .NET Framework 1.1 and ASP.NET 1.1 • Microsoft SQL ServerTM
Back to top
How to use this unit
To make full use of this unit, please read the following units before or in this unit:
• Read the "Threat and Countermeasures" unit. This unit enables you to have a broader and deeper understanding of the potential threats and countermeasures faced by web applications. • Read the Security Web Application Design Guide unit. In this unit, you will understand the architecture, design challenges and guidance of building security solutions. • Read the Protection Database Server unit. Read this unit understand how to protect the database server. • Read the "Build Safety Jack" unit. In this unit, the guidance and recommendation practice of building security assemblies and development security managed codes should also be applied to data access codes. • Use the evaluation unit. To review the security of data accesses at different stages of the product survival, refer to the Web Services section of the following units: "Security Architecture and Design Review", "Security Code Review" and "Safety Deployment Review". • Use the check-in table. "Nuclear Table: Security of Protection Data Access" includes an easy reference checklist. This task-based verification table can be used as a summary of recommending practices in this unit.
Note that in the current version of .NET Framework (1.1), only the ADO.NET SQL Server Data Access provider supports some trust caller, which can be securely used for partial trust Web applications. OLE DB, ORACLE and ODBC ADO.NET data providers require full trust.
This page
This unit profile target applicable range How to use this unit threat and countermeasure design precautions Enter the verification SQL injection authentication Authorized configuration management sensitive data exception management to build a secure data access component code Access security precautions Deployment Notes Summary Other resources
Back to top
Threat and countermeasure
To build a secure data access code, you need to understand what the threat is, how is a common defect in the data access code, and how to use the corresponding countermeasures to reduce risks.
The greatest threat to data access code is:
• SQL injection • Leakage of configuration data • Leakage of sensitive application data • Leakage of database architecture and connection details • Unauthorized access • Network Listening Figure 1 shows these biggest threats.
Figure 1. Threats and attacks on data access code
SQL injection
SQL injection attacks use defective data access code to enable attackers to execute arbitrary commands in the database. If the application uses unconstrained accounts in the database, the threat is greater because this will give an attacker's greater freedom to perform queries and commands.
defect
Common defects that make your data access code are susceptible to SQL injection attacks include:
• Vulnerable input verification • Dynamically constructed SQL statements without using type security parameters • Database logins for high privileges
Countermeasure
Respond to SQL injection, to ensure:
• Constraints and purify input data. • Data access is performed using the type-safe SQL parameters. These parameters can be used to store procedures or dynamically constructed SQL command strings. The parameter executes the type and length check, and ensure that the injected code is considered text data, not the executable database statement. • Use accounts with database restricted permissions. Ideally, you should only grant execution permissions to select those stored procedures in the database, and do not provide direct table access.
Configuring data leakage
The most sensitive configuration data used by data access code is a database connection string. If there is a security problem, the result will include the user name and password, and the result will be more serious.
defect
The following defects will increase security risks associated with security issues:
• Using SQL authentication, this requires that the credentials are specified in the connection string • Embed a connection string in the code • Ming text connection string in the configuration file • Uncrypted connection string
Countermeasure
To prevent the leakage of configuration data:
• Use Windows authentication so that the connection string will not contain credentials. • Encrypt the connection string and restriction access to encrypted data.
Sensitive application data leakage
Many applications store sensitive data, such as customer credit card numbers. It is very important to protect the privacy and integrity of such data.
defect
Coding practices that can lead to sensitive application data leakage include:
• Unprypted storage data • Vulnerable authorization • Fragile encryption
Countermeasure
To prevent the leakage of sensitive application data:
• Protect data using rugged encryption mechanisms. • Authorize each caller before performing data access, so users can only see their own data.
Leakage of database architecture and connection details
If your code returns exception details, malicious users can use this information to attack servers. The exceptions in the data access code expose sensitive information, such as the details of the database architecture, the nature of the data store and the SQL code segment.
defect
The following defects may cause information leakage:
• Improper handling • Vulnerable ASP.NET configurations will return unprocessed exception details to the client
Countermeasure
To prevent these leaks:
• Capture, recording, and processing data accesses in your data access code. • Returns a general error message to the call. This requires the appropriate configuration of the
Unauthorized access
If the authorization is not proper, the user may be able to see the data of another user and may access other limited data.
defect
Practices that may allow unauthorized access include:
• Lack of licenses in data access code, will provide unlimited access • Database accounts for privileges
Countermeasure
To prevent unauthorized access:
• Authorize the support for the authorization of the authorized file using principal permission. • Use the code access security to authorize the calling code. • Use restricted permissions to restrict the application login database and prevent the table from accessing the table directly.
Network listening
The deployment architecture of most applications includes a physical isolation layer that separates the data access code from the database server. Therefore, sensitive data such as an application-specific data or database login credentials must be protected to prevent network listening.
defect
The following practices increase the possibility of network listening: • Transfer clear document credentials on the network during the SQL authentication process • Send to the database server and undated sensitive application data from the database server
Countermeasure
In order to limit the network listening defect, it should be:
• Use Windows authentication to avoid sending credentials on the network. • Install server certificates on the database server. This will automatically encrypt the SQL credentials transmitted on the network. • Use SSL connection to protect sensitive application data between web servers and database servers. This requires the use of a database server certificate. • Use an IPSec encryption channel between the Web and the Database Server.
Back to top
Design consideration
Many important issues need to be considered when designing the code. Important note is:
• Use Windows authentication. • Use the minimum privilege account. • Use the stored procedure. • Protect sensitive data in the storage area. • Use different data access assemblies.
Using Windows Authentication
Ideally, your design should use Windows authentication to get more security advantages. With Windows authentication, you don't have to store a database connection string with embedded credentials. The credentials will not be delivered on the network, and they can benefit from a secure account and password management policy. But you do need a careful consideration which account should be used to connect to SQL Server via Windows authentication.
For more information, see "Authentication" behind this unit.
Use the minimum privilege account
Your application should use the minimum privilege account, which only has limited permissions of the database. Make sure the application logs in the database through the appropriate authorization and restrictions. For more information, see "Authorization" behind this unit.
If your account is damaged or injecting malicious code, using the minimum privileged account can reduce risk and limit possible damage. If SQL injection occurs, the command will execute in the security context defined in the application login and is limited by the database related to the login. If you are connected using an excessive account (for example, as a member of the SQL Server Sysadmin role), the attacker can perform any operations on any database on the server. This includes inserting, updating, and deleting data, deleting tables, and executing an operating system command.
Important Do not use the SA account or any account that belongs to SQL Server Sysadmin or DB_OWNER role.
Use stored procedures
The stored procedure has its advantage in performance, maintainability, and security. Data access should be performed using the parameterized stored procedures whenever possible. Its security advantages include:
• You can limit your application to log in to make it only permission to perform the specified stored procedure. Granting direct table access is unnecessary. This helps to reduce the risk of SQL injection attacks. • Perform length and type check for all input data passed to the stored procedure. Moreover, the parameter cannot be considered an executable code. Similarly, this also reduces the risk of SQL injection attacks.
If you cannot use a parameterized stored procedure for some reason, you need to dynamically construct the SQL statement, you should also use the type of parameters and parameter placeholders to ensure that the input data is performed and the input data.
Protect sensitive data in the storage area
Determine which data stored needs to guarantee privacy and integrity. If you store your password in the database, just for verification, you can consider using a one-way hash. Even if the password table is damaged, the hash value cannot be used to obtain a plain text password.
If you need to store data provided by a sensitive user such as a credit card number, you should use a solid symmetric encryption algorithm (such as triple DES (3DES) encrypted data. You can use the Win32 Data Protection API (DPAPI) Encryption 3DES encryption key, and store the encrypted key in only an administrator with a restricted ACL and the registry key available for your application process account.
Why not use DPAPI?
Although it is recommended to encrypt the DPAPI to encrypt the connection string and other ciphertexts (such as account credentials) that can be manually restored and reconstructed when the machine is encountered, it is not suitable for the data such as the credit card number. This is because there is a reducible problem (if the key is lost, there is no way to restore the encrypted data) and the Web farm problem. Instead, you should use a symmetric encryption algorithm (such as 3DES) and use the DPAPI encryption key. The main issues that make DPAPIs are not suitable for storage sensitive data in the database can be summarized as follows:
• If DPAPI is used for machine keys, you pass CRYPTPROTECT_LOCAL_MACHINE to the CryptProtectData and CryptUnProtectData functions, the machine account will generate an encryption key. This means that each server in the web farm has different keys, which prevents one server from accessing another server encrypted data. Similarly, if the web server machine is destroyed, the key is lost, the encrypted data will not be restored from the database. • If you use a machine key mode, any user on this computer can decrypt data (unless you also use other encryption mechanisms). • If you use the DPAPI for the user key and use the local user account, each local account on each web server has a different security identifier (SID), and generates different keys, which prevents one The server can access the data encrypted by another server. • If you use the DPAPI for the user key and use a cross-machine roaming user profile in the Web farm, all data will share the same encryption / decryption key. However, if the domain controller responsible for roaming the user profile account is destroyed, the user account with the same SID will not be recreated, and you cannot restore encrypted data from the database. Similarly, use roaming user profiles, if someone wants to retrieve data, if an attacker can run code with a specific user account, the data can be decrypted on any machine in the network. This increases potential attack, we do not recommend this.
Use different data access assemblies
If you can choose, don't put the data access logic directly in the ASP.NET page or code hidden file. Place data access logic in different assemblies and implements a logical data access layer (independent of application business logic and representation logic) has the advantages of security, reuse, and maintenance.
From a security perspective, you can:
• Use a strong name for the assembly, which provides tamper-proof function. • It is important to use sandbox protection to isolate your data access code, which is important when the code needs to support partial trust caller (for example, partial trust web applications). • Use data access methods and classes, these methods and classes use code identification rights requests to authorize the calling codes.
In order to perform a depth prevention, the use of principal authority is performed in your business component, and the code to be invoked to call your data access logic is authorized using code identification rights, as shown in Figure 2.
Figure 2. Separation of the layer, business layer and data access layer
For more information on authorized data access code, see the "Authorization" section behind this unit.
Back to top
Enter verification
In addition to ensuring that your database maintains valid and consistent data business needs, you must also verify it before submitting data to the database to prevent SQL injection. If your data access code is received from other components from the current trust boundary, and known data has been verified (for example, verify via an ASP.NET web page or business component), then your data access code can Ignore further data verification. However, be sure to use SQL parameters in the data access code. These parameters will verify the type and length of the input parameters. The next section will discuss the use of SQL parameters. Back to top
SQL injection
SQL injection attacks may happen when the application uses an input to build a dynamic SQL statement to access the database. SQL injection attacks may also occur when code uses some stored procedures (incoming parameters with unfilled user input). SQL injection may enable attackers to enable commands in the database using the application. If the application connects the database to the database, the problem will be more serious.
Note Traditional security measures, such as SSL and IPSec, do not prevent SQL injection attacks.
Prevent SQL injection
You can use the following countermeasures to prevent SQL injection attacks:
• Constraint input. • Use the type of SQL parameters.
Constraint input
Verify the type, length, format, and range of the input. If the input cannot be a value, it will not accept the value. Consider where the input is from. If it comes from a trusted source known to perform a comprehensive input verification, you can choose to ignore data verification in the data access code. If the data comes from an untrusted source or in order to perform a depth prevention, your data access method and component should verify the input.
Use type secure SQL parameters
The Parameters collection in SQL provides type check and length verification. If you use the parameters collection, the input will be treated as text value, and SQL does not treat it as an executable code. Another benefit using the parameters collection is that you can implement types and length checks. If the value is out of the range, it will trigger an exception. This is a good example of depth prevention.
Important SSL does not protect you from SQL injection attacks. Any application for accessing the database, if there is no correct input verification and appropriate data access technology, it is easy to be injected into the SQL injection attack.
Use stored procedures as much as possible and should be called through Parameters.
Use the parameter collection during the stored procedure
The following code snippet illustrates the use of Parameters collection:
Sqldataadapter myCommand = New SqldataAdapter ("AuthorLogin", Conn;
Mycommand.selectcommand.commandtype = commandtype.storedProcedure;
SQLParameter Parm = mycommand.selectcommand.parameters.add (
"@au_id", sqldbtype.varchar, 11);
PARM.VALUE = Login.Text;
In this case, the @ au_id parameter will be treated as a text value instead of an executable code. Similarly, the parameters will be checked. In the example above, the input value cannot be longer than 11 characters. An exception will occur if the data is not complied with the type or length defined by the parameter.
Note that the use of stored procedures does not necessarily prevent SQL injection. It is important to use parameters during the stored procedure. If you do not use parameters, your stored procedure is easily injected into the SQL injection attack if you use unfilled input. For example, there is a problem with the following code snippet:
Sqldataadapter mycommand = new sqldataadapter ("loginstoredprocedure '" login.text "'", conn);
Important If you use a stored procedure, you must use the parameters.
Use the parameter set in dynamic SQL
If you cannot use a stored procedure, you can also use parameters, as shown in the following code snippet:
Sqldataadapter mycommand = new sqldataadapter
"SELECT AU_LNAME, AU_FNAME FROM AUTHORS WHERE AU_ID = @AU_ID", CONN);
SQLParameter Parm = mycommand.selectcommand.parameters.add ("@ au_id",
Sqldbtype.varchar, 11);
PARM.VALUE = Login.Text;
Use parameter batch
There is a common misunderstanding, if you connect a few SQL statements, send a group of statements to the server in a round trip, is an unable to use parameters. However, if you can affirm the name of the parameter does not repeat, you can use this technology. This can be easily achieved by adding a number or some other unique value to each parameter name or some unique values in the process of SQL text.
Use filter routines
Another way to prevent SQL injection attacks is to develop a filter routine that adds an escap in characters that have special meaning of SQL, such as an apostrophe character. The following code snippet illustrates the filter routine that adds an escar:
Private string safESqlliteral (String Inputsql)
{
Return INPUTSQL.REPLACE ("'", "' '");
}
The routine will have problems (such as this problem above) and the reason why the attacker can use ASCII hexadecimal by bypass your check. However, you should also filter your input, which is part of the depth prevention strategy.
Note Don't rely on the filtering of the input.
Please note that if you use the Like clause, wildcards still need to use esters. The following code snippet shows this technology:
S = S.Replace ("[", "[]");
S = S.Replace ("%", "[%]");
S = S.Replace ("_", "[_]");
Back to top
Authentication
When your application is connected to the SQL Server database, you can select Windows Authentication or SQL authentication. Windows authentication is higher. If you have to use SQL authentication (this may be used to use many different accounts to connect to the database, you should take more steps to reduce additional risks as much as possible.
Note Use Logonuser to create an analog tag, requiring the powerful "ACT AS Part of Operating System" privilege on Microsoft Windows 2000, so this way should be avoided.
Consider the following recommended practice:
• Use Windows authentication. • Protect the credentials for SQL authentication. • Connect using the minimum privilege account.
Using Windows Authentication
Windows authentication does not send credentials across the network. If the web application uses Windows authentication, in most cases, you should use the service account or process account (such as an ASPNET account) to connect to the database. Windows and SQL Server must recognize the accounts used on the database server. Accounts must be granted to log in to SQL Server, and log in to access the relevant permissions for accessing the database. When using Windows authentication, a trusted connection should be used. The following code snippet illustrates a typical connection string using Windows authentication.
The following example uses the ADO.NET data provider of SQL Server:
SqlConnection Pubsconn = New SQLCONNECTION
"Server = dbserver; database = pubs; integrated security = SSPI;");
The following example uses the ADO.NET data provider of the OLE DB data source:
OLEDBCONNECTION PUBSCONN = New OLEDBCONNECTION
"Provider = sqloledb; data source = dbserver; integrated security = SSPI;"
"InTIAL CATALOG = Northwind");
Protect the credentials of SQL authentication
If you must use SQL authentication, make sure that the credentials will not be sent across network in a clear text, and the database connection string should be encrypted because of the credentials.
In order to enable SQL Server to automatically encrypt the credentials transmitted by the network, install the server certificate on the database server. In addition, all IPSec encryption channels between web servers and database servers protect all traffic sent to the database server and from the database server. To protect the connection string, you can use DPAPI. For more information, see "Protect Connection Strings" in the Configuration Management section later in this unit.
Connection with the minimum privilege account
Your application should connect to the database by using the minimum privileged account. If you use a Windows authentication connection, from the operating system's point of view, the Windows account should have the lowest privilege, and there should only access the resilial privileges and restrictions on Windows resources. In addition, whether you use Windows Authentication or SQL authentication, the appropriate SQL Server login should be restricted through the permissions in the database.
For more information on how to create a minimum privilege database account and using Windows authentication, please refer to the "Data Access" section in the Security of the ASP.NET application security "unit .
Back to top
Authorize
The authorization process determines whether the user can retrieve and operate specific data. There are two ways to authorize: Data Access Codes can use authorization to determine if the requested operation is performed, and the database can perform the ability to restrict the SQL login used by the application.
If the authorization is not proper, the user may be able to see the data of another user, and unauthorized users may access the restricted data. In response to these threats should:
• Restrict unauthorized calls. • Limit unauthorized code. • Restrict the application in the database.
Figure 3 summarizes the authorization points and techniques that should be used.
Figure 3. Data Access Authorization, Access, and Database
Note how the data access code uses the right to authorize the caller user or the calling code. The code identification requirement is a feature of the .NET code access security.
To authorize the application in the database, log in with the minimum privilege SQL server, which only has permission to perform the selected stored procedure. Unless there are special reasons, the application will not be able to obtain an authorization to perform creation, retrieval, update, destroy / delete any table. Note The stored procedure runs under the security context of the database system. Although the logical operation of the application can be restrained by granting a special stored procedure permission, you cannot restrain the results of the operations performed by the stored procedure. The stored procedure is a trusted code. The interface of the stored procedure must be protected using database permissions.
Limit unauthorized caller
The code should authorize it according to the role or identifier before the user is connected. Role checks are typically used in the business logic of the application, but if you don't have clearly distinguish between business and data access logic, you should use the subject permission requirements on the way to access the database.
The following attributes ensure that only users of the Manager role can call the DisplayCustomerinfo method:
[PrincipalPermissionatTribute (SecurityAction.Demand, role = "manager")]]
Public Void DisplayCustomerInfo (int CustId)
{
}
If you need a finer authorization granularity, and you need to perform role-based logic in the data access method, you should use commandable principal permission requirements or explicit roles, as shown in the following code segment:
Using system.security;
Using system.security.permissions;
Public Void DisplayCustomerInfo (int CustId)
{
Try
{
// Imperative Principal Permission Role Check To Verify That The Caller
// is a manager
PrincipalPermission PrincipalPerM = New PrincipalPerMission
NULL, "Manager");
// Code That Follows Is Only Executed if The Caller is a member
// of the "manager" role
}
Catch (SecurityException EX)
{
.
}
}
The following code snippet uses an explicit, programming-implemented role to ensure that the caller is a member of the Manager role:
Public Void DisplayCustomerInfo (int CustId)
{
IF (! thread.currentprincipal.isinrole ("manager"))
{
.
}
}
Limit unauthorized code
Access security by using the .NET Framework code - more specific is the code identification requirements, you can limit the assembly of accessible data access classes and methods.
For example, if you only want your company or a specific development unit to write your data access components, you should use a strongNameIdentityPermission and ask the calling party to have a strong name of the specified public key, as shown in the following code segment. :
Using system.security.permissions;
.
[StrongNameIdentityPermission (SecurityAction.LinkDemand,
Publickey = "002 ... 4c6")]]
Public void getCustomerInfo (int CustId)
{
}
To extract the text representation of a given assembly public key, you can use the following command:
Sn -tp assembly.dll
Note The "T" of the uppercase should be used in the -tp switch. Because the web application assembly is dynamically compiled, you cannot use the strong names of these assemblies. This will make limited only specific web applications can use data access assemblies. The best way is to develop a custom privilege and require the data to access the components. Fully trusted Web applications (or any completely trusted code) to invoke your components. Part of the trust code only when it is granted custom privileges, you can invoke your data access components.
Custom permission implementations, see "How to Create Custom Encryption Permissions" in this guide "How to ...".
Limiting an application in a database
A better way is to create a SQL Server login supply for the Windows account to connect to the database. The SQL Server login is then mapped to database users in the database. Place a database user in a user-defined database role and grant the role permissions. Ideally, you should only grant the execution of access to the stored procedures used by the role application.
For more information on how to configure this approach, see "Configuring" Configuring Data Access to your ASP.NET Application "unit"
Back to top
Configuration management
The database connection string is the main configuration management focus of the data access code. You should carefully consider where to store these strings, how to protect, especially when they contain credentials. To improve your encryption management security:
• Use Windows authentication. • Protect your connection string. • Protect the UDL file with a restricted ACL.
Use Window Authentication
When you use Windows authentication, the system will manage your credentials, and credentials will not be delivered on the network. You should also avoid embed usernames and passwords into the connection string.
Protect connection string
If you need to use SQL authentication, the username and password will be included in the connection. If the attacker uses the source code leak defects on the web server or try to log in to the server, they will retrieve the connection string. Similarly, people who can log in to the server can view them. So encryption protection connection strings should be used.
Encryption connection string
Connect the connection string by using the DPAPI. Encrypted by DPAPI, an encryption key management problem can be avoided because the encryption key is managed by the platform, and is bound to a specific computer or a Windows user account. To use DPAPI, you must call the Win32 DPAPI function via P / Invoke.
For more information on how to build a managed packaging class, see "How to: Create a DPAPI Library", located in "Microsoft Patterns & Practices, Built Security ASP.NET Web Applications: Authentication, Authorization, and Security Communication" How to ... "section, the URL is: http://msdn.microsoft.com/library/default.asp? Url = / library / en-usetlpmsdn.asp.
Safely store encrypted connection strings
The encrypted connection string can be placed in the registry or in the web.config or a machine.config file. If you use the registry key under HKEY_LOCAL_MACHINE, you can apply the following ACLs on this item:
Administrators: Full Control
Process Account: Read
Note The process account is determined by the process of the data access assembly runs in it. This is usually an ASP.NET process or a corporate service server process (if your solution uses a corporate service intermediate layer).
In addition, you can consider using HKEY_CURRENT_USER, which provides limited access. For more information, see the Registry section in the "Build a secure assembly" unit.
Note If you use the Microsoft Visual Studio_ .NET Database Connection Wizard, the connection string stores in the form of a web application code hide file or a web.config file in the form of a plaintext property value. These two ways should be avoided. Although security may be worse than using restricted registry items, you may still need to store encrypted strings in web.config to make it easier to deploy. In this case, custom
...
To access encrypted text from
Using system.configuration;
Private static string getConnectionstring ()
{
Return ConfigurationSettings.AppSettings ["Connectionstring"];
}
Don't set PERSIST Security Info to 'True' or 'Yes'
When you include the Persist Security Info property in the connection string, the Connectionstring property will make the password from the connection string before returning to the user. Default Setting false (equivalent to ignoring the Persist Security Info property) will discard this information after connecting the database.
Protect UDL files with restricted ACL
If your application uses an external universal data link (UDL) file in the ADO.NET OLE DB managed data provider, NTFS permission should be used to restrict access. Use the following limited ACL:
Administrators: Full Control
Process Account: Read
Note The UDL file is not encrypted. The safer method is to use the DPAPI encryption connection string and store it in a restricted registry key.
Back to top
Sensitive data
Many web applications store a form or another form of sensitive data in a database. If an attacker is trying to perform a query for your database, it is necessary to properly encrypt any sensitive data items (such as credit card numbers).
• If you need to store sensitive data, you should encrypt it. • Protect sensitive data across network transmission. • Store the password aquary value of the SALT value.
If you need to store sensitive data, you should encrypt it.
If possible, try to avoid storing sensitive data. If you have to store sensitive data, you should encrypt this data.
Use 3DES encryption
In order to store sensitive data (such as credit card numbers) in the database, a solid symmetric encryption algorithm should be used, such as 3Des.
Enable 3DES encryption during development
1. Generate a rugged (192-bit, 24-byte) encryption key using the RNGCryptoServiceProvider class. 2. Back up the encryption key and store backups in a very secure location. 3. Use the DPAPI encryption key and store it in a registry key. Use the following ACL to protect the registry key: Administrators: Full ControlProcess Account (for example aspnet): Read
Store encrypted data in the database at runtime
1. Get the data to be encrypted. 2. Retrieve encrypted keys from the registry. 3. Use the DPAPI to decrypt the key. 4. Use the TriplePToServiceProvider class with an encryption key encrypted data. 5. Store encrypted data in the database.
Decrypt the encrypted ciphertext during operation
1. Retrieve encrypted data from the database. 2. Retrieve encrypted keys from the registry. 3. Use the DPAPI to decrypt the key. 4. Decrypt data using the TripleDescryptoServiceProvider class.
Through this process, if the DPAPI account used to encrypt the key is destroyed, the backup of the 3DES key will be retrieved from the backup location, and DPAPI encryption is used under the new account. The new encryption key can be stored in the registry, and the data in the database can still decrypt.
For more information on creating hosting DPAPI libraries, see "How to Create a DPAPI Library", located in "Microsoft Patterns & Practices Volume I, Building Secure ASP.NET Web Applications: Authentication, Authorization, And Secure Communication": URLs: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/secnetlpmsdn.asp.
Protect sensitive data across network transmission
Send it to the database server through the network and sensitive data from the database server, it may contain an application-specific data or database login credentials. In order to ensure the privacy and integrity of data on the network, the platform-level solution (such as the security data center of the IPSec encrypted communication channel) is used, and the application is either configured to establish an SSL database connection. The latter manner needs to install the server certificate on the database server.
For more information on using SSL and IPsec, see "Microsoft Patterns & Practices Volume I, Building a Security ASP.NET Web App: Authentication, Authorization, and Security Communications" (How to ... " Part "How to: Use IPSec between two servers" and "how to: use SSL protection and communication with SQL Server 2000".
Password Ratings value of the SALT value
If you need to implement a user storage area containing the username and password, you do not store your password in a clear text or encrypted format. Do not store your password, and should store the non-reversible values of SALT to reduce the risk of dictionary attacks.
Note The SALT value is a strongly encrypted random number.
Create a SALT value
The following code illustrates how to generate a SALT value by using the RNGCryptoServiceProvider class provided in the System.Security.cryptography namespace.
Public Static String Createsalt (int size)
{
RNGCRYPTOSERVICEPROVIDER RNG = New RNGCRYPTOSERVICEPROVIDER (); BYTE [] BUFF = New Byte [Size];
RNG.GETBYTES (BUFF);
Return Convert.TOBASE64STRING (BUFF);
}
Create (with SALT value) hash value
The following code snippet describes how to generate a column value from the provided password and the SALT value.
Public Static String CreatePasswordHash (String PWD, String Salt)
{
String SaltandPwd = String.concat (PWD, SALT);
String hashedpwd =
FormsAuthentication.hashPasswordforstoringInfigfile (
SaltandPWD, "SHA1");
Return Hashedpwd;
}
More information
For more information on the user storage area of the password, a password, a memory band, please refer to "How to: Combined with Form Authentication and SQL Server 2000", located in "Microsoft Patterns & Practices I V Volute, Build Safety) ASP.NET Web Applications: Authentication, Authorization and Security Communication (URL: http://msdn.microsoft.com/library/default.asp? Url = / library / en-us / dnnetsec / html / secnetlpmsdn. "How to ..." in the ASP).
Back to top
Abnormal management
Configuration errors, errors in the code, or malicious inputs will result in an abnormal situation. If there is no appropriate exception management, these situations expose sensitive information such as location and data source nature and important connection details. The following recommendation practices apply to data access code:
• Capture and record ADO.NET exceptions. • Make sure the database connection is always turned off. • Use a general error page in the ASP.NET application.
Capture and record ADO.NET exception
Place the data access code in the TRY / CATCH block and processes an exception. When writing ADO.NET data access code, the exception type generated by ADO.NET depends on the data provider. E.g:
• SQL Server .NET Framework data provider will generate SQLEXCEPTIONS. • OLE DB .NET Framework data provider will generate OLEDBEXCEPTIONS. • The ODBC .NET Framework data provider will generate ODBCEXCEPTIONS.
Capture exception
The following code uses the SQL Server .NET Framework data provider and explains how to capture the exception of the SQLEXCeption type.
Try
{
// Data Access Code
}
Catch (SQLException Sqlex) // More Specific
{
}
Catch (Exception EX) // Less Specific
{
}
Logging abnormal
You should also record the details from the SQLEXCEPTION class. This class discloses an attribute that contains exception conditions. This includes an illustrative message property, a unique Number property that uniquely identifies the error type, and a State property containing additional information. The State property is usually used to indicate a certain appearance of a particular error condition. For example, if the stored procedure appears in more than one row, the State property indicates the specific time. Finally, the ERRORS collection contains a SQLError object that provides detailed SQL Server error messages.
The following code snippet describes how to handle SQL Server error by using SQL Server .NET Framework data provider: use system.data;
Using system.data.sqlclient;
Using system.diagnostics;
//Method Exposed by a data access layer (dal) Component
Public String getProductName (int Productid)
{
SqlConnection conn = new SQLCONNECTION
"Server = (local); Integrated Security = SSPI; Database = Products");
// Enclose All Data Access Code forin a Try Block
Try
{
Cn.open ();
SQLCommand cmd = new SQLCommand ("LookuppproductName", Conn;
cmd.commandtype = commandtype.storedProcedure;
Cmd.Parameters.Add ("@ ProductID", ProductId;
Sqlparameter parampn =
CMD.Parameters.Add ("@ ProductName", SqldbType.varchar, 40);
PARAMPN.DIRECTION = parameterDirection.output;
cmd.executenonquery ();
// the finally code is executed before the method Returns
Return parampn.value.tostring ();
}
Catch (SQLEXCEPTION SQLEX)
{
// Handle Data Access Exception Condition
// log specific Exception Details
LOGEXCEPTION (SQLEX);
// Wrap the Current Exception in A More Relevant
// Outer Exception and Re-Throw the New Exception
Throw new Exception
"Failed to Retrieve Product Details for Product ID:"
ProductId.toString (), SQLEX);
}
Finally
{
Conn.close (); // EnsuRES Connection IS Closed
}
}
// Helper Routine That Logs Sqlexception Details To The
// Application Event Log
Private void logxception (SQLEXCEPTION SQLEX)
{
Eventlog el = new eventlog ();
El.Source = "CustomApplog";
String strmessage;
StrMessage = "Exception Number:" SQLEX.NUMBER
"(" SQLEX.Message ") HAS Occurred";
El.writentry (StrMessage);
FOREACH (SQLERROR SQLE IN SQLEX.ERRORS) {
StrMessage = "Message:" SQLE.MESSAGE
"Number:" SQLE.NUMBER
"Procedure:" SQLE.Procedure
"Server:" SQLE.Server
"Source:" Sqle.Source
"State:" Sqle.State
"Severity:" SQLE.CLASS
"LINENUMBER:" SQLE.LINENUMBER;
El.writentry (StrMessage);
}
}
Make sure the database connection is always turned off
It is very important if there is an exception, shut down the database connection and release any other limited resources. Use the Finally block, or the C # using statement to ensure that the connection will be turned off regardless of whether there is any abnormal situation. The above code has explained the use of the Finally block. You can also use the C # using statement as follows:
Using ((SqlConnection Conn = New SqlConnection))))))
{
Cn.open ();
// Connection Will Be Closed if AN Exception Is Generated or if Control Flow
// Leaves the scope of the using statement normally
}
Using a general error page in the ASP.NET application
If your data access code is called by an ASP.NET web application or web service, you should configure the
Set mode = "on" for the product server. You can only use mode = "OFF" when you develop and test software before publishing. If this is not possible, it will result in return to the end user-rich error message, as shown in Figure 4. This information may include a database server name, database name, and connection credentials.
Figure 4. Detailed exception information exposes sensitive data
Figure 4 also shows a number of defects that still exist in data access code near the row of abnormal lines. details as follows:
• The connection string is hardcoded. • Use a high privilege SA account to connect to the database. • The password for the SA account is very fragile. • The SQL command structure is easily subjected to SQL injection attacks, and the input is not verified, and the code does not use the parameterized stored procedure.
Back to top
Build a secure data access component
The following code gives an example implementation of the CheckProductStockLevel method, which can be used to query the number of inventories of a product database. These code illustrate many of the important data access code security features described earlier in this unit.
Using system;
Using system.data;
Using system.data.sqlclient;
Using system.text.regularExpressions;
Using system.collections.specialized;
USING Microsoft.win32;
Using DataProtection; Public Static Int CheckProductStockLevel (String ProductCode)
{
INT quantity = 0;
// (1) Code protected by try / catch block
Try
{
// (2) Input validated with regular expression
// Error Messages Should Be Retrieved from the Resource Assembly to Help
// localization. The localization code is omitted for the sake of break.
IF (correcode, "^ [A-ZA-Z0-9] {12} $") == false)
Throw new ArgumentException ("Invalid Product Code");
// (3) The Using Statement Ensures That The Connection is Closed
Using (SqlConnection Conn = New SqlConnection (getConnectionString ()))
{
// (4) Use of parameterized stored procedures is a counterMeasure for
// SQL INJECTION Attacks
SQLCommand cmd = new SQLCommand ("spCheckproduct", conn);
cmd.commandtype = commandtype.storedProcedure;
// Parameters Are Type Checked
SQLParameter PARM =
cmd.Parameters.Add ("@ productcode",
Sqldbtype.varchar, 12);
PARM.VALUE = ProductCode;
// define the output parameter
Sqlparameter Retparm = cmd.parameters.add ("@ Quantity", SqldbType.InT);
Retparm.direction = parameterdirection.output;
Cn.open ();
cmd.executenonquery ();
Quantity = (int) retPARM.VALUE;
}
}
Catch (SQLEXCEPTION SQLEX)
{
// (5) Full Exception Details Are Logged. Generic (Safe) Error Message
// is thrown back to the caller based on the SQL Error Code
// log and error Identification Code Has Been Omitted for Clarity
Throw New Exception ("Error Processing Request");
}
Catch (Exception EX)
{
// log full exception detail
Throw New Exception ("Error Processing Request");
}
Return quantity;
}
// (6) Encrypted Database Connection String is helde in the registrationPrivate Static String getConnectionstring ()
{
// Retrieve the cipher text from the registry; The process instance
// Granted Read Access by The Key's ACL
String EncryptedString = (string) registry.localmachine.Opensubkey
@ "Software / ORDERPROCESSING /")
.GetValue ("Connectionstring");
// use the managed dPapi helper library to decrypt the string
DataProtector DP = New DataProtector (DataProtector.Store.Use_machine_store);
Byte [] DataTodecrypt = Convert.FromBase64String (EncryptedString);
Return encoding.ascii.getstring (DP.Decrypt (Datatodecrypt, Null);
}
The code given above illustrates the following security features (identifies with the number of comment lines).
1. Data Access Code is placed in a TRY / CATCH block. This is critical to preventing system level information from returning to the calling party when an abnormality occurs. The caller ASP.NET web application or web service may deal with an exception and return a suitable general error message to the client, but the data access code is not dependent on. 2. Verify the input using the regular expression. Check the supplied product ID to verify that it only contains characters of A-Z and 0-9, and does not exceed 12 characters. This design is the first countermeasure used to prevent SQL injection attacks. 3. Create a SQLConnection object in the Microsoft Visual C # _ Using statement. This ensures that the connection will be closed in the method whether or not an abnormality occurs. This will reduce the threat of denial of service attacks, which try to use all available database connections. You can get a similar feature by using the Finally block. 4. Use the parameterized stored procedure for data access. This is another countermeasure against the SQL injection attack. 5. Do not return the detailed error message to the client. Record the exception details to diagnose the aid problem. 6. Encrypted database connection string is stored in the registry. One of the most secure storage database connection strings is to use the DPAPI encrypted string and store the encrypted ciphertext in a registry key that is protected with a restricted ACL. (For example, using administrators: Full Control and ASP.NET or Enterprise Services Process Account: Read, depending on which process carries the component.) Note how to retrieve the connection string from the registry, then use the managed DPAPI The auxiliary library decrypts it. This library is provided in "How to create a DPAPI library", this article in "Microsoft Patterns & Practices", building secure ASP.NET web applications: authentication, authorization, and security communication "" how to ... " Part in section.
Back to top
Code access security precautions
All data access is limited by code access security rights. The selected ADO.NET managed data provider determines an accurate requirement. The following table illustrates the permissions granted to the data set to the data set for each ADO.NET data provider.
Table 1 ADO.NET Data Provider Requirements Code Access Safety Permissions ADO.NET Provisions Required Code Access Security SQL Server SqlClientPerMission supports some trust caller, including moderate trust web applications. OLE DB OLEDBPERMISSION * ORACLE ORACLEPERMIRISSION * ODBC ODBCPERMIRISSION ** When writing, the .NET Framework's 1.0 and 1.01, OLE DB, ORACLE, and ODBC providers only support full trust caller. To use these providers from some trusted Web applications, you must use the security code accessible with the sandbox to protect data, which requires a dedicated data access assembly. See "Using Code Access Security" units in ASP.NET using OLE DB data provider with secure codes to protect data access from Sandbox protection data.
If you use the ADO.NET SQL Server Data Provider, you must grant code SQLClientPermission through the code access security policy. Fully and moderate trust Web applications have this permission.
Whether the code is granted SQLClientPermission will determine whether the code can connect to SQL Server. You can also use the right to limit the use of the database connection string. For example, you can force an application to use integration security, or make sure if you use SQL Server security, you will not accept your blank password. If you violate the rules specified by SqlClientPermission, it will cause a safe exception when running.
For more information on how to use SQLClientPermission constraint data access, see the "Data Access" section in the Code Access Security Practice unit.
Back to top
Deployment consideration
Data access components that have been securely designed and developed If they do not deploy in a secure way, there will still be defects and attacked. Common deployment practices are placed on different servers in different servers. Different servers are often separated by internal firewalls, thereby bringing more considerations for more deployment. For developers and administrators, you should pay attention to the following questions:
• Firewall restrictions • Connection string management • Login account configuration • Login audit • Privacy and integrity of network data
Firewall restriction
If you connect SQL Server through a firewall, you should configure a firewall, client, and server. Configure the client by using the SQL Server Client Network Utility to configure the database server by using the Server Network Utility. By default, SQL Server will listen to the TCP port 1433, although this can also be changed. You must open the selected port on the firewall.
Depending on whether the authentication mode of the SQL Server you choose and whether the application uses a distributed transaction, you may need to open another port on the firewall:
• If the application uses Windows authentication to connect SQL Server, you must open a port that supports Kerberos or NTLM authentication. For networks that do not use Active Directory, TCP port 139 is usually necessary for Windows authentication. For more information on port requirements, see the TechNet article "TCP and UDP Port Assignments,", the URL is: http://www.microsoft.com/technet/prodtechnol/windows2000serve/reskit/tcpip/part4/tcpappc.asp, And "Security Considerations for Administrative Authority,", the URL is: http://www.microsoft.com/technet/security/bestprac/bpent/sec2/seconaa.asp • If your application uses a distributed transaction (for example, automation COM Transaction), you may also need to configure the firewall to allow DTC traffic to deliver data between DTC and resource manager (such as SQL Server) between different DTC instances. For complete configuration details, see the "Port" section in the Protection Database Server unit.
Connection string management
Many applications are mainly due to performance reasons, and the connection string is stored in the code. However, the advantage of performance is negligible, and the use of file system cache helps ensure that the connection string is stored in an external file, which provides almost performance. Storing the connection string using an external file is also very good for system management.
In order to improve security, the recommended way is to use the DPAPI encryption connection string. This will be especially important if your connection string contains usernames and passwords. Then, determine where to store the encrypted string. The registry is a safe location, especially when you use HKEY_CURRENT_USER, because access is limited to the process running under the related user account. A alternative to deployment is to store the encrypted string in the web.config file. These two ways discuss in the "Configuration Management" section in this unit.
Login account configuration
It is very important to use a minimum privileged account to connect to the database. This is one of the main techniques to reduce SQL injection attack threats.
As a developer, you must communicate with the database administrator to log in to the exact stored procedure and (possible) table that you need to access. Ideally, you should only allow the application to log in to have execute permissions for a set of restricted storage procedures deployed together with the application.
Use the strong password to connect to SQL or Windows accounts or applications to connect to the database.
See the Recommended Authorization Policy for Application Accounts in the Database in the "Authorization" section in front of this unit.
Login audit
You should configure SQL Server, log in to the login, and possible successful login attempts. Review failed login attempts, very useful for attackers who tried to crack the account password.
For more information on how to configure SQL Server review, see the Protection Database Server unit.
Network data privacy and integrity
If you use SQL authentication to connect SQL Server, you should make sure that login credentials will not be disclosed on the network. Or install the certificate on the database server (this will make SQL Server encrypted credentials), or use the IPSec encryption channel to connect to the database.
It is recommended to use the IPSec or SSL connection database to protect the send to the database and the sensitive application level data from the database. For more information, see the Protection Database Server unit.
Back to top
summary
