DBA Tips Archive for SQL Server
No Title[an error occurred while processing this directive]
by Jeff Hunter, Sr. Database Administrator
This guide demonstrates how to use SQL Server Common Language Runtime (CLR) integration with SQL Server 2012. In this guide, the following two assemblies will be created using C#:
The Common Language Runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.
With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Developers use compiled OO languages like C# or Visual Basic .NET to write code and to have the code executed as if it were a T-SQL procedure, function, or trigger. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.
Managed code uses Code Access Security (CAS), code links, and application domains to prevent assemblies from performing certain operations. SQL Server 2005, SQL Server 2008, and SQL Server 2012 uses CAS to help secure the managed code and prevent compromise of the operating system or database server.
In SQL Server 2005, SQL Server 2008, and SQL Server 2012, the Common Language Runtime (CLR) is off by default. In an effort to improve security, Microsoft has turned many features "off by default". This is a big change from the old policy of turning every feature on so that developers weren't discouraged from using the feature due to difficulties in getting the feature to work.
In order to develop SQL CLR assemblies for SQL Server, you must have and utilize the .NET Framework 3.5 installed on your development computer. If you do not have the .NET Framework version 3.5 installed on your development computer, you must install it if you want to development SQL CLR assemblies for SQL Server. SQL Server 2005, SQL Server 2008, and SQL Server 2012 support only those assemblies that target the 2.0, 3.0, 3.5, or 4.0 version of the .NET Framework.
As mentioned in the Introduction, this guide will compile and build two assemblies using C# that will be integrated with SQL Server 2012. The source code for both assemblies is presented below.
Copy the source code files above to your source directory. For example:
There are two methods to compile the C# source code; Microsoft Visual Studio and the C# command-line compiler. This guide uses the C# command-line compiler.
To use the C# command-line compiler, navigate to the appropriate .NET Framework directory. For example:
From this directory, compile the two SQL CLR source code files into an assembly (dll).
In order to create an assembly with EXTERNAL_ACCESS (or UNSAFE) permission set, you need extra permissions in the database. This can be achieved by setting the TRUSTWORTHY bit in the database (ALTER DATABASE [DevDB] SET TRUSTWORTHY ON); however, this is not a preferred option as it can cause other undesired side effects. Note that if your assembly does not require resources outside of the database (for example, writing to a file), you do not need to assign an EXTERNAL_ACCESS permission set to the assembly in order to execute the SQL CLR in the database. You only need to assign an EXTERNAL_ACCESS permission set to the assembly when accessing resources outside of the database server.
If your assembly will need access to resources outside of the database, use the following method as the preferred alternative to setting the TRUSTWORTHY bit in the database as explained above.
Create a .NET strong name key file by using the sn.exe tool.
In the master database, create a master key (if one does not already exist).
Still in master, create an asymmetric key.
Also in the master database, create a login from the asymmetric key.
Give the login just created EXTERNAL ACCESS ASSEMBLY permission.
Build your assembly like before but this time sign it with your strong name key.
The assembly is now ready to be deployed. The assembly is signed with a strong name key, and the strong name key has an asymmetric key created from it and there is a login created from that symmetric key with the necessary permission set.
With the assemblies built, the next step is to create the assembly in a SQL Server database along with the external procedure and/or function wrappers to run the assembly.
Note that the external reference for creating the procedure and wrapper functions is in the form:
With the assemblies and procedures / functions wrappers created in SQL Server, run the following T-SQL to execute the assemblies.
The following T-SQL can be used to drop the previously created objects in the example Class.
Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.
Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at email@example.com.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
Last modified on
Tuesday, 26-Mar-2013 14:32:40 EDT
Page Count: 23408