Searching the Entire SQL Server Database

Posted on September 8, 2011 by Ashiq Alibhai, PMP

Sometimes, like when you’re reverse-engineering a huge application, you need to do a free-form text search on the database. You know some value is stored in the database (albeit maybe as a partial string), you just don’t know where. It could be in any of thousands of tables and millions of records.

As it so happens, this Stack Overflow question answers exactly how to do this. You can create a stored procedure, and run it, like so:

SearchAllTables 'Linear Algebra'

I recommend you use the link, since it covers two variants on the script (the original, and a slower one that also searches integer fields). But in any case, here’s the full version. Enjoy!


CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END 
END

SELECT ColumnName, ColumnValue FROM @Results
END

Posted in Tools, Web, Wndows Forms | Tagged , | Leave a comment

Selecting Encrypted Columns in SQL Server

Posted on September 7, 2011 by Ashiq Alibhai, PMP

SQL Server has a wonderful (and sometimes annoying) feature where you can encrypt entire columns. If you’re interested in a bigger picture and more details on SQL Server and encryption, check out this uber long post I wrote.

We won’t go into that much detail here. Let’s instead breeze through what you need to know:

  • SQL server has symmetric and asymmetric encryption keys.
  • Keys can themselves be encrypted by “security certificates.”
  • Security certificates can be password-encrypted if you choose to do so.
  • Columns can be encrypted with a key.

This means that to encrypt (or subsequently decrypt) columns, you will need a key and a certificate. Generating these are beyond the scope of this post; let’s just assume you have an AllPurposeKey which is encrypted with an AllPurposeCertificate.

For example, let’s say you had a User table that had an encrypted BirthDate column. If you tried to select all the birthdays, you would see something like:


0x002A5D0CDC10391F25DA73EA80F6AE3801000000E5177115DF0DC30BFFE9AE ...
0x002A5D0CDC10391F25DA73EA80F6AE3801000000A5EE2DED63FCF9F85688BA ...
0x002A5D0CDC10391F25DA73EA80F6AE3801000000B231814527B6C75A520721 ...
...

Clearly, this is unusable. To decrypt it, select it with your key and certificate, and convert it into a date, like so:


OPEN SYMMETRIC KEY AllPurposeKey DECRYPTION BY CERTIFICATE AllPurposeCertificate
SELECT CONVERT(DATE, DecryptByKey(u.BirthDate)) AS BirthDate

And instead, you will see (as expected), the unencrypted dates:


1961-09-07 00:00:00.000
1978-11-22 00:00:00.000
1953-03-31 00:00:00.000
...

Easy breezy! There are benefits to encrypting at a database level, as there are drawbacks, but I leave it to you to puzzle those out.

Posted in Tools, Web, Wndows Forms | Tagged , | Leave a comment

Aggregate Objects in DevExpress

Posted on August 3, 2011 by Ashiq Alibhai, PMP

DevExpress has an amazing feature called “aggregation,” which is not explained terribly well in their documentation.

What is aggregation, and how does it work?

Simply, aggregation is two things:

  1. Cascading Delete: Aggregated objects can’t live without their parents; deleting the parent deletes the (aggregated) child. This is essentially cascading delete in database parlance.
  2. Aggregated Views: More importantly, fields for an aggregate object show up embedded in the parent’s form instead of linked by a drop-down.

Let’s say we had a Person class with an Address instance (associated 1:1). Without aggregation, when you add the address field to the person, you get an association drop-down, like so:

When aggregated, you will instead see all of the address fields on the Person detail view:

Aggregating an Object: Step by Step

So how do you create an aggregate object?

  1. Create the parent (aggregation-holder) and child (to be aggregated) classes, inheriting from BaseObject.
  2. Create a field in the parent with the child’s type, as you would with any other field type (eg. String).
  3. Add the [Aggregated] attribute on the field you just created.
  4. In the AfterConstruction method (still in the parent class), instantiate the new object (eg. this.Address = new Address(this.Session)).

And you’re done!

Troubleshooting Aggregated Objects

Q: I can’t write to any of the fields in the aggregated object!
A: In the model editor, make sure that you haven’t set AllowEdit to false for the field (under Items). The default is true. Additionally, make sure you're instantiating a new instance of the field in the AfterConstruction method, as described above.

Posted in Libraries, Web | Tagged , | Leave a comment

Excel-Style Auto-Filtering List Views in DevExpress

Posted on July 1, 2011 by Ashiq Alibhai, PMP

DevExpress automatically generates ListView and DetailView views for you. List views are great: they provide paging and sorting out of the box. But what about filtering? How can we implement complex, multi-faceted, Excel-style filtering?

The answer is surprisingly easy: simply toggle the ShowAutoFilter property on the ListView.

To set this:

  1. Double-click on your .xafml file to load up the model editor
  2. Uncheck the Views node (see screenshot)
  3. Find the list view you want to filter on, and select it
  4. Under Behaviour, look for the ShowAutoFilterRow property and set it to true.
  5. Rebuild and deploy your application.

You should now see an unintuitively blank row below your list view headers. Go ahead and type anything in and you should see the filters take effect, as well as the filter criteria (which you can click on to modify via a GUI).

That’s quite a lot of framework and scaffolding DevExpress is providing. I’m surprised how easy it is to allow dynamic, smart filtering on any field.

Posted in Libraries, Web, Wndows Forms | Tagged , | Leave a comment

Regular Expression Model Validation in DevExpress

Posted on June 29, 2011 by Ashiq Alibhai, PMP

DevExpress provides model validation out of the box. Yes, really. We’re going to briefly cover two types of validation: making a field required, and validating a field against a regular expression.

First, making a field required. Simply add the required attribute to your model’s field, like so:


[RuleRequiredField("RuleRequiredField for Car.Year", DefaultContexts.Save)]
public int Year { get; set; }

This code has a few pieces to it; it has a string name (I diverged from the default to add the model name in there); and it activates on DefaultContexts.Save, which occurs when you click Save or Validate.

Great. Now, how do we validate that the year is between 19xx and 20xx? With a regular expression, of course!


[RuleRegularExpression("Regex for Car.Year", DefaultContexts.Save, "(19|20)\d{2}", "Enter a year between 1900 and 2099")]

Arguably, you could cover this with a range validator, but in any event, we’ll pretend that’s not the case. The constructor for this attribute takes an additional regex to validate against the field value.

And beware; the RuleRegularExpression alone does not guarantee non-nullness. Although the regular expression itself does not allow an empty value, an empty car year will sail through validation; so ensure that you have both attributes.

Posted in Libraries, Web, Wndows Forms | Tagged , | Leave a comment

Forcing a Database Update with DevExpress

Posted on June 27, 2011 by Ashiq Alibhai, PMP

Sometimes, DevExpress doesn’t seem to pick up your model changes. This happens fairly frequently when building a new module. The build process you should follow is:

  1. Clean Solution: Right-click your solution, and pick Clean Solution.
  2. Update Models: Right-click your solution and pick Update Model on All Projects
  3. Build Solution: Press F6 to build the solution.
  4. Deploy: Deploy your module’s .DLLs to the \bin folder of the application.

If you’re following this process, there’s no chance of your changes not being picked up; there must be something else at play. If you suspect (or know, from peeking at the schema) that the schema is not being updated, you have two options:

  1. Force an Update: Do this by querying the ModuleInfo table and updating the Version column for your module to something really small (like 1.0.0.0).
  2. Obliterate the Database: Delete the entire database.

In either case, run the DB updater from your \bin directory: DBUpdater.v11.1.exe ..\Web.Config

Everything should be updated. Verify in SQL Management Studio.

Posted in Libraries, Web, Wndows Forms | Tagged | Leave a comment

Moving a SQL Server Database with Encrypted Content: SMK, DMK, and You

Posted on June 20, 2011 by Ashiq Alibhai, PMP

If you’ve ever had to move a SQL Server instance to a new location — especially when data is encrypted — the process can be quite a hassle. Moving the data is not a serious problem; the bigger problem is moving over the numerous keys used to encrypt and decrypt information.

But first things first. Let’s go over the entire process step by step. At a high level, we need to:

  1. Make sure we have the right tools installed
  2. Move the database over (schema and data, users, certificates, symmetric and asymmetric keys)
  3. Verify that everything works

So let’s dive into it!

Step 1: The Right Tools

To make this process smooth, make sure you have SQL Server Management Studio installed (comes with SQL Server 2008 R2). If you are using SQL Server 2008 express, you can download SQL Server Management Studio Express.

If you already installed SQL Server, and didn’t get Management Studio, just reinstall — run the installer again, and choose options to create a new instance. When the options appear, pick Management Tools. (SQL Sever won’t reinstall and add a new instance, it’ll just add Management Studio.)

Step 2: Move Data Over

Chances are that you’re working with a live database, and you can’t afford the downtime of detaching. If you can stomach the downtime, simply detach the database from the existing instance, and reattach it to the new instance, and you’re done!

Otherwise, failing that, use Management Studio to create a backup. (Be aware that any changes to the original database after you make the backup won’t be replicated.) To do this:

  1. Open SQL Management Studio (Express) and connect to the database
  2. Right-click the database and select Tasks > Back Up …
  3. Under “Destination,” add a new File destination (this is where it will create the dump)
  4. Under the Options page, check off “Verify backup when finished”
  5. Click OK, and wait.

Eventually, your backup will complete. Copy the file to the new machine, and launch SQL Management Studio (on the new machine).

  1. Create the new (empty) database.
  2. Right-Click and choose Tasks > Restore > Database.
  3. Under “Source for Restore,” choose “From device:” and click on [...]
  4. In the resulting pop-up, click on Add and choose your backed-up file.
  5. Click OK, then click OK, then wait.

You should see a message indicating the backup succeeded. Browse through some tables and verify that everything looks OK (smoke test).

Now the tricky part: if you have an application, stored procedure, or function that decrypts data, run it against the new database. The decryption will fail.

Why?

Step 3: Decryption and SQL Server Keys

SQL Server has a hierarchy of keys that it uses to encrypt, erm, other keys, and also security certificates and data. Check out this handy diagram below (c/o Four Guys from Rolla):

There are several keys here. The hierarchy is:

  • Service Master Key (SMK): This key lies at the root of the hierarchy. As implied by the diagram, this key is created when you install a SQL Server instance. It’s encrypted using among other things, data local on the computer SQL Server is installed on. This means that two keys on two computers will necessarily differ. This is important, because if we encrypt on one computer and decrypt on another computer, we need to have the same Service Master Key. This will pose a problem when moving the database to another SQL Server instance.
  • Database Master Key (DMK): This key exists in the database; SQL Server uses it to create other secure keys (it’s a public-private key encryption scheme). This key is encrypted using the Service Master Key, which, again, means that the encryption uses machine-specific information.
  • Security Certificates, Symmetric, and Asymmetric Keys: These are used to encrypt and decrypt data. They’re encrypted via the Database Master Key (DMK).

Now, when you encrypt or decrypt information in SQL Server, it uses a security certificate or symmetric key. Let’s say you have a certificate called FirstCert, which you use to create the symmetric key RedKey. To decrypt the encrypted Password column on the MyUsers table, you would write SQL like:


OPEN SYMMETRIC KEY RedKey DECRYPTION BY CERTIFICATE FirstCert
Convert(nvarchar, DecryptByKey([Password])) AS DecryptedPassword FROM MyUsers
CLOSE SYMMETRIC KEY StarKey

The first line says “Open this symmetric key, and decrypt it with this certificate; subsequent queries can use it to decrypt data.” Similarly, the last line closes the symmetric key. Without this, you’ll get the infamous SQL error (and likely you will also see):


Please create a master key in the database or open the master key in the session before performing this operation.

Yikes! What happened? We need to open our master key (DMK) before we can perform any cryptographic operations.

And here lies the problem: The current machine DMK cannot be used on data encrypted with another SMK. It will fail to decrypt, because the SMK has changed.

You can technically change the SMK and re-generate all the encrypted certificates, keys, etc. at the cost of some one-off CPU usage. But, the SMK is encrypted with a password; unless you know the password, changing it is impossible.

So what’s the alternative? Well, if you don’t have any encrypted information on your new database, you can backup and restore the key. But since this changes the SMK, the DMK and subsequent keys won’t be decryptable; so only do this if you don’t have any existing encrypted information in your target system.

Backing it up is described in this MSDN article as:


    BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file' 
    ENCRYPTION BY PASSWORD = 'password'

This will generate an encrypted file (you wouldn’t want to expose your SMK for even one second, would you?). Copy it over to the target machine, and restore it as per this MSDN article:


RESTORE SERVICE MASTER KEY 
    FROM FILE = 'path_to_file' 
    DECRYPTION BY PASSWORD = 'password';

Tada, that’s it! Now re-run your decryption SELECTs, and everything should work as they did before. You may need to specify FORCE to obliterate the old key and go ahead with restoring the SMK.

Posted in Tools, Web | Tagged , , | 2 Comments

Outputting Raw HTML in Razor

Posted on June 15, 2011 by Ashiq Alibhai, PMP

Razor does a great job of encoding content by default. This is a good thing. It means that you’re automatically protected from things like SQL injections and XSS vulnerabilities. Let’s say you have a todo-list application, where users can enter a task name. If someone writes a task name like alert('opening!');, they will see exactly what they entered — no javascript popup. You’re safe.

But what if you wanted to output the raw, non-encoded version of the content? For example, what if you want to allow users to write arbitrary HTML (which is dangerous) — say, for a CMS or blog application? This is precisely the situation I ran into with Planetary CMS.

The solution? Quite simple, really: instead of using @ to display content, use @Html.Raw(...).

But wait! ASP.NET serves up this amazing error message:

A potentially dangerous Request.Form value was detected from the client [...]

So there are two layers of security: one is that content is encoded by default, and the second is that potentially dangerous content will not be displayed (unless you explicitly give it permission to do so).

So how do you tell ASP.NET MVC3 that this is okay? Simply annotate your controller’s actions (in my case, it was only on the HttpPost version of the Create method) with ValidateInput(false). And everything will display as expected.

And of course, be careful when you’re doing this. Understand the security that you’ve unwound, and the potential implications of it. If you really need to do this, move forward, and be cognizant of what you are getting into.

Posted in Core .NET, Web | Tagged , , | Leave a comment

Understanding and Solving Named Pipes Provider Error: 40

Posted on June 6, 2011 by Ashiq Alibhai, PMP

Visual Studio emits a generic, verbose error message if it can’t connect to the SQL Server databse:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

The second sentence summarizes the issue: Visual Studio can’t connect to the SQL Server database. But why? Is it because the database is down? Is it a configuration error? What could it be?

Usually, this is caused by an incorrect or non-existent connection string. The first thing to do is check that you have a connection string defined. In Web.Config, you should see:


<configuration>
...
<connectionStrings>
    <add name="ConnectionString" connectionString="Integrated Security=SSPI;Pooling=false;Data Source=(local);Initial Catalog=Solution1" />
</connectionStrings>
</configuration>
...

If you don’t have a connection string, or if it’s commented out, then add one. Above is a sample that tries to connect to the local database.

But wait! Data Source=(local) is not a best practice. Actually, you should use (as our error message hinted) Named Pipes to connect. The equivalent connection string, with named pipes, would look like:

<add name="ConnectionString" connectionString="Integrated Security=SSPI;Pooling=false;Data Source=.\SQLEXPRESS;Initial Catalog=Solution1" />

In this case, we’ve replaced (local) with our SQL server instance name. We then need to double check that named pipes are enabled (they’re turned off by default in SQL Server).

To do that, load up the Sql Server Configuration Manager application. Under Sql Native Client 10.0 Configuration (32 bit), browse to Client Protocols, and set the Named Pipes attribute to true.

That’s all it takes! Restart SQL server, double-check your connection string, and you should be ready to go!

And if things still don’t work? SQL Server makes it quite difficult to connect to the database and verify that things are working (at least, with the pre-packaged tools). I recommend you download Toad for SQL Server. It will detect and connect to the local server, as well as give you the fully-qualified connecting string (machine name and instance name).

If you can view the database, tables, and run queries, then at least you know that SQL Server is running. Otherwise, you’re walking in the dark.

To summarize: this error can be caused by any number of reasons. The core message is that Visual Studio can’t connect to SQL Server. To remedy it, check your connection string, and use Toad for SQL Server to hit the database and make sure that it’s up and running (and to generate the connection string with the server and database instance names).

Posted in Core .NET, Web | Tagged , , | 4 Comments

WPF Brushes: Solid, Gradient, Image, and More!

Posted on May 9, 2011 by Ashiq Alibhai, PMP

If you’ve ever tried to set a foreground or background property for an object in WPF, you’ll notice that it takes an instance of the abstract type Brush. This cryptic class cannot be instantiated; so how do we use it?

The best resource is the MSDN overview of WPF brushes. It lists various sub-classes of brushes (visually!):

I won’t go into too many details (since the MSDN page does a great job of that); suffice to say that the main brushes you need to know about are:

  • SolidColorBrush: For a single, solid colour. Use new SolidColorBrush(Color.FromRGB(r, g, b)).
  • LinearGradientBrush: For a two-colour gradient. Use new LinearGradientBrush(Color, Color, angle) (where angle = 0 for horizontal, 90 for vertical).
  • ImageBrush: Uses an image as a (repeatable) pattern. Use new ImageBrush(ImageSource) (where ImageSource is probably a BitmapSource or DrawingImage).

Best of all is that, like the rest of WPF, these are vector-style brushes designed to scale up or down with your application control size. So use them with impunity, but test, test, test!

Posted in Core .NET, WPF | Tagged , | Leave a comment