Professional ASP.NET Security - Jeff Ferguson

<Title>Securing Database Connections</Title> <SCRIPT LANGUAGE="C#" runat=server>

protected void Page_Load(object sender, EventArgs e)

{

The only disadvantage to this approach is that whenever we change the database username or password, we have to update the code and recompile and re-deploy the component. There is always a tradeoff between security and flexibility, meaning that when we try to make things more flexible, we have to compromise security. When we compare the security that we get from this method, the recompilation option becomes obligatory.

COM+ Object Construction

If you don't want to compile the component every time you change the password, you can store the database connection string in the COM+ object construction. This option provides a flexible way to modify the database connection string. Although the COM+ object construction option doesn't fit in the ASP.NET XCOPY deployment paradigm, .NET still relies on COM+ for transaction management, so using the COM+ object construction is not a bad idea.

All the COM+ specific classes are stored in the System. EnterpriseServices namespace. The ConstructionEnabled attribute enables a .NET class to utilize the COM+ object construction feature. Since we're going to use the COM+ feature, we have to provide a strong name for the component. To do this, we can use the Strong Name Tool (Sn. Exe). To generate a strong name for our .NET component, type the following command in the command line:

Sn -k SecureDBConnection.snk

This will generate a strong name key file SecureDBConnection. snk that contains a public-private key pair that can be used with the served COM component to generate a strongly-named component.

82

Securing Database Access

When we strong-name our component, we use the key pair that we just created to add a public key, and a H'rital signature on top of the information about the assembly (including culture information and a version imber). This information resides within the manifest of the assembly that the component is a part of.

Let's now create our .NET component:

//Specify the COM+ application name.

[assembly: ApplicationName("SecureDBConnection")]

//Specify that we'll create a strong-named assembly, [assembly: AssemblyKeyFileAttribute("SecureDBConnection.snk")] namespace SecureDBConnection

[ConstructionEnabled(Default="server=Sruthi;uid=SecureWebUser;

pwd=!23URL32~@@@!;database=Northwind;")]

public class COMPlusObjectConstruct : ServicedComponent

//Private Database object for this class private SqlConnection sqlDBConn;

//Private Database connection string private String strConnString="";

//Default constructor

public COMPlusObjectConstruct()

//Construct Method

protected override void Construct (string constructstring)

{

// Called right after constructor call if (constructstring. Trim () == "")

{

//Throw an exception

throw new NotSupportedException(

"Unable to read the database connection string");

else

//Copy the DB connection string to the private member strConnString = constructstring;

//Open the database connection public SqlConnection Connect)) {

sqlDBConn = new SqlConnection ( strConnString) sqlDBConn . Open ( ) ;

return sqlDBConn;

//Close the database connection

Ml

public void Disconnect)) { if (sqlDBConn != null)

//If the connection is open then close it if (sqlDBConn. State == ConnectionState.Open) sqlDBConn. Close () ;

Initially, we specify the COM+ application name and the key used for generating the strong name for the assembly. Then we use the ConstructionEnabled attribute to specify a default connection string for the COM4object construction.

[ConstructionEnabled(Default="server=Sruthi;uid=SecureWebUser; pwd=!23URL32~@@@!;database=Northwind;")] public class COMPlusObjectConstruct : ServicedComponent

The Construct method will be called automatically after the constructor of the class is called. In the Construct method, we read the connection string and copy it into the private member of the class. If the connection string is not available, an exception is thrown. After that, this class is very similar to the previous example.

Let's compile the component:

esc /target:library /r:System.EnterpriseServices.dll /out:bin\SecureDBConnection.Dll SecureDBConnection.es

and then register the component into the COM+ catalog:

Regsvcs SecureDBConnection.dll This will register the assembly into the COM+ catalog and the

default object construct will be configured.

When we consult the Activation properties of our component, from Component Services, we should see the following:

84

Securing Database Access

^^SS33^3£S3uuIES3SaSSSliS3Z. -ii-£i

F* Component supports events and statistics !"" Must be activated in eater's content

Cancel

With the default object construct string option, we can change the connection string very easily without recompiling the component. Although using this option will mean that there is no need to compile the component, and will thus afford us some flexibility, it is not particularly secure. For example, if a hacker was able to get to the assembly file, they could view it in the ILDASM utility:

As we can see, the default Object Construct value is shown as a hex value, which can be easily decoded by a malicious user. On top of this, the hacker can also use the COM+ Catalog Admin object model to read all the information about the component, including the object construct information. The one and only way to prevent this attack is to the secure your infrastructure and restrict access to the server.

Using Trusted Connections

The secret that is not shared with anyone is very safe. The same principal also applies to the database connection string. When we store the database connection string in a secure place, such as the registry or COM+ Object construct, there is a possibility that it can be stolen. The better way to address this problem is by using a trusted connection to the database using the OS account.

85

To use this functionality we need to add the ASPNET account into the SQLServer permissions list using the Enterprise Manager.

SQL Seivei Login Properties • New Login

General) Server Roles Database Access |

"3 ^ db_owner U $ db_accessadmin H f| db_securityadmin .J f-i? db ddladmin

We can then build a. database connection string using the following:

sqlDBConn = new SqlConnection("server=SRUTHI;database=NorthWind;Trusted_Connection=yes"); sqlDBConn.Open();

The above statement will create a connection to the database using the current user's identity, which is the ASPNET user account.

If impersonation is enabled, database connections will be created using the logged-in user's identity. So if you're planning on using trusted connections with impersonation then you need to grant access to all the users who need to log in to the application.

Although this sounds like a great option, trusted connections cannot be used in every scenario. For example, if your ASP.NET site is hosted in a shared environment and if you've added the ASPNET user to your SQL Server database, all the ASP.NET applications hosted in the server will have access to your database. Moreover, this is a Microsoft-specific solution, which means that integrated security can only be used to connect to a SQL Server. If your ASP.NET application needs to connect to a different database, such as Oracle on Windows or Oracle running on a Unix platform, this option will not work for you.

86

Securing Database Access

Using Stored Procedures to Control Database Access

Stored procedures are the best way to control database access from a web application. In addition to the usual advantages of using stored procedures, they offer considerable security benefits for an application. Instead of using dynamic SQL statements to insert, update, select, and delete from a database, we should wrap these statements in stored procedures. As with every technique, there's room for mistakes, so you need to take care to avoid opening security holes when using stored procedures. Let's see one of common mistakes made when using stored procedures:

I've seen lot of developers write stored procedures in which dynamic SQL statements are built and executed. This kind of dynamic SQL is also vulnerable to SQL injection attacks. A hacker can still inject SQL code to the stored procedure, so we need to validate user input if we are to avoid being the victim of SQL injection attacks.

The best option to go with is to write standard stored procedures, and make the input parameter size the same as the size of the corresponding columns. In this way, the hacker can't pass a lot of data or a huge block of text to the stored procedure.

When it comes to security, every enterprise-level database has its own way of providing security. For example, MS SQL Server supports object-level security. The data manipulation statements such as SELECT, INSERT, UPDATE, and DELETE need table-level privileges to fulfill a query, and the SELECT and UPDATE statements have column-level privileges. The stored procedures need to have the execute privilege set so that they can execute.

These privileges can be granted using Transact SQL statements or using Enterprise Manager. For example, the following screenshot shows how to provide execute access to the stored procedure

"CustOrderHist".

Permissions

Object: ?CuslOrderHBt(dbo:

List ail usersAiser-defined database roles/publc

List only users/'user-defrned database roies/pubfe with permissions on this object

public

Help

MS SQL Server provides an option to encrypt the content of the stored procedure using the WITH ENCRYPTION clause in the CREATE PROCEDURE statement. Encrypted stored procedures are a great way to hide the source from prying eyes.

Although this encryption algorithm is well known, this can still protect the source of the stored procedure from casual viewing.

Consider the following example, in which we've created a stored procedure called Stp_Encrypted:

Stored Procedure Properties - New Stored Procedure

General

<New Stored Procedure)

Owner Create date:

lent:

;REATE PROCEDURE stp Encrypted

ID ncharfS;

SELECT ProdudNarne Total'.'

FROM Products P [Order Details] OD Orders 0 Customers C

WHERE C CustomerlD • @CuslomerlD

AND C CustomerlD • 0 CustomerlD

AND 0 OrderlD = OD OrdeilD

AND OD ProductID = P ProductID

GROUP BY ProductName

END 10

88

Securing Database Access

If we try to open the encrypted stored procedure in Enterprise Manager, we'll encounter an error message, and there is no way to decrypt the stored procedure.

Error 2058S: [SQL-DMO]/****** Encrypted object & riot transferable, and script can not be generated. ******;

Even if we try to use the Generate SQL Script option from Enterprise Manager, we will not be able to view the source of the stored procedure:

ET QUOTEDJDENTIRER OFF

GO

SETANSLNULLSOFF GO

SET QUOTEDJDENTIRER OFF

GO

SET ANSLNULLS ON

SO

So it is very important to store the source of the stored procedure in a safe place, such as in the Visual Source Safe database, so that the source code will be available when you need to update the stored procedure.

The Hacking Exposed Windows 2000 (ISBN 0-07-219262-3) book has an important chapter on hacking SQL Server. I'd highly recommend the book for SQL Server security.

Summary

In this chapter, we've investigated ways of securing access to our database. Securing a database is absolutely critical, and the importance of taking standard precautions such as changing the default database account, setting a good password, and storing the connection information in an appropriate place cannot be overestimated. Ideally, this information should be kept within a compiled .NET or COM component, or we should use a trusted connection. We also looked at the importance of using stored procedures in securing our database.

Implementing Password Policies

So far, we've seen how to avoid common vulnerabilities in a web application. Suppose you've addressed all of these issues in you system, and you feel that your web application is ready for the outside world. Here we encounter another problem area:

Even though your application might be very secure, if your password is weak, then anyone can get into the system by hacking your password. This does not apply simply to the admin password for the site, or the admin account for the server: this also applies to the user account passwords.

Suppose your site hosts web-based e-mail accounts, and you don't enforce good password management - hackers can easily get into users' e-mail accounts by guessing passwords, and read their personal information. We have to pay attention when specifying the password format, and we have to help users to create a good password. In this chapter, we'll look at ways of ensuring that our users use good passwords, and at general strategies for managing passwords.

What Makes a Good Password?

A good password is the one that is very hard to guess. People usually pick very common words for their passwords. For example, a pet's name, a spouse's name, their children's names, sequential numbers (123456), sequential strings of letters (MNOPQR), and so on. These types of passwords are very easy to break. The best ways to pick a password is to randomly combine the characters A-Z, a-z, 0-9 and few special characters. For example, the following are both good passwords:

a !9K$dAoi a

AJ1m2aOi!l@

These types of passwords are very had to predict, since they aren't based on any common words. However, they're very hard to remember, so people will often write them down, often in unsafe places. I've seen people writing their passwords on an adhesive note and pasting them on their monitors. In this way, even though you might have picked a password that is very hard to predict, the effort expended on picking the password is wasted.

Requiring a Minimum Length

Requiring a minimum length for a password is the first step towards safer passwords. For example, most of the web sites will require us to have a minimum of seven characters in a password. We can achieve this functionality with the custom validation control. Here is the custom validation method:

e.IsValid = true;

void validateLength (String sPWD) {

//Check the length of the password if (sPWD. Length < 8)

return false;

else

return true ;

As we can see in the following screenshot, this ensures that users must enter a password of at least seven characters:

3 Validating Password - Microsoft Internet Explorer

||Sj| http://localhost/chl2ypassword_C5.aspx

Validating Password

User Name: [4

Password:

Insert Data | he length of the password should be

92