0

I would like to take advantage of User Defined Types that can be created in a .NET assembly and then imported to SQL Server to perform a transparent operation like for example encryption. Encryption would be handled in Parse method while decryption in the ToString method. It would be a change on working system so I would like not to modify the code that already uses these tables.

Given I have a following table, where EncryptedString is a .NET type:

CREATE TABLE dbo.EncryptedStrings 
(ID int IDENTITY(1,1) PRIMARY KEY, EncryptedStringValue EncryptedString)

I can then insert values into that table, like it’s a normal string column - existing code could be maintained (Parse method in .NET code is then executed – which performs Encryption in my case).

INSERT INTO dbo.EncryptedStrings (EncryptedStringValue) VALUES ('AAA');
INSERT INTO dbo.EncryptedStrings (EncryptedStringValue) VALUES ('BBB');

But, when I do a simple SELECT:

SELECT * FROM dbo.EncryptedStrings

Then I receive binary serialized representation, like:

enter image description here

I need to explicitly invoke ToString or CAST it to VARCHAR to make ToString be executed:

SELECT ID, EncryptedStringValue.ToString() FROM EncryptedStrings

enter image description here

Is there any way to configure Server so that it always showed ToString representation, and the following SELECT queries always returned the same results?

SELECT ID, EncryptedStringValue FROM dbo.EncryptedStrings
SELECT ID, EncryptedStringValue.ToString() FROM EncryptedStrings
nan
  • 19,595
  • 7
  • 48
  • 80
  • You say that encryption is an *example* of what you want to do. For encryption, why aren't the built in transparent data encryption facilities sufficient. For something other than encryption, what's the benefit of using this CLR type that for all querying purposes seems to just resemble a `varchar`? – Damien_The_Unbeliever Nov 26 '13 at 13:52
  • @Damien_The_Unbeliever I wanted it to look like varchar but to perform my logic on insert and querying. What built-in transparent encryption facilities are you referring to? – nan Nov 26 '13 at 14:01
  • Those would probably be the built-in functions that have been in sql-server since 2005 http://technet.microsoft.com/en-us/library/ms179331(v=sql.90).aspx – dav1dsm1th Nov 26 '13 at 14:25
  • @nan I think this is as simple as it can get when using data encryption, The Built in sql server encryption service is much more complex and takes a lot more work to encrypt data. inserting data and retrieving data is not easy either, you have to Explicitly Open key to insert data and than Explicitly Close the keys after inserting data and you have to do the same when retrieving data from database Open and close keys. its not fun at all. – M.Ali Nov 26 '13 at 14:27
  • @nan - "Transparent Data Encryption" is an Enterprise Edition only feature from 2008. I don't think there is any way of doing what you want. Would creating a view that calls the `.ToString()` work for you? – Martin Smith Nov 26 '13 at 14:58
  • 1
    I'm obviously stupid... but if the value is always unencrypted when you access it (so that your last two selects produce the same result) - what's the point of encrypting? – dav1dsm1th Nov 27 '13 at 04:06
  • 1
    @dav1dsm1th - Transparent Data Encryption works in the same way. The encryption just protects against the plain text version being written to disc and being retrievable from reading the data pages in the files or backups. – Martin Smith Nov 27 '13 at 16:59
  • That makes sense. I forgot that access to the unencrypted version is controlled by the credential that is used to access sql... Obvious really. I said I was stupid. Thanks for the clarification. – dav1dsm1th Nov 27 '13 at 17:04

1 Answers1

1

There are a couple of options, at least one of which has been mentioned so far:

  1. Create a view that calls the appropriate method on the CLR UDT (e.g. ToString()).
  2. Create a computed (possibly persisted) on the table whose definition is a call to the appropriate method (see above)

Other than that, I don't think there's a way to say "call this method whenever this value is accessed".

Ben Thul
  • 31,080
  • 4
  • 45
  • 68