CLR Integration in SQL

Question

CLR Integration in SQL

binak_86

SSC Enthusiast

Points: 194
More actions
April 4, 2012 at 10:25 pm

#253563

Comments posted to this topic are about the item CLR Integration in SQL

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

sharky Default port Points: 1494 More actions · Answer 1

Although the article showed the implementation of CLR, the examples used can in my opinion lead to bad practices. I will use check constraints for validation without CLR or stored procedures, and used the allready build in encription instead of building your own.

binak_86 SSC Enthusiast Points: 194 More actions · Answer 2

Keeping in mind the scenario posted in the article, we are just developing the database, and not responsible for the applications which will be sending data, so in other words we are responsible for data validation, do tell what other ways can be used to implement validation without CLR? 🙂

David Data SSCrazy Points: 2965 More actions · Answer 3

The article describes how to do this in Open Visual Studio 2010.

Is it possible to do the same in the version of Open Visual Studio 2008 that comes with SQL Server 2008 BIDS?

binak_86 SSC Enthusiast Points: 194 More actions · Answer 4

[font="Tahoma"][font="Times New Roman"]Although I haven't tried to do this on VS 2008, but I think it should be do-able as I haven't used anything .NET 4.0 specific. Let me try this and get back to you.[/font][/font]

dukemus SSC Journeyman Points: 88 More actions · Answer 5

The only thing in that table that requires CLR is Email field to validate with regular expression.

How about pwdencrypt and pwdcompare? If I remember correctly these are available since SQL 7 (for sure since SQL 2000). These are the same functions used by sql to encrypt login passwords and results are 100% compatible with "alter login", "create login" etc.

If you use any other algorithm that allows password decryption, then there is no use to encrypt password as the key is also in database or CLR and just call the CLR and you have the results

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 6

jshahan

SSCarpal Tunnel

Points: 4622

April 5, 2012 at 7:22 am

#1469487

Removing this posting and starting another thread

k4tay Valued Member Points: 74 More actions · Answer 7

I am having some issues deploying my CLR project but after reading this article i suspect it has to do with my project must be using version 4 of the framework where this article specifically states to be sure and change it to 3.5. Can soneone comment as to why this was required? Ill be checking my project once i get into the office.

sharky Default port Points: 1494 More actions · Answer 8

I see what you have done, and the example is perfectly valid for demonstration purposes, and well written. I just want to emphasize best practices.

There might be a need to write you own .net security class code to encrypt the password at the client or middle tier to keep the decrypted one away from the database, but writing a CLR to do that then defeats the purpose. You should use passphrase/keys , and not CLR code for that.

Also easier and more manageable to create check constraints rather than stored procs for validation.

Nadrek SSC-Insane Points: 20039 More actions · Answer 9

Interesting article, but I would note that the email address validation isn't the example I would have chosen.

An actual RegEx to validate email per RFC822 is at http://www.ex-parrot.com/pdw/Mail-RFC822-Address.html; it's significantly complex.

Also, if the example is to transparently handle validation, then any password hashing must be handled at the client. Passwords should also be hashed, not encrypted, and many, many times; that's a whole separate article.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 10

binak_86 (4/5/2012)
Keeping in mind the scenario posted in the article, we are just developing the database, and not responsible for the applications which will be sending data, so in other words we are responsible for data validation, do tell what other ways can be used to implement validation without CLR? 🙂

Gosh, there have to be a thousand different ways and most of them will fit into simple check contraints and simple T-SQL comparisons using a Where clause. Format validation of things like an email address and telephone number aren't complex in T-SQL at all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

binak_86 SSC Enthusiast Points: 194 More actions · Answer 11

@sharky

Thanks for the appreciation and agreed on the best practices. Also, this was just for demonstration purposes. I am sure there must be scenarios where CLR would be better than any other approach. But I wanted to give a scenario which could be understood clearly as my focus was on CLR integration.

But I agree with your point. Also, passwords should always be hashed and not encrypted. And wherever there is a better and easier way to do something, one should opt for the easier method than the complex one.

@Nadrek

Thanks for liking the article.

I agree that passwords should be hashed. But what exactly do you mean by many, many times?

binak_86 SSC Enthusiast Points: 194 More actions · Answer 12

@jeff Moden

How can I do validations of email address through T-SQL?

binak_86 SSC Enthusiast Points: 194 More actions · Answer 13

k4tay (4/5/2012)
I am having some issues deploying my CLR project but after reading this article i suspect it has to do with my project must be using version 4 of the framework where this article specifically states to be sure and change it to 3.5. Can soneone comment as to why this was required? Ill be checking my project once i get into the office.

I am sorry I should have mentioned this in the article.

The answer to your question can be found in the following error, which VS 2010 gives me:

Note: If you do not have the .Net Framework version 3.5 installed on your development computer, you must install it if you want to develop SQL CLR assemblies for SQL Server 2005 or SQL Server 2008. SQL Server 2005 and SQL Server 2008 support only those assemblies that target the 2.0, 3.0, or 3.5 version of the .Net Framework. You specify the .Net Framework version in the project properties.

I am just curious, what exactly are you using CLR integeration for, if you don't mind sharing? 🙂

binak_86 SSC Enthusiast Points: 194 More actions · Answer 14

binak_86 (4/5/2012)
[font="Tahoma"][font="Times New Roman"]Although I haven't tried to do this on VS 2008, but I think it should be do-able as I haven't used anything .NET 4.0 specific. Let me try this and get back to you.[/font][/font]

I think I did not get your question the first time I read it, reading it again:

Visual Studio 2008 installs .Net Framework 3.0/3.5, so not only this can be implemented in VS 2008, but also there will be no need to change the framework version.

Do tell me if you face any problem with this.