70-340 Implementing Security for Applications with Microsoft Visual C# .NET Practice Tests

Demo Question 4.

You are an application developer for EliteCertify.com. You are conducting a code review of an assembly written by another developer. The assembly is named MyAssembly.exe. The assembly is for an application that accesses data in a Microsoft SQL Server database. All users of the application have access to the database by using their Microsoft Windows user accounts. The assembly contains the following code segment. string userid; string Password; userid = "sa"; password = ""; SqlConnection sqlConnection = new SqlConnection ( ) ; String connectionString; connectionString = "data source=myServer"; connectionString += ";initial catalog-myDatabase"; connectionString += ";user id =" + userid; connectionString += ";password =" + password; sqlConnection ConnectionString = ConnectionString ; sqlConnection . Open ( ) ; You need to improve the security of the code segment. What should you do?

A. Replace the code segment with the following code segment. SqlConnection sqlConnection = new SqlConnection ( ) ; string connectionString ; connectionString = "data source=myServer" ; connectionString += ";Integrated Security-SSPI" ; connectionString = "initial catalog-myDatabase" ; SqlConnection. Connection = ConnectionString ; SqlConnection .Open ( ) ;
B. Replace the code segment with the following code segment. SqlConnection sqlConnection = new SqlConnection ( ) ; String connectionString; connectionString = "data source=myserver ; initial catalog-myDatabase; user id=sa ; password sqlConnection. ConnectionString =ConnectionString ; sql Connection.Open ( ) ;
C. Run the caspol.exe -resolvperm MyAssembly.exe command from the command line.
D. Run the permview /decl MyAssembly.exe command from the command line.


Display Answer

Answer: A

Explanation: Never use the SQL default administrative account 'SA' and a blank password "", for any sort of access. This account has all access to all databases regardless of who or what created it as well as can be used to take complete control of the machine and even the network. SQL has hundreds of extended stored procedures (XP_???) of them xp_cmd can be use to elevate permissions well beyond what is needed and be used to compromise almost every aspect of the system and the network. Security Recommended Practices Microsoft recommends the following practices to help you protect your data and applications from malicious users and accidental user actions. Notification Services Security Practices* Run the NS$instance_name service under a weak domain or local account. Do not use the LocalSystem or NetworkService service account or any account in the Administrators group. However, if you are using a delivery protocol that requires the account that the service runs under to have additional privileges, you must use higher privileges. For example, sending notifications using an Internet Information Services (IIS) SMTP server requires the account under which the service runs to be a member of the local Administrators group. * Ensure that the password used by the service account is a strong password. For more information about strong passwords, see "Creating Strong Passwords" in the Microsoft Windows documentation. * Ensure that all code run by the NS$instance_name service, such as custom event providers, content formatters, and protocols, is from a trusted source. Notification Services assumes that code listed in the application definition file (ADF. comes from a trusted source. * Secure all folders containing configuration files or application data. For more information about securing files and folders, see File and Folder Security. SQL Server Security Practices* When installing SQL Server, never allow a blank sa password, even if you select the integrated security mode. This guarantees that if the security mode changes to mixed mode, the sa account will still have a password. * Use Windows Authentication whenever possible. Windows Authentication provides advanced security features, such as policies for password length, complexity, and expiration. Note that if the NS$instance_name service uses a SQL Server user name and password to connect to SQL Server, this user name and password are encrypted and stored in the registry. * If you use SQL Server Authentication, use strong passwords for the SQL Server login accounts and change the passwords periodically. * Do not grant unnecessary permissions to the public role in each database. The public role is a special database role to which every database user belongs, and cannot be dropped from the database. Notification Services does not use the public role. * Do not grant database access to the guest user account. The guest user account allows a SQL Server login account that does not have a database user account to access a database. * Consider encrypting the database files using NTFS file encryption. This can decrease performance, so you must weigh optimal performance against file security. Network Communications Security Practices* To reduce the possibility of intruders viewing data as it is being transferred between Notification Services and the database, use encrypted communication between client applications and SQL Server. For more information, see "Using Encryption Methods" in SQL Server Books Online. * If you are using an HTTP protocol to post data to a Web server, and if the Web server supports SSL, post the notification using an address that starts with https://. This form of address encrypts the data that is sent to the Web server. Physical Security PracticesEnsure that your servers are located in an area that is adequately secured. If a malicious user can physically access the server, the server is not secure. Database Security One of the most common scenarios for a distributed application involves reading and writing data on a remote database. The dilemma that arises is how to do so securely while maintaining application scalability. Where you choose to manage security in your application will greatly impact, either negatively or positively, the scalability of your application. To achieve scalability using database connection pooling foregoes having the database manage security. This is because database connection pooling requires the connection string be identical to pool connections. Therefore, you must manage security elsewhere. If you must track database operations on per user basis, consider adding a parameter for user identity to each operation and manually log user actions in the database. Following the advice above, another issue is how to store the database connection string, which typically contains security credentials, so multiple users can access it without compromising security. Most sample applications demonstrate storing the connection string in the Web.config or global.asax files. However, because these files are plain text files that have limited security, it is not the best location for storing this information. Should an intruder compromise your Web server's security, these files would be easily accessible. Here are just a few alternatives: * If using the Web.config file, store the connection string encrypted and then decrypt the connection string in your application code when needed. * Build a COM+ application using the ServicedComponent Class and store the connection string in the construct string for that component. When storing sensitive information in the constructor string, you should verify the following: * Only the appropriate users/groups belong to the Reader role of the System Package. However, you must carefully manage COM+ to prevent it from being unable to read its own configuration. * You have controlled and audited access to the %windows%\Registration folder, where the COM+ configuration database (RegDB. stores its files. For more information, see ServicedComponent Class. * Use integrated security to make a trusted connection with SQL Server. This makes it possible for you to use a connection string that eliminates the need for storing a password in the connection string, such as: "Data Source-mySqlserver;Integrated Security= SSPI;Initial Catalog=mtDB" There are some drawbacks to using integrated security, most of which you can overcome. Because integrated security requires a Windows account, it defeats connection pooling if you impersonate each authenticated principal using an individual Windows account. However, if you instead impersonate a limited number of Windows accounts, with each account representing a particular role, you can overcome this drawback. Each Windows account must be a domain account with IIS and SQL Server in the same or trusted domains. Alternatively, you can create identical (including passwords) Windows accounts on each machine. After a typical installation, the default security authentication mode is Windows Authentication for SQL Server 2000, which is different from SQL Server 7.0. In SQL Server 7.0, the default authentication mode is Mixed (Windows Authentication Mode and SQL Server Authentication). Windows Authentication is a better security method because of the additional security features it provides, such as secure validation and encryption of passwords, password expiration and auditing. For more information, see Authentication Modes. If you configure SQL Server to use Windows Authentication, you could create one Windows account for read-only operations and another Windows account for read/write operations. You then map each Windows account to a SQL Server login and establish the desired permissions. Using application logic, you then determine which Windows account to impersonate when performing database operations. In SQL Server, you can add any Windows user account as a member of a fixed database role. Each member gains the permissions applied to the fixed database role. For more information, see Managing Permissions. For SQL Server 7.0, integrated security does not work with SQL Server's TCP/IP network library, but uses the named pipes network library instead. As an added security measure, the ConnectionString property of the SqlConnection object does not persist or return the full connection string by default. To do so, you must set Persist Security Info to true.