Demo

Demo Script

SQL Server 2008 CLR – New Supported .NET Libraries for Custom

Assemblies

Lab version: 1.0.0

Last updated: 11/10/2011

CONTENTS



OVERVIEW ................................................................................................................................................... 3

Key Messages ........................................................................................................................................... 3

Key Technologies ...................................................................................................................................... 3

Time Estimates ......................................................................................................................................... 3



SETUP AND CONFIGURATION .................................................................................................................. 4

Task 1 – Running the Configuration Wizard ......................................................................................... 4



OPENING STATEMENT............................................................................................................................... 6



STEP-BY-STEP WALKTHROUGH .............................................................................................................. 7

Segment #1 – Creating a User-defined Function that References a New Supported .NET Library ......... 7

Segment #2 – Deploying and using the User-defined function............................................................... 11



SUMMARY .................................................................................................................................................. 15

Overview

This document provides setup documentation, step-by-step instructions, and a written script for demonstrating the .NET libraries that were

added to the supported list and can now be referenced from your custom assemblies.



Key Messages

1. SQL Server has a list of supported .NET Framework libraries, which have been tested to ensure that they meet reliability and security

standards for interaction with SQL Server. These libraries guarantee no memory, resources or transactions leaks, even under low

memory or other exceptional conditions.

2. SQL Server 2008 adds new supported .NET Framework libraries to the list, such as the System.Core assembly (generic LINQ functionality)

and the System.Linq.Xml assembly (LINQ to XML). Therefore, if your SQL Server 2008 assembly make use of any of these libraries and

meets the necessary level requirements, it can be registered with the PERMISSION_SET=SAFE permission level.





Key Technologies

This demo uses the following technologies:

1. Microsoft Visual Studio 2008 Service Pack 1 Professional or Team editions

2. Microsoft SQL Server 2008 R2 Express (or higher)





Time Estimates

 Estimated time for setting up and configuring the demo: 3 min

 Estimated time to complete the demo: 20 min

Setup and Configuration

System Requirements

 Microsoft SQL Server 2008 R2 Express (or higher)

 Microsoft SQL Server Management Studio



 Microsoft Visual C# 2008 Express or Microsoft Visual Studio 2008 (with Visual C# feature)

You must perform the following steps to prepare your computer for this demo. After completing the demo, you can run the Cleanup.cmd script

to revert any changes made to your system. If you intend to present other demos in the training kit, avoid running this script until you have

completed them (the script will drop the SQLTrainingKitDB database used elsewhere in the training kit).





Task 1 – Running the Configuration Wizard

The following steps describe how to run the Configuration Wizard tool included with the demo to verify that all the prerequisites are properly

installed.

1. Browse to the setup folder in the Source folder of this demo, and run the Setup.cmd script. This script will launch the Configuration

Wizard for the demo. The Configuration Wizard is designed to check your computer to ensure that it is properly configured with all of

the dependencies needed to run the demo.

2. Click through the steps in the Configuration Wizard to get to the Detecting Required Software step; a scan of prerequisites will be

performed on your computer. If you do not have the necessary dependencies, install them using the links provided by the tool and

rescan your computer.

Figure 1

Dependencies check completed successfully





3. Once the required software is properly installed click Next to configure your computer for this demo. A script will be executed to create

the SQLTrainingKitDB database if it does not exist, and another to create the server alias ('SQLServerTrainingKitAlias') used in this demo

to establish a connection with the database.

Figure 2

Configuration Wizard tasks









Opening Statement

SQL Server has a list of supported .NET Framework libraries, which have been tested to ensure that they meet reliability and security standards

for interaction with SQL Server. These libraries guarantee no memory, resources or transactions leaks, even under low memory or other

exceptional conditions. Additionally, supported libraries do not need to be explicitly registered on the server before they can be used in your

custom SQL Server assemblies; SQL Server loads them directly from the Global Assembly Cache (GAC).

The user-defined functions, user-defined types, and user-defined aggregates you author in custom assemblies can make use of any of these

supported libraries, and if it meets the necessary security level requirements then be catalogued with the PERMISSION_SET=SAFE permission

level. SAFE is the recommended permission setting for assemblies that perform computation and data management tasks without accessing

resources outside an instance of SQL Server. They are as safe as T-SQL procedures. For more information about security level requirements, see

CLR Integration Programming Model Restrictions.

Unsupported libraries can still be called from custom SQL Server assemblies. However, before using them in your code you must first register

them manually in the SQL Server database using the CREATE ASSEMBLY statement. Nevertheless, any unsupported library that is registered and

run on the server should be reviewed and tested for security and reliability.

SQL Server 2008 adds new supported .NET Framework libraries to the list, such as the System.Core assembly (generic LINQ functionality) and the

System.Linq.Xml assembly (LINQ to XML). Other examples of supported .NET libraries are: System.dll, System.Data.dll, System.Xml.dll, and

System.Transactions.dll.

I would now like to walk you through a demo to show how a SQL Server 2008 user-defined function created within a custom assembly can make

use of the .NET Framework libraries added to supported list and be cataloged with PERMISSION_SET=SAFE permission level.



Note: For the complete list of supported libraries, see http://msdn.microsoft.com/library/ms403279.aspx.









Step-by-Step Walkthrough

This demo is composed of the following segments:

1. Creating a User-Defined Function that References a New Supported .NET Library

2. Deploying and Using the User-Defined Function





Segment #1 – Creating a User-defined Function that References a New Supported .NET Library



Action Script Screenshot

1. Open Microsoft Visual Studio 2008  Let us open our CLR User-defined

from Start | All Programs. function demo in Visual Studio. To

create a SQL CLR User-defined

2. Open the SupportedLibraries.sln functions you should use a SQL

solution located under the code Server Project template which let

folder in the Source folder of this you do automatic assembly

demo. deployment.









3. In the Solution Explorer, open the  Let us examine the main class with

Class1.cs class file. greater detail.



4. Show the FilterForAge SQL CLR  The class contains a single method

User-defined function. called FilterForAge, which receives

an age as parameter. This method

creates a list of persons that is filtered

by age. Only persons whose age is

greater than the one given as a

parameter are included.



 The method uses a Linq to XML

query to filter the list. To use Linq to

XML features we have to reference

the System.Linq.Xml library. This is

one of the new libraries added to the

supported list.



 The function is a public static method

that returns a special data type:

SqlXml that maps to SQL Server’s

XML data type.





5. Right-click the SupportedLibraries  Let us try to add a reference to the

project in Solution Explorer, and project, but first we have to create a

click Properties. connection to SQL Server.



6. Go to the Database tab, and click the  To create the connection we have to

Browse button under Connection go to the project’s properties, and

String. select the Database tab.

Under Connection String, we click

7. If available, select a database Browse and select or create a new

connection reference; or else create a database reference.

new one by clicking Add New

Reference.  Notice that by default the assembly

permission level is set to Safe, which

8. In the New Database Reference is the recommended permission

window, type setting for assemblies that perform

SQLServerTrainingKitAlias as the computation and data management

Server Name. Select tasks without accessing resources

SQLTrainingKitDB database in the outside an instance of SQL Server.

Select or enter the database name

combo box, and click OK.  Other possible permission levels are

External and Unsafe. External is

recommended for assemblies that

NOTE: If you are prompted to enable

access resources outside SQL

SQL/CLR debugging on the

Server. These assemblies have the

connection, click Yes.

same permissions as SAFE

assemblies, with the additional ability

9. Expand the Permission Level to access external system resources

combo box to show the available such as files, networks,

options. environmental variables, and the

registry. We will talk about UNSAFE

assemblies later.

10. Expand the References node in  If we check the references of the

Solution Explorer by double-clicking project, notice that both new

it. supported assemblies are being

referenced. System.Core that is

11. Right-click the References node and related to Linq and System.Xml.Linq

click Add Reference. which is related to Linq to XML. In

SQL Server 2005 those assemblies

were not in the supported list.

12. Scroll down the list to show the listed

assemblies and then click Cancel.

 Now let us try to add a reference.

Notice that there are only a few

assemblies in the list. This is because

only the assemblies from the

supported list are showed.

Sometimes, some SAFE assemblies

are not included in the References list

because Visual Studio has not been

updated to include them yet. In those

cases, the reference has to be added

manually by editing the .csproj file.



 Examples of other supported .NET

Framework libraries are System.dll,

System.Data.dll, and System.Xml.dll.



 On the other hand, examples of

unsupported libraries in SQL Server

2008 are

System.DirectoryServices.dll,

System.ServiceModel.dll, and

System.Linq.Data.









Segment #2 – Deploying and using the User-defined function



Action Script Screenshot





1. In Visual Studio Solution Explorer,  Now I will show you how you can

right-click the SupportedLibraries deploy the assembly with the user-

project and select Deploy. defined function created previously to

then be able to use it from a T-SQL

script.



 The Visual Studio SQL Server Project

provides automatic assembly

deployment. The Deploy option

uploads our project assembly to the

currently configured database,

previously compiled as a .dll file.



 It is important to notice that

assemblies are per-database. In

other words, they do not live at the

schema or instance level, they live at

the database level.

2. In Solution Explorer, right-click the  Let us see what happens if we

project and select Properties. change the assembly permission

level settings to UNSAFE and try to

3. In the Database page, change the deploy it again.

Permission Level to Unsafe.

 UNSAFE permission level gives

assemblies unrestricted access to

resources, both within and outside

SQL Server. UNSAFE level can also

potentially subvert the security

system of either SQL Server or the

common language runtime.

Therefore, UNSAFE permissions

should be granted only to highly

trusted assemblies.





4. Press CTRL+SHIFT+B to rebuild the  When we try to redeploy the

project. assembly, we get an error. This is

failing because only members of the

5. In Solution Explorer, right-click the sysadmin fixed server role can upload

SupportedLibraries project and assemblies with an UNSAFE

select Deploy. permission level.





6. Redeploy the library in Safe Mode as  Assemblies with the SAFE

seen previously. permission level can be deployed to

SQL Server 2008 directly. On the

other hand, assemblies with the

UNSAFE permission level require

elevation or specific database

configuration to be deployed.

7. Open SQL Server Management  Let us open the SQL Server

Studio from Start | Microsoft SQL Management studio to run the user

Server 2008. defined function.



8. In SQL Server Management Studio,  As an alternative to Visual Studio

open the SupportedLibraries.sql file auto-deploy function you can upload

located under the assests\sql folder the assembly manually by using the

in the Source folder of the demo. CREATE ASSEMBLY statement.

Then, by using the CREATE

9. Uncomment and show the code FUNCTION statement, we create the

located at the end of the script (it is function called dbo.FilterForAge that

not necessary to execute it). returns XML and corresponds to the

FilterForAge method of the assembly.



NOTE: To be able to execute the

statement, replace the  By default, the assembly is uploaded

%SourceFolder% placeholder with the with the SAFE permission level.

absolute path of the Source folder of Therefore, the WITH PERMISSION =

SAFE clause is not needed in the

this demo.

statement.





 We can upload our assembly with the

SAFE permission level because all

the assemblies we are referencing,

including the LINQ to XML assembly,

are in the supported .NET

Frameworks libraries list of SQL

Server 2008.



 We will not execute this code in this

opportunity because we have already

deployed the assembly using the

auto-deploy feature of Visual Studio.

10. Select the USE statement and click  If we query the content of the

Execute ( ). sys.assemblies system view, we can

see that our SupportedLibraries

T-SQL assembly lives in the database with

USE SQLTrainingKitDB the SAFE permission level.

GO





11. Highlight the SELECT statement

below and click Execute ( ).

T-SQL

SELECT * from sys.assemblies

GO





12. Select both sp_configure statements  The ability of SQL Server to execute

(located at the top of the script) and CLR code is off by default. To use the

click Execute ( ) to configure the user-defined function provided by the

SQL Server instance to run user assembly, the first thing we must do

assemblies. is guarantee that we can run user

assemblies in the SQL Server

T-SQL instance.

sp_configure 'show advanced

options', 1  To do this, the 'clr enable' option

RECONFIGURE should be set to one. This is an

GO advance option so we first have to

sp_configure 'clr enabled', 1 configure the instance to show

RECONFIGURE advanced options with the 'show

GO advanced options' option.

13. Select the USE statement and click  Now, we can invoke the function and

Execute ( ). get XML back. This will return a result

set with one column that is an xml

T-SQL fragment.

USE SQLTrainingKitDB

GO





14. Highlight the SELECT

dbo.FilterForAge statement below

and click Execute ( ) to invoke the

FilterForAge user-defined function.

T-SQL

SELECT dbo.FilterForAge(25);

GO





15. Click on the XML column returned

and display the XML data.









Summary

In this demo you saw that a SQL Server 2008 user-defined function that was created within a custom assembly, can use the .NET system libraries

added to the supported libraries list being catalogued with PERMISSION_SET=SAFE permission level.