SnipStorm Explorer Released!

SQL Server T-SQL Script Addin All your scripts a click away...



Many database developers have T-SQL scripts scattered across directories and folders. Finding that one script that you need can be frustrating and time consuming. Enter SnipStorm Explorer, a Windows explorer for T-SQL scripts, or snippets. This tool only displays snippets that are stored on your hard drive for easy access and quick viewing.

Online storage and access with SnipStorm.com

Available as Windows desktop app and SSMS addin
















From now to October 1 ,2009...SnipStorm Explorer is 50% off!!!

Just enter the discount code: SnipStormRelease at checkout.

You’ve Generated a CLR Procedure, Now What?

SQL.CLR makes it easy to generate CLR stored procedures and user defined functions. Just select then object and click go! One nice feature is that you can select the options to create a project and/or compile an assembly for deploying to SQL Server. This offers 2 ways to deploy the CLR object.

First, by creating a SQL Server project, the code generated from the existing T-SQL object can be modified to add, edit or remove any custom business logic. This is where CLR really provides us with tremendous flexibility when designing our logic on the data. We have the entire .NET base class library, not to mention any custom libraries we have developed, to leverage in our project. Once we have made the changes to our code, we can simply right-click our project and select “Deploy.”


The second option is to have SQL.CLR compile an assembly from the existing object. This means that we will not modify any existing logic in the code. From here we need to open SSMS and run a few lines of T-SQL to continue the deployment. Create an assembly in the database you wish to store the code for the object.

CREATE ASSEMBLY [Assembly_Name] FROM '\\[Path_to_assembly]\[Assembly_Name].dll' WITH PERMISSION_SET = SAFE

Next create the object, a stored procedure in this example with the format of:

CREATE PROCEDURE [Procedure_Name] AS EXTERNAL NAME [AssemblyName].[Namespace.ClassName].[MethodName]

Now that we have deployed our assembly, created the stored procedure all that’s left is to execute the stored procedure…

EXEC [Procedure_Name]

Case of the SQL Encryption Mondays

While we were developing SQL Encryption Assistant (SEA), we discovered an interesting if not scary tidbit with the encryption process. It involves encrypting a column of data with a symmetric key. Here’s the setup: A simple 3 column table, we’ll call it CUSTOMER_DATA. The columns are as follows:

• CUS_ID – INT identity column
• CUS_NAME – VARCHAR customer name
• CUS_SSN – NVARCHAR(MAX) customer social security number

CREATE TABLE [dbo].[CUSTOMER_DATA]
(
[CUS_ID] [int] IDENTITY(1,1) NOT NULL,
[CUS_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CUS_SSN] [nvarchar](MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [PK_CUSTOMER_DATA] PRIMARY KEY CLUSTERED
(
[CUS_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Add a row of data to the table.

INSERT INTO dbo.CUSTOMER_DATA (CUS_Name,CUS_SSN,CUS_DOB)
VALUES ('New Customer','123-456-789','11/12/1970')

Using SEA, we created a password-encrypted symmetric key named ColumnEncryptionKey. This key will be used to encrypt a column of data in the table. (Can you guess which one?) Here’s what the T-SQL would look like:

CREATE SYMMETRIC KEY [ColumnEncryptionKey]
AUTHORIZATION [dbo]
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD = N'1234qwer'

Now, using that symmetric key we encrypt the column of data. To do this we need the decryption password of the key. No problem. But, as usual, It’s a Monday and I haven’t had enough “fill-in-the-blank with your favorite caffeinated concoction”, and I ham-sandwiched the password. Doh! No problem, I’ll just enter it again, c-o-r-r-e-c-t-l-y and our column of SSNs will be safe. But wait, there’s more! There’s a red message from SSMS. What could it be saying…


Msg 15313, Level 16, State 1, Line 1 The key is not encrypted using the specified decryptor.

Right, got it. I know. Wrong password. But, that’s not the interesting piece. Next line, please (drumroll)...

(1 row(s) affected)

WHAT!?!?! It didn’t update the…I entered the wrong…what the…who the...why would it do that? Let me check the table.

SELECT * FROM dbo.CUSTOMER_DATA



Noooooo! It nulled out the data! Yep, turns out if you enter the wrong decryptor, SQL Server goes ahead anyway with the failed encryption. Your data is gone. So, how do we fix this? One, don’t enter the wrong decryptor. Excellent, I’ll try and be more perfect next time. Or, use SEA. It will not let this happen and save you from yourself when you have a case of the “Mondays.”

Certificates in SQL Server 2005

The most common uses for certiciates in SQL Server are to sign code, encrypt symmetric keys or encrypt data. The most compelling reason to use certificates for encrypting keys, is that they can be backed up and restored.