|Important||This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here.|
Using Verifiable Assemblies with SQL Server
Extended stored procedures, packaged as dynamic-link libraries (DLLs), provide a way to extend SQL Server functionality through functions developed with Visual C++. Extended stored procedures are implemented as functions inside DLLs. In addition to functions, extended stored procedures can also define user-defined types and aggregate functions (such as SUM or AVG).
When a client executes an extended stored procedure, SQL Server searches for the DLL associated with the extended stored procedure and loads the DLL. SQL Server calls the requested extended stored procedure and executes it under a specified security context. The extended stored procedure then passes result sets and returns parameters back to the server.
SQL Server 2005 provides extensions to Transact-SQL (T-SQL) to allow you to install verifiable assemblies into SQL Server. The SQL Server permission set specifies the security context, with the following levels of security:
Unrestricted mode: Run code at your own risk; code does not have to be verifiably type-safe.
Safe mode: Run verifiably typesafe code; compiled with /clr:safe.
Safe mode requires the executed assemblies to be verifiably typesafe.
To create and load a verifiable assembly into SQL Server, use the Transact-SQL commands CREATE ASSEMBLY and DROP ASSEMBLY as follows:
CREATE ASSEMBLY <assemblyName> FROM <'Assembly UNC Path'> WITH PERMISSION_SET <permissions> DROP ASSEMBLY <assemblyName>
The PERMISSION_SET command specifies the security context, and can have the values UNRESTRICTED, SAFE, or EXTENDED.
In addition, you can use the CREATE FUNCTION command to bind to method names in a class:
CREATE FUNCTION <FunctionName>(<FunctionParams>) RETURNS returnType [EXTERNAL NAME <AssemblyName>:<ClassName>::<StaticMethodName>]
The following SQL script (for example, named "MyScript.sql") loads an assembly into SQL Server and makes a method of a class available:
-- Create assembly without external access drop assembly stockNoEA go create assembly stockNoEA from 'c:\stockNoEA.dll' with permission_set safe -- Create function on assembly with no external access drop function GetQuoteNoEA go create function GetQuoteNoEA(@sym nvarchar(10)) returns real external name stockNoEA:StockQuotes::GetQuote go -- To call the function select dbo.GetQuoteNoEA('MSFT') go
SQL scripts can be executed interactively in SQL Query Analyzer or with the command-line option using Isqlw.exe. For information about Isqlw.exe, see isqlw Utility. The following command line connects to MyServer, uses the default database, uses a trusted connection, inputs MyScript.sql, and outputs MyResult.txt.
isqlw –S MyServer -E –i myScript.sql –o myResult.txt