SQL.CLR Standard Edition v.1.0.2.801 Released

SQL.CLR Standard Edition v.1.0.2.801 has been released. Included in this release is support for the output parameter type and SQL Server project integration.

SQL.CLR Standard Edition
SQL.CLR Standard 2008 Edition

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]

Code Validation with Digital Signatures

One of the advantages of digital signatures is to authenticate certain information. How this is normally accomplished is that a person would use their private key to encrypt a given document or the document's fingerprint. Initially a hash function would be applied to the document generating a message digest. The user would then encrypt the message digest using their private key. That way if the recipient of the document wishes to authenticate that it has not been tampered with they could use the public key of the sender to decrypt the message digest. Then using the same hash function on the message the two results could be compared to determine if they match. If so, then the recipient can be ensured that the document has not been altered.

Situations can occur where two different messages result in the same message digest. This is called a collision.

Adding a digital signature to code in SQL Server provides a way to ensure that the code has not been tampered with. To check if a stored procedure, trigger, or assembly is authentic one can query the sys.crypt_properties table. The thumbprint column will contain the value of the signing certificate's thumbprint for the object that has been signed.

For Example, I have a stored procedure getEmployees that has been signed by Certificate Cert_01.

The following query shows that getEmployees has been signed.

SELECT
OBJECT_NAME(crp.major_id) 'Procedure',
cer.name 'Certificate',
crp.thumbprint
FROM
sys.crypt_properties crp INNER JOIN sys.certificates cer
ON crp.thumbprint = cer.thumbprint

Results:

Procedure Certificate thumbprint
--------- ----------- -----------------------------
getEmployees Cert_01 0xE879E7ECF3702DE6D27A9A28E8B7052D77695E41

Now if I alter the stored procedure getEmployees no rows are returned.

Results:
Procedure Certificate thumbprint
--------- ----------- -----------------------------
(0 row(s) affected)


Given that the procedures was changed, the digital signature has been dropped by SQL Server.

SEA: Did You Know?

Cool feature even I forget about!

Did you know...that from the object explorer in SEA, you can select an object and drag-and-drop it to the query window? Nice!

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.”

SQL.CLR Gets a Fresh Coat of Paint

Along with some minor tweaks, SQL.CLR, our CLR procedure generator for SQL Server, got a makeover.
SQL.CLR Product Page

SQL.CLR now has a more "wizard" look-and-feel to it that is common to most tasks in SSMS. The window size was reduced to make it more manageable, added a welcome screen, changed some of the option locations to group them into a more logical order. Little things to make the app easier to use.

But, the new paint job was the big one...hope you like it!

Service Broker Assistant : The Feature List

As we get closer to our initial release date, I wanted to put together a preliminary feature set for the product. Service Broker is a rich and flexible architecture, so what we are attempting to do is separate some of the basic object creation from some of the more interesting application building functionality.

Both standard and professional editions will have basic creation features i.e. CREATE SERVICE, CREATE QUEUE, etc., as well as ALTER functionality. Plus, they will each be able to configure service broker.

Some of the more advanced options will come in the professional edition. Things like "Common Task Automation" and "Poison Message Detection and Removal". These would include deactivating Service Broker message delivery, pausing service broker networking and configuring initiating and targeting service security.

An important part of building service broker applications is the monitoring of the services. The professional edition will supply some basic monitoring features, performance monitoring and event tracing.

As I said earlier, this is a preliminary sketch of the features we hope to include in the coming release of Service Broker Assistant, but check back for more updates as the release approaches.

FYI: We are still targeting late Q2 for the release :)