Creating CLR assemblies with VS 2008

Question

Creating CLR assemblies with VS 2008

Dave-3000

SSCertifiable

Points: 6186
More actions
August 13, 2008 at 2:01 pm

#189328

I am trying to write a C# function that I can call via the CLR from my stored proc to delete all the files in a directory. All the examples I have seen assume you don't have VS2008 and use the command line to compile the code from a text file. Has anyone done this from VS2008? We just got the pro edition and I am still finding my way around in it. Thanks.
There is no "i" in team, but idiot has two.

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

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

It is really simple to do in Visual Studio, and the steps in 2008 are the same as 2005. When you create the database project, it will ask for a server and database. Add a function, and write the code. To deploy the function from Visual Studio, you will need to specify an account that has the necessary rights to create an assembly and the database objects contained in the assembly.

Since you want to do filesystem access you have to create the assembly with External_Access permissions which will require an additional step, of signing the assembly with a certificate which you can see how to do on the following link:

http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/9/Default.aspx

Alternatively you can set the database trustworthy option on, but this is not the recommended practice.

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 2

Thanks Johnathon, but how does the user in your example relate to the user who is actually going to run the stored proc? I have an Agent job that runs a stored proc. SQL Agent runs under the context of a Windows domain user on my network. The stored proc will exec this CLR stored proc to delete files. Do I have to somehow indicate the new user created to my domain user?

There is no "i" in team, but idiot has two.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

The Login and user that you create from the certificate have nothing to do with the permissions that the CLR runs under. They exist solely to allow you to create the assembly with EXTERNAL_ACCESS or UNSAFE permissions. For the OS Level rights, you have two options. If you don't do anything at all, the Function will have the same rights as the SQL Server Service Account at the OS Level. If you want it to function under the rights of the SQL Agent Service Account, then you have to use WindowsIdentity Impersonation in your CLR Function. You can read more about Impersonation in this article:

http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/34/Default.aspx

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 4

When you build a SQL project is VS, where does it put the assembly? I have to specify it for CREATE ASYMMETRIC KEY, but I don't see where on my DB server it stashed the assembly.

There is no "i" in team, but idiot has two.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

It will be in the projects solution folder in the bin\Debug or bin\Release folder as appropriate for the type of build you did. You have to then copy the dll to the SQL Server, and use the path on the SQL Server to create the key, or use a full UNC path to it on your local machine if the SQL Server can see your local machine. I generally place the DLL in the root path of C or D and create the key, then you can delete the DLL from the SQL Server, as it is no longer needed.

The assembly itself is kept inside of SQL Server in the database as a binary BLOB, so it is backed up as part of the database.

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 6

I copied it to C:, then did this:

USE master

GO

-- First Create the Asymmetric Key from the Assembly

CREATE ASYMMETRIC KEY DftFileDeleteKey

FROM EXECUTABLE FILE = 'C:\SqlServerProject1.dll'

GO

--Now create a SQL Login from the Assymetric Key.

-- Create the Login from the Asymmetric Key

CREATE LOGIN DftFileDelete FROM ASYMMETRIC KEY DftFileDeleteKey

GO

--Now grant either the CREATE EXTERNAL ACCESS ASSEMBLY or CREATE UNSAFE ASSEMBLY right to this user.

-- Grant the External Access Priviledge to the Login

GRANT EXTERNAL ACCESS ASSEMBLY TO DftFileDelete

GO

--Then add a Database User mapped to the new Login in your database.

USE [DiscretionaryFundsQA]

GO

-- Add a database user in the SQLCLR_Net Database for the Login

CREATE USER DftFileDelete FOR LOGIN DftFileDelete

GO

I went back to the project and set the assembly owner. I still get a security exception:

System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.CodeAccessPermission.Demand()

at System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption)

at System.IO.Directory.GetFiles(String path, String searchPattern, SearchOption searchOption)

at StoredProcedures.FileDelete(SqlString& outVal, String dirPath)

What did I do wrong?

There is no "i" in team, but idiot has two.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

Did you change the safety level of the Assembly on the Deploy tab to External_Access? You can check this from SQL Management Studio by expanding the database, Programability, Assemblies, and opening the Assemblies Properties, or you can use the following query:

select name, permission_set_desc

from sys.assemblies

If it says Safe, then you need to change it to External_Access, and try calling the function again. You can also do this change by calling ALTER ASSEMBLY like the following:

ALTER ASSEMBLY [SQLCLR_CSharp_Examples]

WITH PERMISSION_SET = EXTERNAL_ACCESS

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 8

Yep, that did it. I thought I set it to external access on the create assembly statement, but I guess not. Thanks for all your help.

There is no "i" in team, but idiot has two.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

I guess I'd have to ask... what does the CLR and the C# function do?

--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)

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 10

I was wondering when you'd show up to ask me that, Jeff 🙂

I have a stored procedure that creates files that are emailed as attachments by sp_send_dbmail. I always write the files to the same directory as *.htm. The emails go out once a week so before I create a new batch of files to be shipped I delete all the *.htm files in that directory.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.IO;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void clr_DirectoryEmpty(out SqlString outVal, string dirPath, string fileMask)

{

// list all the files that fit the mask in the directory

string[] files = Directory.GetFiles(dirPath, fileMask, SearchOption.TopDirectoryOnly);

foreach (string s in files)

{

File.Delete(s.ToString()); // delete the file

}

outVal = "Deleted " + files.Length.ToString() + " files";

}

};

I call it like so:

DECLARE @OutVal varchar(100), @DirPath varchar(100), @FileMask varchar(100)

SET @DirPath = 'F:\DiscFunds'

SET @FileMask = '*.htm'

-- clear out old statements from directory with this handy CLR procedure

EXEC dbo.clr_DirectoryEmpty @outVal = @OutVal, @DirPath = @DirPath, @FileMask = @FileMask

And then Jonathan helped me jump through the flaming hoops to get it working.

There is no "i" in team, but idiot has two.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 11

Dave (8/13/2008)
I am trying to write a C# function that I can call via the CLR from my stored proc to delete all the files in a directory.

This statement is what kept me from asking the same thing Jeff did. This in my opinion is a good place to use CLR if you are going to go about deleting files from inside TSQL code. The alternative is to enable xp_cmdshell and then you have a larger risk, again in my opinion. Both require changes to the default surface area configuration.

I will point out a few problems I have with your specific implementation of this though. By allowing a path/file spec to be passed, you open yourself up to the same security issues you would have with xp_cmdshell. What if someone calls this with *.DLL and passes the path to the .NET framework? You will have a big mess on your hands as this proc deletes your framework DLL's. You might consider deploying the procedure with an array of excludable paths to prevent this from happening? The other alternative is to code the procedure for single use, and use an app config file to hold the path and file spec as keys that you can get using the System.Configuration class, so that they aren't hardcoded into the procedure. To change them would require that you restart the SQL Service though, since changes to the app config are only picked up on service startup.

Of course there is the argument that you should do this with a SQL Agent Job step using a cmdexec call.

Food for thought.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

Dave (8/18/2008)
I was wondering when you'd show up to ask me that, Jeff 🙂
I have a stored procedure that creates files that are emailed as attachments by sp_send_dbmail. I always write the files to the same directory as *.htm. The emails go out once a week so before I create a new batch of files to be shipped I delete all the *.htm files in that directory.

I sure do appreciate the feedback, Dave... especially the code. 🙂

I share the same concern that Jonathan expressed... I believe you have opened a portal for some trouble because you can use the CLR to delete ANYTHING including some operating system files. And, it doesn't even have to be malicious on anyone's part... could be just an accident. Might as well allow the use of xp_CmdShell if you're going to allow such a CLR to exist. It's salvageable, though... just change it so it doesn't allow path/file name as a parameter. Then, I'd say "good reason to have a CLR". 😀

This is part of the reason why I fear the use of CLR's as a DBA... this particular CLR could be used to put a world of hurt on the system and most DBA's aren't necessarily made privy to the source code for CLR's. I'd much rather see something like Jonathan's suggestion about using an SQL Agent Job and, perhaps, maybe even a proxy user so that no one but the DBA's can modify or even see the code that's being executed to do the delete.

--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)

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 13

You've both made a good point (I hate it when that happens).

Since the goal is really to delete text files, I can change the C# code to reject a file mask for .dll or .exe files. I'll post some code when I have it done.

Thanks for the suggestion.

There is no "i" in team, but idiot has two.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 14

To keep your CLR Assembly scalable, you might consider using a Enum of acceptable file types. Then your procedure could validate that the filetype provided is in the acceptable Enum or not. This would allow you to add new file types to the enum, build the assembly and then instead of doing a full deploy, issue an ALTER ASSEMBLY DDL command in SSMS to update the assembly from the new DLL. Since the procedure definition doesn't change, the alter assembly will mark the appdomain for unload, and SQL will reload the new assembly code at next execution.

If the Enum test fails, you can SQLPipe an error message back to the user calling the procedure.