CLR Integration in SQL

  • binak_86

    SSC Enthusiast

    Points: 194

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

  • sharky

    Default port

    Points: 1494

    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

    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

    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

    [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

    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

    Removing this posting and starting another thread

  • k4tay

    Valued Member

    Points: 74

    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

    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

    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: 997182

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • binak_86

    SSC Enthusiast

    Points: 194

    @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

    @jeff Moden

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

  • binak_86

    SSC Enthusiast

    Points: 194

    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

    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.

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

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