Herve Roggero

SQL Azure - Auditing Choices

Herve Roggero — Sat, 07 Nov 2009 22:24:59 GMT

As I am digging more into SQL Azure, it seems choices for auditing will become a little bit more restricted.

Generally speaking there are four ways to audit SQL Server statements; these mechanisms are used by various software vendors to deliver auditing capabilities for compliance mandates and for security reviews. However as we will see, many of the products will stop from working for SQL Azure due to some limitations imposed by the database. At a high level, the four auditing mechanisms are:

Server-side Traces
Uses server-side traces to trace and store statements executed on a database server
Log File Auditing
Uses backups of log files to discover which statements were previously executed
Network Sniffing
Sniffing of network packets and storing SQL Server packets' content, including statements
Database Proxying
Captures all incoming network packets directly before forwarding them to the database server

It turns out that option number 1 is no longer available. Many SQL Server auditing products rely on this mechanism. Since server-side tracing stored procedures are no longer available, these products will not be able to audit SQL Azure statements.

Regarding option number 2, it seems that no option is available to obtain a backup of the log files. The BACKUP and RESTORE operations are not supported since cloud computing takes care of high availability concerns.

Regarding option number 3, packet sniffing will no longer work either since all direct connections to SQL Azure require SSL. As a result, all packets are encrypted and cannot be analyzed.

The only remaining option is to use a database proxy that handles the SSL handshake on both ends and stored the database statements going through. However, if a database connection can be made around the proxy, those statements will not be captured. While SQL Azure allows firewall settings to limit connections (by IP), it would be difficult to prove from an auditing standpoint that the firewall settings were not altered over time.

At this point at least, there appears to be no real silver bullet for auditing a SQL Azure database; at least not yet...

Still, most applications using the SQL Azure platform will not likely store any sensitive data, initially. As the SQL Azure platform grows in its use, I would expect some of the options above to be enabled, or new options to become available.

SQL Azure - Getting Started

Herve Roggero — Wed, 07 Oct 2009 15:16:07 GMT

I started to look into SQL Azure, the database cloud computing initiative from Microsoft, and I was completely blown away. Microsoft is providing an interesting option that strikes an interesting balance between a plain-old RDBMS implementation and a scalable platform. By definition, relational databases have difficulty scaling due to the many features and constraints that make them popular. However, SQL Azure removes some of those features in order to provide a more scalable SQL Server, while keeping intact its strong relational capabilities.

For example, outside of hashing, SQL Azure does not support encryption that uses certificates, which makes somewhat sense or those certificates would need to be deployed virtually everywhere. Considering that encryption is CPU intensive anyway, it is probably best to leave the encryption details to the consumer.

I was initially surprised that the USE <database> command didn't work, although it doesn't generate an error either. I guess every database created could be sitting on any hardware... which makes sense from a scalability standpoint.

Finally, I had to get used to sqlcmd.exe again... but it works like a charm. There are ways to get a SQL Azure database available through Visual Studio. However you should expect that not all the T-SQL commands are available, or fully supported. For example, none of the system procedures will work... (bummer!).

To get started, go to http://www.microsoft.com/azure/sql.mspx - from there you can sign up for the CTP.

Strong Password Hashing with SQL Server

Herve Roggero — Sat, 19 Sep 2009 16:39:14 GMT

While SQL Server security features continue to improve, hashing in SQL Server using native functions is simple, but not necessarily up to speed on the latest security specifications. The good news however, is that using extended stored procedures allows database developers to take advantage of the strength of .NET using the System.Security.Cryptography namespace.

I started to evaluate Hashing capabilities within SQL 2005 and 2008 and as it turns out, hashing is extremely simple using the HASHBYTES function. This function takes two parameters: the hashing algorithm (sha1, md5...) and the input parameter.

For example, the following code hashes a password. Pay attention to the Encoding chosen; the type of the variable dictates which encoding will be used (varchar and nvarchar).

DECLARE @password1 varchar(100) -- UTF8 Encoding
DECLARE @password2 nvarchar(100) -- Unicode
SET @password1 = 's3cret'
SET @password2 = 's3cret'
SELECT HASHBYTES('sha1', @password1)
SELECT HASHBYTES('sha1', @password2)

Output:

0xFEF341F85D87439E7D91A2D465B9871EF66B5E98
0xC06DCADF544BC3D6ECE7C64F485D2846E7A93F55

Unfortunately, SQL Server does not support any of the stronger hashing mechanisms, such as SHA256. In addition, storing passwords by simply hashing them is not considered sufficient as they are vulnerable to dictionary attacks; indeed, hashing a given input always yields the same output. To provide stronger hashing, one would need to obtain a cryptographically strong random byte array, known as a vector (or a salt value). To use stronger hashing algorithms and create vectors, we need to use the .NET framework through an extended function.

Using .NET Hashing

The objective of our .NET code is to generate a single output that is never the same (avoiding the dictionary vulnerability) and uses SHA256 as the hashing mechanism. The following shows how to use a .NET extended function that takes a password as an input and returns a byte array to SQL Server that contains both the hash and the vector.

Note that your SQL Server database should allow .NET execution. Here is the SQL statement used to enabled CLR on SQL Server:

sp_configure 'clr enabled' , '1'
go
reconfigure

The following T-SQL uses the strong .NET hashing functions found later in this post. The output is a byte array that can be stored as binary(48).

DECLARE hash binary(48)
SET hash = (SELECT dbo.pyn_encryption_hashPassword('s3cret'))
SELECT dbo.pyn_encryption_verifyPassword('wrongpwd', hash) -- returns 0
SELECT dbo.pyn_encryption_verifyPassword('s3cret', hash) -- returns 1

The above code makes it simple to offer a strong password hashing function that combines SHA256 and a vector. Since the vector is embedded in the password, there is no need to store the vector in a separate column.

Here is the .NET code that creates and verifies a password using a strong hashing method. The methods below use UTF8 encoding. To use Unicode, replace the method calls GetNonUnicodeBytes() to GetUnicodeBytes().

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBytes pyn_encryption_hashPassword(SqlString password)
{
// Create a strong vector
byte[] vector = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
System.Security.Cryptography.RandomNumberGenerator.Create().GetNonZeroBytes(vector);

// Get the password bytes
byte[] pwdBytes = password.GetNonUnicodeBytes();

// Add the vector bytes to the password bytes and hash them both at the same time
System.Security.Cryptography.SHA256Managed sha256 = new System.Security.Cryptography.SHA256Managed();
byte[] outputBytes = sha256.ComputeHash(AddBytes(pwdBytes, vector));

// Return the resulting hash, and append the vector again to it so it can extracted later
return new SqlBytes(AddBytes(outputBytes, vector));
}

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean pyn_encryption_verifyPassword(SqlString password, SqlBytes hash)
{
byte[] vector = new byte[16];
byte[] pwdAndHash = new byte[32];

// Split the hash and vector into separate variables
Array.Copy(hash.Value, 32, vector, 0, 16);
Array.Copy(hash.Value, 0, pwdAndHash, 0, 32);

// Get the password bytes that will be tested against the hash
byte[] pwdBytes = password.GetNonUnicodeBytes();

// Compute a hash using the password provided, and the vector extracted from the hash
System.Security.Cryptography.SHA256Managed sha256 = new System.Security.Cryptography.SHA256Managed();
byte[] testHash = sha256.ComputeHash(AddBytes(pwdBytes, vector));

// Compare hash values to determine if the password provided matches
return new SqlBoolean(BitConverter.ToString(pwdAndHash) == BitConverter.ToString(testHash));
}

private static byte[] AddBytes(byte[] array1, byte[] array2)
{
// Add two byte arrays
byte[] array3 = new byte[array1.Length + array2.Length];
Array.Copy(array1, array3, array1.Length);
Array.Copy(array2, 0, array3, array1.Length, array2.Length);
return array3;
}

};

Conclusion

While SQL Server hashing capabilities offer good support for simple hashing needs, extending SQL Server with .NET can provide a significant security advantage when it comes to encryption. Since the code above does not access any local resources on the database server, it can run in Safe permission mode.

Using SQL Server Hashing

SQL Saturday - South Florida on August 8th 2009

Herve Roggero — Mon, 13 Jul 2009 01:55:59 GMT

Scott Klein (http://geekswithblogs.net/ScottKlein/Default.aspx) and I are organizing the first SQL Saturday of South Florida, coming on August 8th 2009. It will be held in Miramar at Devry University. If you are in the area, please drop by! We have over 20 speakers and a great deal of giveaways. To find out more, please go to:

http://www.sqlsaturday.com/eventhome.aspx?eventid=20

Thanks

Herve

Data Masking - in-flight vs. at-rest

Herve Roggero — Mon, 29 Jun 2009 03:36:02 GMT

Many organizations are faced with the threat of data theft, from which legal battles, hefty fines and negative publicity can arise. Interestingly enough stealing data is not always that difficult. In this post we will review certain aspects of data masking, a technique used to disguise personable and sensitive information.

Data masking encompasses two key areas: in-flight and at-rest. In-flight data masking is different than encryption; the goal of this technique is to temporarily transform data from its original value before it is viewed on screen, or printed, depending on predefined security rules, such as a login name or Active Directory group. This is perhaps better described as a "need-to-know" transformation. The real data is not changed, but its representation is transformed based on who sees it. For example a supervisor may see a customer's full credit card information but a customer representative may only see the last four digits. While some applications may provide built-in support for similar features, most do not offer this capability.

At-rest masking is a technique typically used by corporations that need to restore backup copies of their production systems for quality assurance, package implementation testing or software development projects. This type of masking is typically considered much more difficult for many reasons, including process changes, testing tool and script compatibility, discovery of logical entity relationships within a database and across systems and more. For example, from a process standpoint, who in your organization would be responsible for defining and maintaining the data transformation of Social Security Numbers? Should a Social Security Number be transformed the same way on DB2, Oracle and SQL Server databases? How will testing batch processes be affected if cross-system data integrity is no longer maintained after data masking? Is it possible to stage data masking over multiple days depending on the size of each system? How often should non-production systems be refreshed?

In some cases data resides in flat files, XML documents, PDF documents (such as invoices) or images. Very few vendors seem to be able to mask data outside of what an ODBC driver can provide.

While in-flight data masking is technically difficult to achieve (and very few vendors are available), at-rest data masking may seem technically simpler but poses significant challenges that impact software development life cycle and package implementation projects alike. Perhaps the most difficult aspect of a masking project is to identify the business and technical requirements so that a solution can be picked, be it a vendor or a custom solution.

Some of the vendors that offer masking solutions include Compuware and IBM. There are a few other vendors that are more specialized if your masking needs are limited to Oracle for example. On SQL Server specifically, it is possible to devise a complex and effective masking solution using SSIS.

However the real complexity of the project is not the technology, but rather the process, as discussed above. My advice with this type of project is to carefully select vendors and possibly leverage consulting firms that may be able to guide you in the difficult task of gathering requirements and selecting a vendor.

Security versus Auditing

hroggero — Mon, 11 May 2009 03:46:58 GMT

I have seen an interesting issue surface recently, one that many other corporations are probably facing: the dividing forces of security and auditing. It could be argued that auditing practices should strengthen security, however this may depend on the situation. Let's take database access control as an example.

In a typical two-tier application, connections established to a database server are performed using a shared account. Usually, shared accounts are considered less secured than network accounts due to the lack of strong authentication offered by network services like Kerberos. Also, database accounts are considered an issue with database auditing since these are shared accounts: who really accessed my data?

So at first sight it might appear that database accounts are neither good for auditing nor for security.

However, let's consider these additional factors:

shared accounts used for application connections are usually kept secret; users do not know the user id/password of the database connection, and as a result can only see or act against the data through the application layer
when using network accounts (SSPI) to establish a database connection, a password is usually not required at the time of the database connection; the connection is granted automatically if the user is granted access; that's a benefit of Single Sign-On
database servers do not differentiate connections coming from users or from applications; the connection is granted if it is authorized
in many configuration settings, the accounts used through an application need to have complete access to the database so the application can perform its normal CRUD operations, while the application controls what users can do depending on application-layer access control rules

With those additional factors, we can see that configuring two-tier applications with SSPI can open significant security challenges. While corporations gain in auditing by knowing who is actually accessing which data, users can now technically connect directly to the database server without using the application, bypassing application layer security. This can create a significant security challenge and may leave databases open for attacks or unintentional data loss.

Can anything be done? With SQL Server 2005 SP2 and higher, administrators can start enforcing connection rules through the use of Logon Triggers. These triggers allow more control over the connection when it is taking place, such as verifying that the connection is indeed established from the application, instead of Excel for example. Also, database firewalls can be useful in limiting the use of database or network accounts, such as the one that my company creates.

This is an interesting topic and one that can be a real challenge for certain organizations. It appears finding the right balance between security and auditing may not be as straight forward as it seems. At least not in this case.

Presenting at SQL Server User Group - May 28 2009

hroggero — Mon, 11 May 2009 02:19:54 GMT

Well - I am pleased to announce that I will be speaking at the SQL Server User Group of South Florida this coming May 28 2009 in Miramar (at the Devry University Campus). This group is run by Scott Klein. The topic? It will be on SQL Server Performance covering SQL Server 2005 and 2008. I will be mostly talking about indexes, index strategies, execution plan analysis and tracing...

This month, our sponsor if TEK Partners - they will be bying Pizza... so there is no excuse... food, fun and networking!

So if you are in the area make sure to swing by!

SQL Server User Group - Upcoming

hroggero — Mon, 27 Apr 2009 01:00:47 GMT

For those living in the area, the next SQL Server User Group event is in West Palm Beach on Wesnesday (April 29 2009) and will host Andy Warren. If you are in the area, please make sure to come!!! For more information about time and location, please visit http://www.gcsqlgroup.com/. NOTE: The meeting will be held on April 29, not May 7.

The new location in WPB is:

CompTec

1750 North Florida Mango
Suites 302 & 303
West Palm Beach

See you there!