http://www.microsoft.com/china/msdn/library/architecture/rchitecture/Architecturetopic/buildsucapp/BsaaseCmod12.mspxsql script injection attack analysis
When you accept unfilled user input values (see above) in your application, malicious users can use escape characters to add their own commands.
Try to consider such a SQL query, which hopes that the user's input is a social security number, such as 172-32-xxxx, and final query forms are as follows:
SELECT AU_LNAME, AU_FNAME FROM AUTHORS WHERE AU_ID = '172-32-xxxx'
Malicious users can enter the following text into the application input field (for example, text box control).
'; INSERT INTO JOBS (Job_Desc, Min_LVL, Max_LVL) VALUES (' Important Job ", 25, 100) -
An INSERT statement is injected into this example (but any statements allowed by the account for connecting to SQL Server) can be performed). If the account is a member of the sysadmin role (it allows the use of XP_cmdshell's shell command), and the domain account used by SQL Server has access to other network resources, the code is particularly large.
The above command generates the following combined SQL strings:
SELECT AU_LNAME, AU_FNAME FROM AUTHORS WHERE AU_ID = '; INSERT INTO JOBS (Job_Desc, Min_LVL, Max_LVL) Values (' Important Job ", 25, 100) -
In this case, the '(single quotes) character of the malicious input starts to stop the current string in your SQL statement. It only turns off the current statement only in the following cases: The meaning of the mark below is not the continuation mark of the current statement, but the start tag of a new statement.
SELECT AU_LNAME, AU_FNAME AUTHORS WHERE AU_ID = ''
; (Semicolon) character tells SQL you are starting a new statement, and keeping up with the shortcoming of malicious SQL code:
Insert INTO JOBS (Job_Desc, Min_LVL, Max_LVL) Values ('Important Job ", 25, 100)
Note The SQL statement does not necessarily require a semicolon. This is related to the supplier / implementation method, but SQL Server does not need. For example, SQL Server analyzes the following statement as two separate statements:
Select * from mytable delete from myTable
Finally, - (Double Topline) Character Sequence is a SQL Comment Symbol, tells SQL ignores the rest of the text, in this case, ignore the '(single quotes) end character (otherwise it will cause SQL analyzer errors) .
As a result of the above statement, all the text executed by SQL is:
SELECT AU_LNAME, AU_FNAME FROM AUTHORS WHERE AU_ID = '; Insert Into Jobs (Job_Desc, Min_LVL, Max_LVL) Values (' Important Job ', 25, 100) -' Solution
The following methods can be used to call SQL from the application security.
• Use the Parameters collection when building a SQL statement. Sqldataadapter mycommand = new sqldataadapter
"SELECT AU_LNAME, AU_FNAME FROM AUTHORS WHERE AU_ID = @au_id",
MyConnection);
SQLParameter Parm = mycommand.selectcommand.parameters.add (
"@au_id",
Sqldbtype.varchar, 11);
PARM.VALUE = Login.Text;
• Use the parameters collection when calling the stored procedure. // Authorlogin Is A Stored Procedure That Accepts a Parameter Named Login
Sqldataadapter mycommand = new sqldataadapter ("authorlogin", myconnection;
Mycommand.selectcommand.commandtype = commandtype.storedProcedure;
SQLParameter Parm = mycommand.selectcommand.parameters.add (
"@Loginid", sqldbtype.varchar, 11);
PARM.VALUE = Login.Text;
If you use the parameters collection, this input will be processed by text regardless of what the malicious user is included in the input. Another advantage of using the parameters collection is that you can perform types and length checks. The value of the exceeding range triggers anomaly. This is a safe depth defense example. • Filter SQL characters from user input. The following method explains how to ensure that any strings used in simple SQL comparison (equal to, less than, greater than) statements are secure. It is to do this by ensuring that any apostrophe used in a string is added to make this. In the SQL string, two consecutive apostrophes are considered as an apostrophe character instance in the string, not a separator. Private string safESqlliteral (String Inputsql)
{
Return INPUTSQL.REPLACE ("'", "' '");
}
Ã, ...
String SafeSQL = Safesqlliteral (Login.Text);
Sqldataadapter mycommand = new sqldataadapter
"SELECT AU_LNAME, AU_FNAME FROM AUTHORS WHERE AU_ID = '"
Safesql "'", myconnection);
Other best practices
Here is some other measures for reducing security vulnerabilities and to limit possible damage to a certain range:
• Input illegal content is prevented by limiting the size and type of inputs (front-end applications). By limiting the size and type of the input, the possibility of hazard can be greatly reduced. For example, if the database view field is eleven characters, it is enforced to achieve this rule. • Run SQL code with an account with the least permissions. This greatly reduces the possible loss. For example, if the user wants to inject SQL to delete a table of the database, the account used by the SQL connection does not have the corresponding permissions, and the SQL code will fail. This is another reason why the SA account, sysadmin, or db_owner is used for the application's SQL connection. • When an exception error occurs in the SQL code, do not disclose the SQL error caused by the final user. Record the error message and only display user friendly information. This avoids unnecessary details that may be helpful for attackers. Protection mode matching statement
If the input will be used in the string of the Like clause, the character (except the apostrophe) also has a special mode matching meaning.
For example, in the Like clause, the% character indicates that "Match Zero or Multiple Characters" is a text character in the input as a text character without special meanings, and they also need to perform escape. If you do not perform special processing, the query may return an error result, and the non-proportional mode matching characters in the beginning or at the beginning of the string may also destroy the index.
For SQL Server, the following methods should be used to ensure that the input content is valid:
Private string safESqllikeclauseliteral (String Inputsql)
{
// Make the Following Replacements:
// 'becomes''
// [Becomes [[]
/% Becomes [%]
// _ Becomes [_]
String s = INPUTSQL;
S = INPUTSQL.REPLACE ("'", "' '");
S = S.Replace ("[", "[]");
S = S.Replace ("%", "[%]");
S = S.Replace ("_", "[_]");
Return S;
}
---------------------------------- ================ ================== 以下 是 原文: Browse all security guidance theme Microsoft Corporation in this chapter, such as Microsoft_ SQL ServerTM 2000, in most distributed Web The application plays a crucial role. Data storage can contain various types of data, including user application parameters, personal private data, and medical records, audits, and security logs, and even include the credentials you need to access the application. Obviously, these data should be protected - whether they can only be accessed by users with appropriate permissions during storage during storage. This chapter describes a solution to key security issues related to access data storage, and provides recommendations for accessing data from a distributed web application. This chapter focuses on SQL Server 2000, but many subjects are equally applicable in other data storage. Goal Learning this chapter: • Create a secure data access component. • Connect to SQL Server from ASP.NET using Windows Authentication or SQL Server authentication. • Select the authorization mechanism for SQL Server 2000 for your application. • Use SSL or IPSec to protect data during the transfer between client applications and data sources. • Create a database account that has the least privilege used in SQL Server 2000. • Safely store database connection strings and user credentials. • Use the password hash and SALT values to securely store user credentials for authentication to the database. • Prevent SQL databases from being attacked by SQL injection. • Start the audit mechanism on SQL Server 2000. Applicable to: This chapter is suitable for the following products and technologies:
• Microsoft_ Windows_ XP or Windows 2000 Server (with Service Pack 3) and its later operating system • .NET Framework version 1.0 (with Service Pack 2) and its later version • Microsoft Visual C # .NET Development Tool • SQL Server 2000 (with Service Pack 2) and its subsequent version How to use this chapter to get the biggest gain from this chapter:
• You must have experience in programming using Visual C # .NET. • You must have experience in developing and configuring an ASP.NET web application. • You must have experience in configuring SQL Server 2000 security. • You must have experience in configuring Windows Security and Creating User Accounts using the Windows Management Tool. • Read article "Introduction to Building Secure ASP.NET Applications", which details the importance of secure communication in authentication, authorization, and distributed web applications. • Read article "Security Model for ASP.NET Applications" outlines the architecture and technology used to create distributed ASP.NET web applications, and focusing on authentication, authorization, and secure communication in this architecture. Where the location is located. • Read article "Authentication and Authorization", which provides information on simulation, trusted subsystems, and SQL Server 2000 roles, which will be discussed in this chapter. • Read article "Communication Security", which describes SSL and IPSec, you can use them to protect data storage and customer application communication channels. • Read article "ASP.NET Security", which involves in-depth security issues related to ASP.NET. In particular, the problem of process identification, calling party identifier, and simulation, which affects the options available when your ASP.NET application performs data storage authentication. • Please read the following articles, these articles describe how to implement many of the techniques mentioned in this chapter: • "How to create a custom account to run asp.net" • "How to use ipsec to provide secure Communication Between Two Servers" • "How to Use SSL TO Secure Communication with SQL Server 2000" • "How to create a dpapi library" • "How to use dPapi (Machine Store) from ASP.NET" • "HOW TO USE DPAPI (User Store) from ASP. NET with Enterprise Services "•" How to store an encrypted connection string in the registry "•" How to use forms authentication with sql server 2000 "
This page
Introduction Data Access Security Authentication Authorized Security Connection Create Permissions Connection Create a Database Account Security Storage Database Connection String for Database Connection Strings Verify User Identity SQL Injection Attack Audit SQL Server Process Identification Summary Introduction Data Access Security Map 1 Display important security issues related to data access.
Figure 1 Important data access security problem
The important issues shown in Figure 1 are summarized below (these issues will be discussed in detail in this chapter):
• Safe Storage Database Connection Strings. This is especially important if your application is connected to SQL Server using SQL authentication or connecting to a non-Microsoft database that requires explicit login credentials. In these cases, the connection string contains a plain text form username and password. • Use the correct logo to access the database. Data access can be performed using the process identifier of the calling process, one or more service IDs or the original modes of the original modes (using analog / delegate). What identical to choose is determined by the data access model - trusted subsystem or analog / delegation. • Protect the security of data passed through the network. For example, protect the login credentials and pass and transmit confidential data for SQL Server. Note Public login credentials only on the network you use SQL authentication rather than Windows authentication. SQL Server 2000 supports SSL using server certificates. IPSec can also be used to encrypt communication between client computers (such as web or application servers) and database servers. • Authenticate the database call side. SQL Server supports Windows Authentication (using NTLM or Kerberos) and SQL authentication (using SQL Server's internal authentication mechanism). • Call the party authorization to the database. Permissions are associated with separate database objects or associated with users, groups, or roles. SQL Server Gateway Guard Figure 2 Keep an important gateway guarding the SQL server data access. Figure 2. SQL Server gateway guard
Important gateway guards include:
• Selected data stored for saving database connection strings. • SQL Server login (determined by the server name specified in the connection string). • Database User (SQL Server Logging in the user context in the database) and its related database roles. • Permissions associated with separate database objects. Permissions can be assigned to users, groups, or roles. The granularity of trusted subsystems and analog / delegated database access is a key factor that needs to be considered. You must consider that you need user-level authorization for the database (this requires analog / delegate model), or you can authorize the user using the application role logic in the application intermediate layer. This is a trusted subsystem model. If the database requires user-level authorization, then you need to simulate the original call. Although this simulation / delegation model is supported, it is recommended that you use trusted subsystem models; in this model, check the original modes in the IIS / ASP.NET gate, map it to a role, then according to Role membership authorize it. Then, use the service account at the application or role level, or use the application's process identifier (such as an ASPNET account) to authorize the application's system resource. Figure 3 shows these two models.
Figure 3 Trusted subsystem model and simulation / delegation model for database access
There are also many important factors that should be considered when connecting SQL Server for data access. These factors are summarized (described in detail later):
• What type of authentication should be used? Although Windows authentication provides enhanced security features, firewalls and non-credible domains may force you to use SQL authentication. If so, you should make sure that the application uses SQL authentication as soon as possible, which will be introduced in the "SQL Authentication" section below this chapter. • Single user role with multi-user roles. Depending on the application's users, your application is to access SQL separately using an account that has a set of fixed permissions within the database, or needs multiple (role-based) accounts? • Calling party ID. The database needs to receive an identity of the original call by calling the context to perform authorization or audits, or you can use one or more trusted connections to pass the original caller ID in the application level? The operating system is to pass the identity of the original call, which requires the analog / delegation model in the intermediate layer. This greatly reduces the effectiveness of the connection pool. Although the connection pool is still enabled, it generates many small pools (each security context), almost no connection is reused. • Do you have a confidential data to the database server or from the server? Although Windows authentication allows you to pass user credentials to the database on the network, if your application data is confidential data (for example, employee details And wage data), should be protected using IPSec or SSL. Back to top
Authentication This section describes how to authenticate SQL Server clients before connecting to SQL Server and how to select the logo for database access in the client application. Windows Authentication Windows Authentication is safer than SQL authentication, because the former has the following advantages:
• Manage the credentials for you and do not pass credentials on the network. • Allows you to embed usernames and passwords in the connection string. • Login security is improved by setting password failure period, minimum length and locking account after multiple invalid login requests. This alleviates the threat of dictionary attacks. In the following scenarios, Windows authentication should be used:
• You use trusted subsystem models and to connect to SQL Server using a single fixed identifier. If you connect from ASP.NET, this assumes that there is no simulation in the web application configuration. In this scenario, use the ASP.NET process identification or service component identity (obtained from an account running Enterprise Services server application). • You plan to use the delegation to delegate the security context of the original call (and prepare to sacrifice the scalability of the application by abandoning the database connection pool). When you use Windows Authentication to connect to SQL Server, consider the following key points:
• Use the ASP.NET process account to the minimum authority principle. Do not enable the Logonuser API call by granting the "Part of the Operating System" to the ASP.NET process account. • Determine which code requires more permissions, then place them within the service component running in the Enterprise Services application outside the process. For more information about accessing network resources from ASP.NET and selecting and configuring the correct account to run ASP.NET, see article: "ASP.NET Security". You can choose from the following method using Windows Authentication Using Windows Authentication from the ASP.NET application (or web service, or the ASP.NET bearer), you can select from the following method:
• Use the ASP.NET process ID. • Using fixed ID in ASP.NET. • Use service components. • Use the LogonUser API and simulate a specific identity. • Use the original call logo. • Use an anonymous Internet user account. It is recommended to configure a local ASP.NET process ID by changing passwords on a web server to a certain value, and then create a mirror account by creating a local user with the same username and password on the database server. This approach is described in detail below and other methods. Use the ASP.NET process to identify if you are connected to SQL Server from an ASP.NET application (or web service, or ASP.NET bearer), use the ASP.NET process ID. This is a common method, and the application defines the trust boundary, that is, the database trust the ASP.NET account to access the database object. You have three options: • Use the image of the ASPNET local account. • Use a mirror-defined local account. • Use custom domain accounts. Using a mirroring ASPNET local account This is the simplest way, usually used when you have a target database (and can control the management of the local database server account). If you choose this method, you should use the ASPNET to at least the local account to run the ASPNET and then create a repetition account on the Database Server. In this way, there is another advantage that it can play a role in non-trusting domains and through firewalls. The number of ports open by the firewall may not be sufficient to support Windows authentication. Customized local accounts using images This method is basically the same as the previous method, but you don't use the default ASPNET account when using this method. This means two things:
• You need to create a custom, have appropriate permissions and privileges. For more information, see "How to Create a Custom Account to Run Asp.Net". • You no longer create the default account created by the .NET Framework installation process. A company's system may not allow the default installation account. This is likely to improve the security threshold for the application. For more information, see Sans TOP 20: "G2 - No password or the most weak account" (http://www.sans.org/top20.htm). This method using a custom domain account is similar to the previous method, and the difference is that you use the least permissions of the domain account instead of local accounts. This method assumes that the client computer and server computer are located within the same domain or trust domain. Its main advantage is that the computer is not shared between the computer and the computer, and the computer only provides access to the domain account. And, the management of domain accounts is easier. Implementing an ASPNET process identifying an account using a mirroring from ASP.NET to the database, you need to do the following:
• Using User Manager on the web server to reset the password of the ASPNET account to a known strong code value. Important If you change the ASPNET password to a known value, the password in the Local Security Authority "(LSA) of the local computer will no longer match the account password stored in the Windows Security Account Manager (SAM) database. If you need to restore the AutoGenerate default, you must do the following: Run the ASPNET_REGIS.EXE, reset the ASP.NET to its default configuration. For more information, see Articles in Microsoft Knowledge Base Q306005: "HOWTO: REPAIR IIS MAPPING AFTER You Remove and Reinstall IIS". When you complete the reset, you will get a new account and the new Windows Security Identifier (SID). The account's permissions are set to their default values. This way, you need to explicitly reapply your permissions and privileges for the old ASPNET account. • Set the password in Machine.config. Encrypt ProcessModel / aspnet_setReg, Username Password = "registry: hklm / software / yoursecureapp / ProcessModel / aspnet_setReg, Password ".... /> . You should use Windows ACL to prevent Machine.config from being illegally accessed. For example, restricting an IIS Anonymous Internet User Account Access Machine.config. • Create a mirror account on the Database server (using the same username and password). • In the SQL database, create a server login for the local ASPNET account and map the login to a user account in the desired database. Then create a database user role, add the database user to the role, and configure the appropriate database permissions for the role. For more information, it will be introduced in "Creating a Database Account" in this chapter. Connect to SQL Server using Windows authentication To connect to SQL Server using Windows authentication, perform the following steps: • In the client application, use the connection string containing "Trusted_Connection = YES", or "Integrated Security = SSPI". These two strings are equivalent, and Windows authentication is generated (assuming SQL Server is configured to use Windows authentication). For example: sqlConnection conn = new sqlconnection "Server = YourServer; Database = youriver;" "Trusted_Connection = YES;"); - Or - SqlConnection Conn = New SqlConnection "Server = YourServer; Database = YourDatabase;" "Integrated Security = SSPI;"); Impressing the requested client (ie, the client authenticated by SQL Server) is determined by the client's thread analog token (if the thread is currently being simulated) or the current process token of the client. Using this method using fixed ID within ASP.NET, you should use the Impersonate = "true" Username = "Registry: hklm / software / yoursecureapp / Identity / Aspnet_SetReg, UserName Password = "registry: hklm / software / yoursecureapp / Identity / aspnet_setReg, Password "/> This becoming the default identity you are using when connecting to the network resource (including database). A fixed analog ID is not recommended when using .NET Framework 1.0 on a Windows 2000 server. This is because ASP.NET needs to "act as part of the operating system". The ASP.NET process requires this permission because it needs to perform the Logonuser call using the user credentials you provide. Note Microsoft Windows .NET Server 2003 does not require "Part of a part of the operating system" when performing the LogonUser call. And, .NET Framework 1.1 will provide enhancements to this scenario on Windows 2000. Log in is performed by the IIS process so that ASP.NET does not need to "act as part of the operating system". For more information on this strong privilege, see Microsoft Systems Journal (Microsoft System Journal) "SECURITY Briefs" in August 1999 (http://www.microsoft.com/msj/0899/security / Security0899.aspx). The .NET Framework version 1.1 will provide enhancements to this scenario on Windows 2000. Log in is performed by the IIS process so that ASP.NET does not need to "act as part of the operating system". Using Service Components You can develop service components that dedicated to data access code. With service components, you can use the Components to access the database in the Enterprise Services (COM ) server application that runs in a specific identity, you can also write code using the LogonUser API to perform analog. Service components outside the process can improve the security threshold because the process jumps to add an attacker's difficulty, especially the process is more difficult to attack at different identifiers. Another advantage is that you can separate the code that needs more permissions from the rest of the application. Call the logonuser and simulate a specific Windows ID You should not call the Logonuser from the ASP.NET. In Windows 2000, this method requires you to provide the ASP.NET process identifier to provide the permissions of "part of the operating system". The preferred method is to use the service components in the Enterprise Services server application from the ASP.NET process to be called Logonuser as described above. Using the original ID identification To use this method, you need to use the Kerberos to delegate and simulate the caller of the database directly from the ASP.NET or service component. From ASP.NET, add the following code to the application's web.config file. By configuring IIS to anonymity authentication, this configuration causes your web application code to operate using an anonymous Internet user analog tokens. In a web hosting environment, the advantage of this approach is to allow you to review and track database access from multiple web applications, respectively. More information • For more information and implementation details of the identity of the original call, see "Flowing the Original Caller to the Database" in the article "Securing .NET Web Applications in An Intranet Environment". • For more information on how to configure IIS to use an anonymous user account, see article: "ASP.NET Security". When can I use Windows authentication? Some application programs may disable use of Windows authentication. E.g: • Your database client and database server are separated by a firewall that is forbidden to use Windows authentication. • Your application needs to use multiple identities to connect to one or more databases. • You are connected to a non-SQL Server database. • There is no security in ASP.NET to run code as specific Windows users. Or you can't (or don't want) to pass the security context of the original call, or you want to use a dedicated service account (instead of granting login permissions to the end user), or at the same time there are both facts. In these scenarios, you need to use SQL authentication (or a native authentication mechanism for the database), and must: • Protect database user credentials on the application server. • Protect database user credentials during transmission from the server to the database. If you use SQL authentication, there are a variety of ways to ensure SQL authentication is safer. These methods will be described in detail in the next section. SQL authentication If your application needs to use SQL authentication, you need to consider the following key points: • Connect to SQL using an account with at least permissions. • The credentials are passed through network, so they must be protected. • You must protect the SQL connection string (which contains credentials). Connection String Type If you use credentials (usernames and passwords) to connect to the SQL Server database, the connection string is as follows: USING THE SQL Server .NET Data Provider: SqlConnection conn = new SQLCONNECTION "Server = YourServer; UID = YouruserName; PWD = YourstrongPWD;" "Database = yourdatabase"); Using the OLE DB .NET DATA Provider: OLEDBCONNECTION CONN = New OLEDBCONNECTION "Provider = sqloledb; data source = yourver;" "UID = YouruserName; PWD = YourstrongPwd; Initial Catalog = YourDatabase"); If you need to connect to a specific instance of SQL Server installed on the same computer (only the features provided in SQL Server 2000 or later), the connection string is as follows: USING THE SQL Server .NET DATA Provider: SqlConnection conn = new SQLCONNECTION "Server = YourServer / Instance; UID = Yourusername; PWD = YourStrongPWD;" "Database = YourDatabase"); If you connect to the Oracle database with explicit credentials (usernames and passwords), the connection string is as follows: oleDbconnection conn = new OLEDBCONNECTION "Provider = msdara; data source = youriDatabasealias;" "User ID = YouruserName; Password = yourstrongPwd;"); For more information, more information about using General Data Links (UDL) files in the connection, please see Articles in Microsoft Knowledge Base Q308426: "How to: Use Data Link Files with the OLEDBCONNECTION Object in Visual C # .NET ". Choose a SQL account for a connection Do not use the built-in SA account or any account that belongs to the SQL Server Sysadmin fixed server role or a member of the DB_OWNER fixed database role. Sysadmin members can perform any action in SQL Server. DB_OWNER members have unrestricted privileges in the database. You should use a strong password account with minimal privileges. Avoid using the following connection strings: SqlConnectionstring = "Server = yourver / instance; Database = YourDatabase; UID = SA; PWD =; " Using a high-level account with a minimum authority, for example: sqlconnectionstring = "server = yourver / instance; Database = yourdatabase; Uid = yourstrongaccount; PWD = Yourstrongpassword; " Note that this does not solve the problem of storing credentials in a web.config file. Everything you have made so far is to use accounts with minimal privileges to limit the range of losses that may cause hazards. To further improve the security threshold, you should encrypt the credentials. Note If you select case sensitive sort order when you install SQL Server, your login ID is also case sensitive. Clearing the credentials on the network When you connect to SQL Server authenticated using SQL authentication, the username and password are passed in a clear text on the network. This may mean a serious safety hazard. For more information on how to protect the channels between applications or web servers and database servers, will be introduced in the "Secure Communication" section below this chapter. Protecting the SQL connection string username and password should not be stored in the configuration file in a clear manner. For more information on how to securely store the connection string, will be described in the "Secure Storage Database Connection String" section below this chapter. Authenticating non-SQL Server Database You may experience a typical problem that you may encounter when connecting to a non-SQL database, is similar to what you need to use SQL authentication. If the target resource does not support Windows authentication, you need to provide a clear credential. To protect this type of application security, you must securely store the connection string, and must also protect communication security on the network (preventing credentials from being taken). Back to top Authorized SQL Server provides a number of roles based on role-based licensing methods. These methods are around three types of roles supported by SQL Server: • User-defined database roles. These roles are used to collect users with the same security permissions in the database into groups. Create SQL Server Login and match the specific database user. Then add the database user to the user database role and use these roles to establish permissions for each database object (stored procedure, table, view, etc.). • Application roles. The similarities between these roles and user database characters are: Use them when creating object permissions. However, different from the user database role is that they do not include a user or group, but must be activated by an application using a built-in stored procedure. Once activated, grant the permissions of the role determine the data access capabilities of the application. The application role allows the database administrator to grant the selected application access to the specified database object. This is different from the permissions to the user. • Fixed database roles. SQL Server also provides a fixed server role, such as DB_DataReader and DB_DataWriter. These built-in roles are present in all databases, which can be used to quickly grant a set of specific (and other commonly used) read permissions in the database. For more information on these different roles (and fixed database roles but fixed server roles applied to the server level rather than database level), see "SQL Server Online Book" (http://www.microsoft.com /sql/techinfo/productdoc/2000/books.asp). Using multiple database roles If your application has multiple user categories, users within the same category require the same permissions in the database, then your application requires multiple roles. Each role requires a different set of permissions in the database. For example, members of the Internet user role need to have read-only privileges to most tables in the database, and members of the administrator or operator role need to read / write permissions. In this scenario, multiple user-defined SQL Server database roles can be used. These roles are used to grant database object privileges to users who have the same security permissions within the database. Using this method, you must: • Create multiple service accounts for database access. • Create a SQL Server login for each account. Create a database user, grant it to log in to the database. • Add each database user to the user-defined database role. • Establish the necessary database permissions for each role in the database. • Authorization to the user in the application (ASP.NET web application, web service or intermediate component), then use the application logic in the data access layer to determine which account is connected to the database. This is based on the role member of the caller. You can configure different ways to separately grant permissions to users who belong to a set of characters. Then, add a mandatory role check in the method code to determine the role member identity (the member identity determines the connection to use). Figure 4 shows this method. Figure 4. Connect to SQL Server using multiple SQL user database roles For this scenario, use the preferred Windows authentication, you can develop code (using the Logonuser API) in the Process Service Components to simulate a set of logos in a set of Windows IDs. For SQL authentication, you should use different connection strings (including different user names and passwords) based on role logic in the application. Back to top Security Communications In most applications, you need to protect communication links between application servers and databases. You need to be able to ensure: • Message confidentiality. The data must be encrypted to ensure that it remains private. • Message integrity. Must be signed for data to make sure it is not tampered with. In some scenarios, you need to protect all data passed between application servers and database servers; in some scenarios, it is necessary to protect selected data in data transmitted through a particular connection. E.g: • Some employee data transmitted between the client and the database server is confidential data in an intranet Human Resources Application. • In Internet applications (such as secure banking applications), all data passed between application servers and database servers must be safe. • If you are using SQL authentication, you should also protect communication link security, make sure the username and password will not be damaged by the network monitoring software. Option Application Server and Database Server Network Link Security There are two options: • IPSec • SSL (Using Server Certificates on SQL Server Computers) Note You must run SQL Server 2000 to support the use of SSL. The earlier version does not support SSL. The client must have installed the SQL Server 2000 client library. Choosing a method should use IPSec or SSL depends on many important environmental factors, such as firewall reasons, operating systems and database versions, and more. Note IPSec is not used to replace the security mechanism for the application level. It is now used in the following aspects: used as a depth defense mechanism; for providing protection without changing unsafe applications; protecting non-TLS (eg, SSL) protocols to resist network attacks. More information • For more information on configuring IPSec, see the article "How to use ipsec to provide secretation between two servers". • For more information on configuring SSL, see the article "How to use SSL to Secure Communication with SQL Server 2000". • For more general information about SSL and IPsec, see the article: "Communication Security". Back to top Connection to connect to the minimum permissions Connection database means that you create only the minimum permissions you need in the database. Simply put, you don't use a SA account, SYSADMIN role or a member of the DB_OWNER role. Ideally, if the current user is not authorized to add or update the record, the corresponding account used to establish a connection (this can be synthesized as a sign representing a specific role) or not add or update records in the database. When you connect to SQL Server, the way you use must support the particle size required for database authorization. Do you need to consider what is trust in the database. It can trust: •application. • The role defined by the application. • Original modes. Database trust applications Consider a financial application you authorize to use their database. Financial applications are responsible for managing user authentication and grant access. In this case, you can manage connections over a single trusted account (this account corresponds to a SQL login or a Windows account mapped to SQL login). If you are using Windows authentication, this usually allows you to access the database using a process identity (such as an ASP.NET Assist Process or Enterprise Services Server Application ID). From an authorized perspective, this method is very rough because the identity used by the connection has access to all database objects and resources required by the application. The advantage of this method is that you can use the connection pool and simplify management because you only authorize a single account. Disadvantages are that all users are running in the same connection permission. Database Trust Different Roles You can connect to the database using a connection pool consisting of separately trusted connections, these connections correspond to the roles defined by your application; for example, a connection is available, while another for manager Use, and so on. These connections can be used to use Windows authentication or not. The advantage of Windows authentication is that it handles credential management and does not send credentials on the network. However, although Windows authentication can be used at the process level or application level, if you need multiple identities (one logo each role), other issues will bring other problems. Many applications use the LogonUser API to establish a Windows Access Token for the Windows Access Token to establish the context security, but this method should face the issue of credential management because the application must securely store the account username and password. And such applications also need to face permissions, as it requires calling process accounts must have permissions that "act as part of the operating system". Applications with SQL authentication are also facing issues of credential management and require a secure communication channel connected to the database to protect credentials. In this case, you need to transfer the original call from multiple layers to the database. This means that the client needs network credentials to be able to skip another computer from a computer. This requires the Kerberos delegation. Although this solution provides the authorization of the database, it will affect the performance and scalability of the application because you know the identity of the original call and can create each user permission to the database object. The connection pool (although it is still enabled) becomes invalid. Back to top Creating a database account with minimal permissions is a simple example to explain how to create a database account with the least privilege. Although most database administrators are familiar with these steps, many developers may not be familiar, relying on using SA account to force their application to run. This may bring problems when transferring from a development environment and then entering a production environment, because the application shifts from a highly open environment to a more stringent settings, and such settings hinder the application normal operation. You should start using SQL login for SQL accounts or Windows accounts (users or groups). Then, by creating a database user to grant login access to the database, add the database user to the user-defined database role and assign permissions to the role. Set up SQL data access account 1. Create a new user account and add the account to the Windows group. If you want to manage multiple users, you should use the user group. If you have to handle a single application account (such as a duplicate ASP.NET process account), you can choose to add this account to the Windows group. 2. Create a SQL Server login for the user / group. • Start "Enterprise Manager" and find your database server and expand the Security folder. • Right-click "Logins" and click New Login. • Enter the Windows account name in the Name field, and then click "OK" to close the SQL Server Login Properties dialog. 3. Create a new database user in the relevant database to allow login to access the database. • Use Enterprise Manager and expand the Database folder, then expand the database that is accessed. • Right-click User, and then click New Database User. • Select the login name previously created. • Enter a username. 4. Create a new database role, which will then assign permissions to the role. • Right-click on the "Database" folder and click New Database Role. • Enter a role name. • Click Add to add the database user to the role. • Configure permissions as described below. 5. For tables that need to be accessed, a "SELECT" permission is granted to the database user; for any related stored procedure, "Execute" permissions are granted to the user. Note If the stored procedures and tables are owned by the same person, and only the stored procedure access to the table (does not need to be directly access table), it is sufficient to grant the execution permission to grant the stored procedure. The reason is the concept of owner relationship chain. For more information, see "SQL Server Online Book". 6. If you want the user account to have access to all views and tables in the database, add these views and tables to the DB_DataReader role. Note The public role exists in each database, all other database users and roles belong to this role and cannot be deleted. You should undo or reject the permissions on this role so that authorization is completely permissions related to user-defined database roles. Back to top Safe Storage Database Connection Strings can store database connection strings in multiple locations, and the degree of security and configuration of these locations and methods are different. Options The following list describes the primary options for storing the connection strings: • Encrypted with DPAPI • Using a plain text in Web.config or Machine.config • UDL file • Custom text file • Registry • COM directory Using DPAPIWINDOWS 2000 and later operating systems provide Win32 Database Protection API (DPAPI ) To encrypt and decrypt data. DPAPI is part of the password API (Crypto API), which is implemented in Crypt32.dll. It consists of two methods - CryptProtectData and CryptunProtectData. DPAPI is particularly useful because it can eliminate key management issues brought about by the application of the password. Although encryption ensures data security, you must take additional steps to ensure the security of the key. DPAPI uses the password of the user account associated with the calling code of the DPAPI function to derive the encryption key. Therefore, the operating system (not the application) management key. Why not use LSA? Many applications use local security authorities (LSAs) to store confidentiality. DPAPI is better than the LSA method in the following areas: • To use the LSA, the process requires administrative privileges. This will cause safety hazards because it greatly increases the loss of attackers who managed to destroy the process. • LSA only provides a limited number of slots for confidential storage, and many slots have been occupied by the system. Machine Storage and User Storage DPAPI can be used in conjunction with machine storage or user storage (requires a loaded user profile). DPAPI is used by default for user storage, but you can use the machine storage by passing the CryptProtect_Local_Machine flag to the DPAPI function. This user profile provides an additional security layer because it limits which users can access confidential content. Only users encrypting the data can decrypt the data. However, when using DPAPI through the ASP.NET web application, you need to perform additional development work using the user profile, as you need to take a clear step to load and uninstall the user profile (ASP.NET does not automatically load user configuration) file). Machine storage is easier to develop because it does not require user profile management. However, unless an additional entropy parameter is used, it is not safe because any user of the computer can decrypt data. Entropy is a random value designed to make decryption confidential content. The problem that occurs using additional entropy parameters is that it must be securely stored by the application, which brings another key management problem. Note If you use the DPAPI and machine storage, the encrypted string is specific for a given computer, so you must generate encrypted data on each computer. Do not copy encrypted data across computers in the field or group. If you use DPAPI and user storage, you can decrypt data on any computer with a roaming user profile. DPAPI Implementation Solution This section describes two implementations that describes how to protect the connection string (or any type of confidential information) from the ASP.NET web application using DPAPI. Imforgments of this section include: • Use DPAPI from Enterprise Services. This scenario allows you to use DPAPI for user storage. • Use DPAPI directly from ASP.NET. This solution allows DPAPIs to be used for machine storage, which is easier to develop solutions because you can call DPAPI directly from the ASP.NET web application. DPAPI cannot be called using the DPAPIASP.NET Web application from Enrprise Services, because this requires a loaded user profile. The ASPNET account that is often used to run a web application is a non-interactive account, not a user profile. In addition, if the ASP.NET application is being simulated, the web application thread runs as a user currently verified, which varies depending on the request. Here you introduce the following questions about the ASP.NET application to use DPAPI: • ASP.NET application calling DPAPI will fail through the ASP.NET application running under the default ASPNET account. This is because the ASPNET account does not have a user profile, which cannot be used for interactive logins. • If the ASP.NET web application is configured to simulate its caller, the ASP.NET application thread has an associated thread analog token. The login session associated with the analog token is a network login session (used on the server to represent the caller). Network login session does not enable the user profile to be loaded. To resolve this issue, you can create a service component (within the Enterprise Services (COM ) server application) to call DPAPI. You can ensure that the account running the component has a user profile and you can use the Win32 service to automatically load the configuration file. Note The Win32 service can be avoided by calling the Win32 profile management function (loaduserprofile and unloaduserprofile) in the service component. This method has two disadvantages. First, a request to call these APIs will seriously affect performance; Second, these APIs require execution of the call to manage authority on the local computer, which disrupts the minimum permission principle of Enterprise Services process account. Figure 5 shows the Enterprise Services DPAPI solution. Figure 5. ASP.NET web application uses COM server applications to interact with DPAPI In Figure 5, the runtime sequence of the event is as follows: 1.Windows Service Control Manager launches Win32 service and automatically loads the user profile associated with the service run account. The same Windows account is used to run Enterprise Services applications. 2.WIN32 Service Call the service component call startup method, which launches the Enterprise Services application and load the service component. 3. The web application retrieves the encrypted string from the web.config file. You can store the encrypted string using the Value = "AqaAAncmnd8BFderjhoawe / Cl SbaaaabCQC / XCHXI3" /> You can retrieve the encrypted string with the following code line: string connString = configurationSettings.appsettings ["sqlconnstring"]; Note You can use Web.config or Machine.config to store encrypted connection strings. Machine.config is the preference because it is located in the system directory outside the virtual directory. This will be further introduced in the next section "Web.config and Machine.config". 4. The application calls the method on the service component to decrypt the connection string. 5. Service components interact with P / Invoke to call the Win32 DPAPI function. 6. This decrypted string is returned to the web application. In the WEB.CONFIG file, the encrypted connection string is first prepared, first prepare a utility application that accepts the connection string, and calls the EncryptData method for the service component to obtain an encrypted string. This utility must be run when you log in with the account running Enterprise Services Server application. Use DPAPI directly from ASP.NET If you use the machine store, and call the DPAPI function with the CryptProtect_Local_Machine tag, you can call DPAPI from the ASP.NET web application. However, since you use the machine to store, you can access confidential information any Windows account that can be logged in to your computer. A method of reducing risk is to increase entropy, but this requires additional key management. If you do not want to use entropy when you use the machine, consider the following alternative: • Use the ACL to limit access to encrypted data (regardless of whether the data is stored in the file system or stored in the registry). • Consider the hardcoding of entropy parameters to the application, so that the key management is disabled. More information • For more information on the DPAPI library that creates with .NET web applications, see "How to create a dpapi library". • For details on how to use DPAPI directly from ASP.NET, see "How to use dpapi (Machine Store" from asp.net. • For details on how to use DPAPI from Enterprise Services, see "HOW TO Use DPAPI (User Store". • For more information on Windows data protection with DPAPI, see MSDN: "Windows Data Protection". Use Web.config and Machine.config recommends not to store passwords in Web.config in a clear text. By default, HttpForbiddenhandler prevents files from being downloaded and viewed by malicious users. However, users who have access to folders with configuration files can still see the username and password. Machine.config is considered a more secure storage location than Web Application because it is located in a system directory (with ACL) outside the web application. For information on protecting Machine.config, see Chapter 8 "ASP.NET Security". Use the UDL file OLE DB .NET data provider to support the UDL file name in its connection string. To reference UDL files, use "File Name = Name.UDL" in the connection string. Important This option is only available when you connect to the database using the OLE DB .NET data provider. The SQL Server .NET data provider does not use the UDL file. It is recommended not to store UDL files and other application files in a virtual directory. You should save them outside the virtual directory hierarchy of the web application, then use the Windows ACL to protect the file or contain the folder of the file. You should also consider that the UDL file is stored on logical volumes outside the logical volumes where the operating system is located to prevent possible file specification and directory traversal errors. ACL Particle If you apply ACL when you use a UDL file (or any text file), it provides a higher granularity than Machine.config. The default ACL associated with Machine.config is awarded access to access rights to a wide range of local and remote users. For example, Machine.config contains the following default ACLS: MachineName / ASPNET: RBuiltin / Users: R Builtin / Power Users: C Builtin / Administrators: f NT authority / system: f Comparison, you can further lock your application's UDL file. For example, you can limit access to an administrator, a system account, and an ASP.NET process (the account requires read rights), as shown below. Builtin / Administrators: F MachineName / ASPNET: R NT authority / system: f Note Because the UDL file can be modified from the outside to any ADO.NET client application, the connection string containing the reference to the UDL file is analyzed each time you open the connection. This will affect performance, so for best performance, it is recommended that you use a static connection string that does not contain UDL files. Create a new UDL file 1. Use the Windows Explorer and navigate to the folder you want to create a UDL file. 2. Right-click the folder, point to "New", and then click Text Document. 3. Provide a file name with the .udl file extension. 4. Double-click the new file to display the UDL Properties dialog. For more information about using the UDL file from the Microsoft C # Development Tools, see Articles in Microsoft Knowledge Base Q308426: "How to: Use Data Link Files with OLEDBConnection Object In Visual C # .NET". Use a custom text file that many applications use a custom text file to store the connection string. If you use this way, consider the following suggestions: • Store custom files outside the virtual directory hierarchy of the application. • Consider putting files on logical volumes other than operating system logical volumes to prevent possible file specification and directory traversal errors. • With restricted ACL protection files, the ACL only grants read permissions to your application's process account. • Avoid storage connection strings in a clear manner in a file. You should consider using DPAPI to store encrypted strings. Using Registry You can use custom items in the Windows registry to store the connection string. These information stored can be saved in the HKEY_LOCAL_MACHINE (HKLM) or HKEY_CURRENT_USER (HKCU) registry configuration unit. For process identity, such as an ASPNET account, such as an ASPNET account, which must be stored in HKLM to allow ASP.NET code to retrieve it. If you use this method, it should be: • Use ACL to protect registry entries using RegedT32.exe. • Encrypt data before storage. For more information on encrypted data storage in the registry, see "How to store an encrypted connection string in the registry". With the COM directory If the web application contains service components, you can store the connection string as a constructor string in a COM directory. In this way, these strings are easy to manage (using "Component Services" tools, and it is also easy to retrieve by component code. Enterprise Services calls the construct method of the object after instantiating the object and passes the configured constructor. The COM directory cannot provide high security because the information is not encrypted; however, compared with the configuration file, it increases the security threshold due to the increase of the process. To disable access to the Directory through the Component Services tool, you should only add a group of users you want to authorize to the Administrator and Reader roles of the System application. The following example shows how to retrieve object constructors strings from the service component. [Constructionenabled (Default = "default connection string")] Public Class Yourclass: ServicedComponent { Private string _connectionstring; Override protected void construct (String S) { _Connectionstring = S; } } To increase security, you can encrypt the constructor through the Add Code before storage and then decrypt in the service component. More information • For more information on using the connection string, see Articles in Microsoft Knowledge Base Q271284: "HOWTO: Access COM Object Constructor String In a Visual Basic Component". • For the full code example provided by the .NET Framework SDK, see Object constructor in the following directory Example: / Program Files / Microsoft Visual Studio .NET / FRAMEWORKSDK / Samples / Technologies / ComponentServices / ObjectConstructions. Back to top Verify the user identity for the database If you want to build an application that requires a data stock to verify user credentials, consider the following: • Store unidirectional password hash (using random SALT values). • Avoid SQL injection when verifying user credentials. The WEB application that stores the One-way Password hash (using the SALT value) using a table single authentication Web application usually needs to store user credentials (including passwords) in the database. For security, you should not store passwords in the database (regardless of expressing or encrypted). You should avoid the storage of encrypted passwords, because this will bring key management issues - you can use encryption to protect your password security, but you need to consider how to store encrypted keys. If the key is destroyed, the attacker can decrypt all passwords in the data store. The preferred way is: • Store a single-way hash of the password. Calculate the hash when you need to verify your password. • Jointly using password hash and SALT values (a random number with a strong encryption capability). By using the SALT value with the password hash, you can reduce the threat of dictionary attacks. Creating the code below the SALT Value Description How to generate a SALT value using the random digital generation function provided by the RNGCryptoServiceProvider class in the System.Security.cryptography name space. Public Static String Createsalt (int size) { RNGCRYPTOSERVICEPROVIDER RNG = New RNGCRYPTOSERVICEPROVIDER (); BYTE [] BUFF = New byte [Size]; RNG.GETBYTES (BUFF); Return Convert.TOBASE64STRING (BUFF); } Creating a hash value (using the SALT value) The following code snippet shows how to generate a hash value from the supplied 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; } For more information about the full implementation details of this method, see "How to Use Forms Authentication with SQL Server 2000". Back to top SQL Injection Attack If you have to use a form single authentication for the SQL database, you should take the precautions introduced in this section to prevent SQL injection attacks. SQL Injection Attack is a behavior to passing other (malicious) SQL code to an application, which is usually attached to the legitimate SQL code contained in the application. All SQL databases are varying degrees to suffer from SQL injection attacks, but the focus of this chapter is on the SQL Server database. When you handle the user input as a part of the SQL command, pay special attention to the possible SQL injection attack. If your authentication scheme is used to verify the user for the SQL database, for example, you must prevent SQL injection attacks for SQL Server. If you build a SQL string using unfilled input, the application may be entered by malicious user (note, don't trust user input). The risk is that when you enter a user input into a string that will become an executable statement, malicious users can use the escape to attach the SQL command to the SQL statement you want. The code snippet in the following sections uses the PUBS database included with SQL Server to illustrate examples of the SQL injection attack. Problem When you add user input or other unknown data to a database query, your application is easily attacked by SQL injection. For example, the following two code snippets are easily attacked. • You entered the SQL statement built with unfilled users. Sqldataadapter mycommand = new sqldataadapter "SELECT AU_LNAME £ ¬au_fname from authors where au_id = '" Login.Text "'", myconnection; • You call the stored procedure by building a single string, which contains unfilled user input. Sqldataadapter mycommand = new sqldataadapter ("LoginStoredProcedure '" Login.Text "'", myconnection; Analysis of SQL Script Injection Attack When you accept unfilled user input values (see above) in your application, malicious users can use escape characters to add their own commands. Try to consider such a SQL query, which hopes that the user's input is a social security number, such as 172-32-xxxx, the last query form is as follows: SELECT AU_LNAME, AU_FNAME FROM AUTHORS WHERE AU_ID = '172-32-xxxx' Malicious users can enter the following text into the application input field (for example, text box control). '; INSERT INTO JOBS (Job_Desc, Min_LVL, Max_LVL) VALUES (' Important Job ", 25, 100) - An INSERT statement is injected into this example (but any statements allowed by the account for connecting to SQL Server) can be performed). If the account is a member of the sysadmin role (it allows the use of XP_cmdshell's shell command), and the domain account used by SQL Server has access to other network resources, the code is particularly large. The above command generates the following combined SQL string: select au_lname, au_fname from authors where au_id = '; INSERT INTO JOBS (Job_Desc, Min_LVL, Max_LVL) Values (' Important Job ', 25, 100) - In this case, The '(single number) character of malicious input is stopped in your SQL statement. It only turns off the current statement only in the following cases: The meaning of the mark below is not the continuation mark of the current statement, but the start tag of a new statement. SELECT AU_LNAME, AU_FNAME AUTHORS WHERE AU_ID = '' ; (Semicolon) character tells SQL you are starting a new statement, and keeping back is malicious SQL code:; Insert Into Jobs (Job_Desc, Min_LVL, Max_LVL) Values ('Important Job ", 25, 100) Note The SQL statement does not necessarily require a semicolon. This is related to the supplier / implementation method, but SQL Server does not need. For example, SQL Server analyzes the following statement as two separate statements: SELECT * from myTable delete from MyTable Finally, - (Double Topline) Character Sequence is a SQL Comment Symbol, tells SQL ignores the rest of the text, in this case, ignore the '(single quotes) end character (otherwise it will cause SQL analyzer errors) . As the result of the above statement, all the text performed by SQL is: SELECT AU_LNAME, AU_FNAME FROM AUTHORS WHERE AU_ID = '; INSERT INTO JOBS (Job_Desc, Min_LVL, Max_LVL) VALUES (' Important Job ', 25, 100) -' Solution The following methods can be used to call SQL from the application security. • Use the Parameters collection when building a SQL statement. Sqldataadapter mycommand = new sqldataadapter "SELECT AU_LNAME, AU_FNAME FROM AUTHORS WHERE AU_ID = @au_id", MyConnection); SQLParameter Parm = mycommand.selectcommand.parameters.add ( "@au_id", Sqldbtype.varchar, 11); PARM.VALUE = Login.Text; • Use the parameters collection when calling the stored procedure. // Authorlogin Is A Stored Procedure That Accepts a Parameter Named Login Sqldataadapter mycommand = new sqldataadapter ("authorlogin", myconnection; mycommand.selectcommand.commandtype = commandtype.storedProcedure; SQLParameter Parm = mycommand.selectcommand.parameters.add ( "@Loginid", sqldbtype.varchar, 11); PARM.VALUE = Login.Text; If you use the parameters collection, this input will be processed by text regardless of what the malicious user is included in the input. Another advantage of using the parameters collection is that you can perform types and length checks. The value of the exceeding range triggers anomaly. This is a safe depth defense example. • Filter SQL characters from user input. The following method explains how to ensure that any strings used in simple SQL comparison (equal to, less than, greater than) statements are secure. It is to do this by ensuring that any apostrophe used in a string is added to make this. In the SQL string, two consecutive apostrophes are considered as an apostrophe character instance in the string, not a separator. Private string safESqlliteral (String Inputsql) { Return INPUTSQL.REPLACE ("'", "' '"); } Ã, ... String SafeSQL = Safesqlliteral (Login.Text); Sqldataadapter mycommand = new sqldataadapter "SELECT AU_LNAME, AU_FNAME FROM AUTHORS WHERE AU_ID = '" Safesql "'", myconnection); Other best practices The following are other measures for reducing security vulnerabilities and to limit possible damage to a certain range: • Input illegal content is prevented by limiting the size and type of inputs (front-end applications). By limiting the size and type of the input, the possibility of hazard can be greatly reduced. For example, if the database view field is eleven characters, it is enforced to achieve this rule. • Run SQL code with an account with the least permissions. This greatly reduces the possible loss. For example, if the user wants to inject SQL to delete a table of the database, the account used by the SQL connection does not have the corresponding permissions, and the SQL code will fail. This is another reason why the SA account, sysadmin, or db_owner is used for the application's SQL connection. • When an exception error occurs in the SQL code, do not disclose the SQL error caused by the final user. Record the error message and only display user friendly information. This avoids unnecessary details that may be helpful for attackers. Protection mode matching statement If the input will be used in the string of the Like clause, the character (except for the apostrophe) also has a special mode matching meaning. For example, in the Like clause, the% character indicates that "Match Zero or Multiple Characters" is a text character in the input as a text character without special meanings, and they also need to perform escape. If you do not perform special processing, the query may return an error result, and the non-proportional mode matching characters in the beginning or at the beginning of the string may also destroy the index. For SQL Server, you should use the following method to make sure the input content is valid: private string safesqllikeclauseliteral (String Inputsql) { // Make the folloading replacements: // 'becomes' // [Becomes [[] /% Becomes [%] // _ Becomes [_] String s = INPUTSQL; S = INPUTSQL.REPLACE ("'", "' '"); S = S.Replace ("[", "[]"); S = S.Replace ("%", "[%]"); S = S.Replace ("_", "[_]"); Return S; } Back to top Audit is default that the login audit function in SQL Server does not open. You can use the SQL Server Enterprise Manager or configure the audit function using the registry. The dialog box in Figure 6 displays the enable auditing for the user's login success or failure. Log entries are written to SQL log files by default, this file is located in the following locations: C: / Program Files / Microsoft SQL Server / MSSQL / LOG. You can use any text reader (such as NotePad) to view them. Figure 6. "SQL Server Properties" dialog containing the audit level settings You can also enable SQL Server audit feature in the registry. To enable the SQL Server audit feature, create the following AuditLevel item in the registry, set the value to one of the REG_DWORD values specified below. HKEY_LOCAL_MACHINE / SOFTWARE / Microsoft / MSSQLServer / Auditlevel You can select one from the following values, allow you to capture the details you want: • 3- Capture successful login attempt also capture a failed login attempt • 2-Capture failure login attempt • 1-Capture successful login attempt • 0- Do not capture any login to try suggest that you open failed login audit function, because This is a way to determine if someone tries to maliciously attack SQL Server. Record of failure auditing attempts is small, unless you are attacking, and in this case you have to know these attempts. You can also write scripts for SQL Database Management Objects (DMO). The following code snippet displays some VBScript code examples. Sub setauditlevel (Server AS String £ ¬newauditlevel as sqldmo_audit_type) DIM OBJSERVER AS New SQLSERVER2 ObjServer.loginsecure = true 'use integrated security ObjServer.connect Server 'Connect to the Target SQL Server 'Set the audit level Objserver.integratedsecurity.Auditlevel = Newauditlevel Set objserver = Nothing End Sub Check if SQL Server online books know, enumeration type SQLDMO_AUDIT_TYPE includes: SQLDMOAUDIT_ALL 3 Log All Authentication Attempts Regardless Of Success OR Failure SQLDMOAUDIT_FAILURE 2 log failed Authentication SQLDMOAUDIT_SUCCESS 1 LOG SUCCESSFUL AUTHENTICATION SQLDMOAUDIT_NONE 0 DO NOT LOG Authentication Attempts Back to Top SQL Server's process identity runs SQL Server using a local account with the least permission. When you install SQL Server, you can choose to run SQL Server service using your local System account or specified account. Do not use the System account or administrator account. And the local account that has the least permissions should be used. You do not need to grant any specific permissions to this account, because the installation process (or SQL Server Enterprise Manager, if you are reconfigured after the SQL service is installed) awarded the necessary permissions to the specified account. If SQL Server needs to access remote computers, such as network backup and recovery or replication, you need to use a minimum permissions to create a duplicate local account on the remote server with the same username and password. Password synchronization can be implemented with script. If you want to access a server in the domain that is not trustworthy, you need to use the method of creating a repetition account. Back to top Summary below is a summary, providing some suggestions for data access in .NET web applications: • Use Windows authentication as much as possible. • Use accounts with minimal privileges in the database. • Run the ASP.NET/enterprise Services when connecting to SQL Server. • Use the user-defined database role in the database when authorization. • If you are using SQL authentication, the following steps are taken to improve security: • Use a custom account with a strong password. • Use the database role to limit the permissions of each account in SQL Server. • Add an ACL to any files for storing the connection string. • Encrypt the connection string. • Consider using DPAPIs in the credential store. • When you verify SQL using a form, take precaution to prevent SQL injection attacks. • Do not store user passwords in the database for user verification. You should store the password hash without using the SALT value instead of a plain text password or encrypted password. • Protect the confidential data sent from SQL Server or from SQL Server through the network. • Windows authentication protects credentials but cannot protect application data. • Use IPSec or SSL. Go to the original English interface
