I'll be presenting at the Northern NJ .NET User Group meeting on Tuesday February 9.  Topics we'll cover include:

• Intro to SQL Server 2008 Integrated Full-Text Search (iFTS) features and functionality, including:
• Full-text indexes
• Thesaurus
• Word breakers, filters and stemming
• Multilanguage support
• Intro to SQL Server 2008 FILESTREAM storage
• Troubleshooting with iFTS dynamic management views and functions
• Building a simple and powerful .NET-based search engine-style interface

Click here for more information: http://www.setfocus.com/n3ug/welcome.aspx

See you there!

A common question on the newsgroups is "how do you encrypt data in a .NET [or other] client application and then decrypt it on SQL Server [or vice versa]?" I actually ran down my list of answers to someone who asked this in the newsgroups a few weeks ago. I won’t get into the details, but the answers all pretty much say the same thing -- theoretically you could make it work (with a lot of assumptions on your part), but it won’t be easy -- and probably not worth the investment of time and energy, to be honest. Now it’s time to change my answer.

You see, when this question is brought up the people who ask usually make a specific point to ask about symmetric encryption (AES, Triple DES, etc.). You can’t easily make the “encrypt on client/decrypt on server” scenario work with symmetric encryption because SQL Server doesn’t let you import or export symmetric keys.

Asymmetric encryption is an entirely different beast. Someone asked about sending a password to SQL Server securely (not in plain text) for FIPS compliance here.

Since passwords are usually pretty short I told the poster asymmetric encryption might solve his problem. Then I decided to prove it. The code below (both T-SQL and .NET) demonstrates. All of the steps should be performed in order. The .NET code at the end needs to be put into a C# Windows Forms or Console project of your own (.NET 2.0 or higher only).

1) T-SQL: Create a test database, database master key, and certificate on SQL Server

-- Create a test database

CREATE DATABASE Test;

GO

-- Switch to the new test database

USE Test;

GO

-- Create database master key

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'P@$$w0rd';

GO

-- Create a test certificate

CREATE CERTIFICATE TestCert

   WITH SUBJECT = 'Test Certificate',

   EXPIRY_DATE = '20151231';

GO

-- This statement just tests the new certificate to make sure

-- it's installed correctly

SELECT ENCRYPTBYCERT(CERT_ID(N'TestCert'), 'abcdef')

GO

2) T-SQL: Backup the certificate (public key only) to a .cer file in the file system

USE Test;

GO

-- Backup the certificate to a .CER file; assumes c:\Temp

-- directory exists

BACKUP CERTIFICATE TestCert

TO FILE = 'c:\Temp\TestCert.cer';

GO

3) T-SQL: Create a stored procedure that uses the certificate to decrypt a binary string passed into it

-- This procedure uses the SQL certificate to decrypt the

-- encrypted password

CREATE PROCEDURE dbo.DecryptPasswordWithSqlCert

      @EncryptedPassword binary(128)

AS

BEGIN

      SELECT CAST

            (

                  DECRYPTBYCERT

                  (

                        CERT_ID('TestCert'),

                        @EncryptedPassword

                  ) AS nvarchar(100)

            ) AS DecryptedPassword;

END;

GO

4) .NET: Create an X509Certificate2 object and use the public key to encrypt a string password; Call the stored procedure with the encrypted password and use the SQL Server certificate to decrypt it

        // Load the certificate from the file system and create an RSACryptoServiceProvider

        // from the certificate Public Key to encrypt data

        private RSACryptoServiceProvider GetCryptoProvider

        (

            string CertificateFilename

        )

        {

            X509Certificate2 cert = new X509Certificate2(CertificateFilename);

            RSACryptoServiceProvider r = (RSACryptoServiceProvider)cert.PublicKey.Key;

            return r;

        }

        // Encrypts string password (Unicode) with the RSACryptoServiceProvider

        private byte[] EncryptPasswordWithFileCert

        (

            RSACryptoServiceProvider Rsa,

            string Password

        )

        {

            // Results of RSA encryption are limited to 128 bytes

            byte[] Bytes = Rsa.Encrypt(Encoding.Unicode.GetBytes(Password), false);

            byte[] Result = new byte[128];

            // Need to reverse the order of the encrypted bytes for SQL Server encryption

            for (int i = 127; i >= 0; i--)

            {

                Result[127 - i] = Bytes[i];

            }

            return Result;

        }

        // Connects to server/database and executes stored procedure

        // The stored procedure decrypts the encrypted password you pass in

        private string DecryptPasswordWithSqlCert

        (

            string ConnectionString,

            byte[] EncryptedPassword

        )

        {

            string DecryptedPassword = "";

            using (SqlConnection Con = new SqlConnection(ConnectionString))

            {

                Con.Open();

                using (SqlCommand Cmd = new SqlCommand("dbo.DecryptPasswordWithSqlCert", Con))

                {

                    Cmd.CommandType = CommandType.StoredProcedure;

                    // Pass in the encrypted password

                    Cmd.Parameters.Add("@EncryptedPassword", SqlDbType.Binary, 128).Value = EncryptedPassword;

                    // Return the decrypted password as a string

                    DecryptedPassword = (string)Cmd.ExecuteScalar();

                }

            }

            return DecryptedPassword;

        }

        // This is my connection string

        private string SqlConnString = "DATA SOURCE=(local);INITIAL CATALOG=Test;INTEGRATED SECURITY=SSPI;";

        private void QuickTest

        {

            // Create RSACryptoServiceProvider from .cer file

            RSACryptoServiceProvider Rsa = GetCryptoProvider("C:\\Temp\\TestCert.cer");

            // Encrypt the password with the file certificate public key

            byte[] EncryptedPassword = EncryptPasswordWithFileCert(Rsa, "Test*Password123");

            // Decrypt the password on the server

            string DecryptedPassword = DecryptPasswordWithSqlCert(SqlConnString, EncryptedPassword);

            // Output the decrypted password

            Console.WriteLine(DecryptedPassword);

        }

A couple of items worth noting about this code:

* SQL Server (and .NET) asymmetric encryption function have a strict limit of 128 bytes that can be returned by the encrypted result. The encryption functions add 11 bytes of padding, so you’re automatically down to 117 bytes of plain text that can be encrypted or 58 Unicode characters. You can work around these limitations by encrypting your data in chunks, but I wouldn’t advise it -- asymmetric encryption is expensive in terms of time and resources.

* For some reason SQL Server needs the .NET asymmetric encryption results reversed, byte-for-byte. Not sure of the exact reason for this, but it’s simple enough to handle (as I did in the code) with a for loop on the .NET side.

* The BACKUP CERTIFICATE statement in the sample code only exports the certificate Public Key, which is used for encryption. You can also export the Private Key (for decryption) if you wish, but there’s no need in this scenario. You’ll need to look up the syntax of the BACKUP CERTIFICATE statement in BOL if you need to export your certificate’s Private Key.

* The .NET X509Certificate2 class is used in the code sample, and it is only supported on .NET 2.0 and higher. The older .NET X509Certificate class won’t do the job because it is lacking some features that this code sample requires.

I ran into an issue the other day and I needed a solution for automically configuring my SSIS packages from securely stored DBMS connection strings. Problem is that most DBMSs don’t support Integrated Authentication—they require a username and password. Storing the username/password combo in the connection string in plain text is a security risk, so the question becomes an issue of storing this information securely.

There are several ways to store configuration data for SSIS packages. Probably one of the easiest and most popular methods is to use .dtsConfig files stored in the file system. This method presents some challenges when you want to secure usernames and passwords in the files. I won’t go into the details, since this post isn’t about .dtsConfig files, but Jamie Thompson does a great job of running down the .dtsConfig file options over at his old blog: http://consultingblogs.emc.com/jamiethomson/archive/2007/04/26/SSIS_3A00_-Storing-passwords.aspx.

After abandoning .dtsConfig files the idea of combining SSIS SQL Server configurations and cell-level encryption popped into my head. I created a quick proof of concept and tested it. Then SSIS guru Jamie Thompson pointed me over to a blog post from Larry Charlton (from way back in 2007!) at http://curionorg.blogspot.com/2007/05/encrypted-sql-server-ssis.html that is very similar to what I came up with. Kudos to Larry for a nice piece of code.  My concept is very similar to Larry’s, but with one major difference in implementation that I’ll cover below.

So the idea is that SSIS, by default, looks for a table named [dbo].[SSIS Configurations] to get its configuration data. This script builds the table:

CREATE TABLE [dbo].[SSIS Configurations]

(

      ConfigurationFilter   nvarchar(255) NOT NULL,

      ConfiguredValue       nvarchar(255) NULL,

      PackagePath           nvarchar(255) NOT NULL,

      ConfiguredValueType   nvarchar(20)  NOT NULL

);

GO

Here’s a breakdown of the columns that SSIS is looking for:

• ConfigurationFilter holds a name you assign to the configured value. In my sample package I named my entry “Password Connection String”.
• ConfiguredValue is the actual value. In the sample package this is the actual connection string with username and password.
• PackagePath is the SSIS package property path. In the example the connection string value is used to configure the connection string of an OLEDB Connection named “Password Login Connection”. The path is “\Package.Connections[Password Login Connection].Properties[ConnectionString]”.
• ConfiguredValueType tells SSIS the data type of your ConfiguredValue. In this case the data type is “String”.

INSERT INTO [dbo].[SSIS Configurations]

(

    ConfigurationFilter,

    ConfiguredValue,

    PackagePath,

    ConfiguredValueType

)

VALUES

(

    'Password Connection String',

    'Data Source=(local);User ID=TestLogin;Password=p@$$w0rd;Initial Catalog=Test;',

    '\Package.Connections[Password Login Connection].Properties[ConnectionString]',

    'String'

);

GO

Note: if you use the SSIS designer in BIDS to populate the table with connection strings, it will strip out sensitive information like passwords.

You can use SQL Server security to restrict access to this table, but for my purposes that wasn’t good enough. Due to IT policies I specifically had to obscure the plain text of the connection string since it contained a password. For that I decided to turn to SQL Server cell-level encryption. The trick is that SSIS needs to be able to read the data directly from the table as plain text, but it shouldn’t be stored as plain text.

Larry’s implementation uses the T-SQL EncryptByPassPhrase and DecryptByPassPhrase functions, both of which require you to store a plain text passphrase somewhere to encrypt and decrypt your data and limit you to Triple-DES encryption. But the main problem I have with this method is that storing a plain text passphrase somewhere doesn’t solve the security issue—it just passes the buck. The same problem is evident when you try to encrypt SSIS packages by password.

Make no mistake, encryption key management is the hardest part of encryption—it’s a simple case of Turtles All The Way Down. Personally I don’t want to take on the responsibility for managing plain text passwords and passphrases—I’d rather let the server do that for me. So my solution needs to eliminate the need to store and pass plaintext passwords and passphrases to encrypt and decrypt the secure data.

To secure data using cell-level encryption first we need to do a little setup. The first step is to create a database master key (DMK), as shown below.

Note: always backup new encryption keys and certificates as soon as you create them!

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Ma5t3rK3yP@55';

GO

Then we need to create a certificate that is secured by the database master key (DMK). For simplicity I named the certificate ConfigCert.

CREATE CERTIFICATE ConfigCert

       AUTHORIZATION TestUser

       ENCRYPTION BY PASSWORD = 'myT3stP@$$'

       WITH SUBJECT = 'Configuration Encryption Certificate',

       EXPIRY_DATE = '20201231';

GO

ALTER CERTIFICATE ConfigCert

WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'myT3stP@$$');

GO

Next we create a symmetric encryption key that’s protected by the certificate. I called the symmetric key ConfigSymKey.

CREATE SYMMETRIC KEY ConfigSymKey

       AUTHORIZATION TestUser

       WITH ALGORITHM = AES_256,

       KEY_SOURCE = 'I am the very model of the modern major general',

       IDENTITY_VALUE = 'Stella! Stella!'

       ENCRYPTION BY CERTIFICATE ConfigCert;

GO

Note: it’s a best practice to always specify a KEY_SOURCE and IDENTITY_VALUE in the CREATE SYMMETRIC KEY statement so you can recreate the same symmetric key when necessary. There is no BACKUP SYMMETRIC KEY statement.

Then we need to create a slight variant of the [dbo].[SSIS Configurations] table designed to store the encrypted configuration values, as shown below.

CREATE TABLE [dbo].[SSIS_Configurations_Encrypted]

(

      ConfigurationFilter   nvarchar(255)  NOT NULL,

      ConfiguredValue       varbinary(512) NULL, -- This column changed

      PackagePath           nvarchar(255)  NOT NULL,

      ConfiguredValueType   nvarchar(20)   NOT NULL

);

GO

The only difference between this table and the [dbo].[SSIS Configurations] table is the ConfiguredValue column. In the encrypted version of the table this column is varbinary instead of varchar, and it’s bigger than it’s unencrypted version. The reasons are that (1) all SQL Server encryption functions return varbinary data, and (2) the encrypted data will always be longer than the plain text.

The next step is to drop the [dbo].[SSIS Configurations] table and create a view with the same name.

DROP TABLE [dbo].[SSIS Configurations];

GO

CREATE VIEW [dbo].[SSIS Configurations]

AS

SELECT ConfigurationFilter,

      CAST

      (

         DecryptByKeyAutoCert

         (

            Cert_ID(N'ConfigCert'), NULL, ConfiguredValue

         ) AS nvarchar(255)

      ) AS ConfiguredValue,

      PackagePath,

      ConfiguredValueType

FROM [dbo].[SSIS_Configurations_Encrypted];

GO
 

The view uses the DecryptByKeyAutoCert function to automatically decrypt the ConfiguredValue column. This works because SSIS does not differentiate between the view and the table of the same name. This is a good thing, because it simplifies our process quite a bit. Our next step is to make sure that inserts and updates to the view properly update the underlying table with encrypted data. For this we create an INSTEAD OF INSERT, UPDATE trigger.

CREATE TRIGGER SSIS_Configurations_Trigger

ON [dbo].[SSIS Configurations]

INSTEAD OF INSERT, UPDATE

AS

BEGIN

    SET NOCOUNT ON;

    -- SQL treats updates as a logical delete followed by an insert

    -- So look at the deleted virtual table to see if anything was deleted

    DECLARE @IsUpdate char(1) = CASE WHEN (SELECT COUNT(*) FROM deleted) = 0 THEN 'N'

                                     ELSE 'Y'

                                END;

    -- Open the symmetric key

    OPEN SYMMETRIC KEY ConfigSymKey

    DECRYPTION BY CERTIFICATE ConfigCert;

    -- Do a merge

    MERGE [dbo].[SSIS_Configurations_Encrypted] AS target

    USING inserted AS source

    ON

    (

            target.ConfigurationFilter = source.ConfigurationFilter

            AND target.PackagePath = source.PackagePath

            AND target.ConfiguredValueType = source.ConfiguredValueType

    )

    WHEN MATCHED AND @IsUpdate = 'Y' THEN

        UPDATE SET target.ConfiguredValue = EncryptByKey(Key_Guid(N'ConfigSymKey'), source.ConfiguredValue)

      WHEN NOT MATCHED AND @IsUpdate = 'N' THEN

        INSERT

        (

            ConfigurationFilter,

            ConfiguredValue,

            PackagePath,

            ConfiguredValueType

        )

        VALUES

        (

            source.ConfigurationFilter,

            EncryptByKey(Key_Guid(N'ConfigSymKey'), source.ConfiguredValue),

            source.PackagePath,

            source.ConfiguredValueType

        );

    -- Close the symmetric key

    CLOSE SYMMETRIC KEY ConfigSymKey;

END;

GO

This trigger intercepts INSERT and UPDATE requests to the [dbo].[SSIS Configurations] view to automatically encrypt the ConfiguredValue column, which is then used to update the encrypted data in the underlying [dbo].[SSIS_Configurations_Encrypted] table. We can re-run the previous INSERT statement from the beginning of the article to test it.

INSERT INTO [dbo].[SSIS Configurations]

(

    ConfigurationFilter,

    ConfiguredValue,

    PackagePath,

    ConfiguredValueType

)

VALUES

(

    'Password Connection String',

    'Data Source=(local);User ID=TestLogin;Password=p@$$w0rd;Initial Catalog=Test;',

    '\Package.Connections[Password Login Connection].Properties[ConnectionString]',

    'String'

);

GO

A couple of quick SELECT queries reveal the results:

SELECT *

FROM dbo.[SSIS_Configurations_Encrypted];

GO

SELECT *

FROM dbo.[SSIS Configurations];

GO

The results from the first query show the ConfiguredValue is stored unreadable/encrypted. Querying the view returns the unencrypted plain text.

Making a clear fashion statement with the classic belt and suspenders, you can deny permissions to the underlying table and the view to those that don’t need to see it, and you can deny access to the symmetric key and certificate to those same folks. If someone gains access to the view or the table, they still need access to the encryption key and certificate to decrypt the contents. Without this access, anyone querying the view will see NULL in the ConfiguredValue column.

UPDATE: Andy L. pointed out that [dbo].[SSIS Configurations] is just the default name SSIS uses for your configuration entries table. You can actually change it in the designer at design time, or in SQL Server at build time. The designer lets you choose which table to point at. The important thing is that the columns need to have the correct names.

On the SQL Server public programming newsgroup someone recently posted a question about an SSMS error ("Cannot parse script. 'System.OutOfMemoryException' thrown.") I hadn’t encountered this error myself, but the workaround is to break up very large scripts (50+ MB) into smaller scripts. Adam Machanic posted a T-SQL Tuesday challenge to post a solution to a puzzling situation, so this actually gives me a good opportunity to share how I structure my own build scripts -- which avoids this issue entirely.

When I create database build scripts, I use the SQLCMD utility to run them from the command line instead of using SSMS or another tool. SQLCMD has its own commands, which it parses separately from SQL/T-SQL statements. These commands are not understood by SQL Server or other scripting tools like SSMS (exception: you can run SSMS in SQLCMD mode, but that’s another story). These special SQLCMD commands all start with a ":" at the front of the line.

The SQLCMD command that makes parent-child structured build-scripts possible is the ":r" or "run" command, which tells SQLCMD to run another script file from within the current script file. In the figure below I’ve set up a local directory structure with database object creation scripts in subdirectories:

The \Scripts directory contains a Create.All.Sql script. This script uses the SQLCMD run command to execute the Database\Create.Database.Sql script, the Create.All.Schemas.Sql script, and so on.  The Create.All.Schemas.Sql script calls the Person.Schema.Sql and Sales.Schema.Sql scripts in turn. The other Create.All.* scripts each call the object creation scripts in their subdirectories as well. Here’s what my Create.All.Sql script looks like:

/*
    Create All Items
*/


:r Database\Create.Database.sql
:r Schemas\Create.All.Schemas.sql
:r Types\Create.All.Types.sql
:r Tables\Create.All.Tables.sql 

Each :r command kicks off the next level of child packages in turn.

SQLCMD has another great feature known as scripting variables that you can use to create dynamic scripts. Essentially you define a scripting variable on the command line with SQLCMD's -v command line option. Now the way scripting variables work, they are replaced wholesale in your scripts with their replacement value. So if you define a scripting variable named environment you can replace it with a value like "Dev", "QA" or "Prod" anywhere it occurs in your script. This is great for making dynamic scripts that need to be built across multiple environments.

In the example I've used a scripting variable named database. You can set the value of the database variable from the command line with the -v option. In the example below I set the database variable to the value "Test".

The nice thing about the SQLCMD scripting variables is that once you declare them you can access them from the parent script you run (in this case Create.All.Sql) or from any child scripts that are run (like Create.Database.Sql, Create.All.Schemas.Sql, Person.Schema.Sql and Sales.Schema.Sql). Here’s the Create.Database.Sql script from the example:

/*

 Create database

*/

USE master;
GO


CREATE DATABASE $(database);
GO 

The scripting variable is accessed in the script with $(database). The scripting variable is replaced with its value by SQLCMD, so in the example SQL Server sees this:

/*

 Create database

*/

USE master;
GO


CREATE DATABASE Test;
GO 

One thing to keep in mind when you use this scripting pattern is that every script should end with the batch terminator (default is "GO").  If not you could end up with one script running into another and get some strange, not-very-helpful error messages.

Another thing you need to know is that scripting variables are replaced wholesale with their replacement text. This makes them very flexible, since you can replace text anywhere in the script with anything you want. It can also be dangerous if your script is run by someone with malicious intent. A malicious user can replace a scripting variable with T-SQL statements that could damage your data or database structure; so keep your scripting variable-enabled scripts out of the hands of potentially malicious users.

I've attached a sample ZIP file with the directory structure shown in the example above. The scripts build out a few database objects from the AdventureWorks sample database.

Vince threw out a question - how did I create my geeky Christmas card at http://sqlblog.com/blogs/michael_coles/archive/2009/12/22/merry-christmas.aspx?  For those who haven't seen it yet -- this is your spoiler alert -- go check it out before you read on.

The script in the blog draws a picture of the Grinch with the SQL 2008 Geometry data type.  Unfortunately the way I wanted to create it is a much better story than how I ended up making it.  Originally I wanted to take a Grinch image, write a little .NET app to read in the bitmap and convert all the image borders to vector graphics.  After I spent about 30 minutes working on the app to pick apart the raster image and find the edges I figured out it would be a heckuva lot easier to just fat-finger the coordinates in, especially for such a simple image.

I ended up just drawing a simple vector image of the Grinch in PaintShop Pro.  Then I opened up Excel and moved the mouse over every corner in the image and hand-keyed the coordinates into a spreadsheet.  I used Excel to create some concatenated string SELECT statements and copied/pasted it all into SSMS.

I did find out pretty quickly, by the way (and yes, I should have known this), that the y-coordinates in the PaintShop Pro window are the opposite of the Geometry data type.  That is, (0, 0) is the upper left corner and the y coordinates increase as you go down in PaintShop.  So my original Grinch image actually printed upside down.  That's why you see all the negative numbers in the final script :)

Anyway the story of how I made it is a lot less interesting than how I *wanted* to make it :)

The script below generates the geekiest Christmas card in the world.  To see it, follow these instructions:

(1) Copy and paste the script below into SQL Server Management Studio 2008
(2) Execute the script
(3) Look at the "Spatial Results" tab in SSMS

Merry Christmas!

Back in January 2007, SQL guru Itzik Ben-Gan posted a series of MS Connect enhancement requests concerning windowing function enhancements.  Those who have used the ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE() functions on SQL 2005 and 2008, you already know how useful they are.  They simplify code and can improve performance considerably over the alternatives, which usually include self-joins, temp tables and/or cursors in various combinations.

Well, the windowing functionality that you've seen in SQL 2005 and 2008 is just the tip of the iceberg.  The ISO SQL standard actually defines several additional options for these functions that SQL Server doesn't yet support. These additional options allow you to do some pretty amazing calculations. The ROWS and RANGE window subclauses that the standard defines allows you to perform "sliding window" calculations; the ORDER BY clause for aggregate functions which simplifies complex running sum (and other) calculations.

As a SQL developer or DBA, these enhancements will simplify your life.  But don't take my word for it - read Itzik's white paper at http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc.  Then let Microsoft know you want these enhancements in SQL Server by voting on Itzik's enhancement requests at the following links:

Just got confirmation from Missouri on their portion of the "Elevate America" program (http://www.microsoft.com/About/CorporateCitizenship/us/CommunityInvestment/participatingstatesmap.aspx).  Missouri started passing out vouchers through their Dept. of Labor and Industrial Relations on Nov. 2nd.  So far they've passed out about 25% of their total 24,000 vouchers.  The Missouri program has a state residency requirement.  To apply, follow these steps:

1.  Go to https://www.missouricareersource.com/mcs/mcs/default.seek and register.
2.  Click on the "Job Seeker" button.
3.  Register and login.
4.  After you register and login you'll be presented with an "Elevate America" tab.
5.  Click the tab and follow the online instructions.

Hurry, this free training opportunity for Missouri residents will probably be gone fast!

UPDATE:  On a side note, I just received confirmation that the State of Mississippi has exhausted their supply of these vouchers (in less than two weeks!)  Residents of other states need to be aware of when these will be passed out and how they can get them so the people who need them won't miss out!