CLR Stored Procedure

the .net sql stored procedure tries to create a file on the server:
error:
Msg 6522, Level 16, State 1, Procedure ZIP, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "ZIP":
System.ArgumentException: exception: {0}
Parameter name: System.UnauthorizedAccessException: Access to the path 'C:\' is denied.
   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

Code:
EXEC sp_configure @configname = 'Show Advanced Options', @configvalue = 1
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure @configname = 'clr enabled', @configvalue = 1
RECONFIGURE WITH OVERRIDE
GO
drop assembly ZipStorer
ALTER DATABASE UTIL SET TRUSTWORTHY ON
create ASSEMBLY ZipStorer from 'C:\ZipStorer.dll'
WITH PERMISSION_SET = UNSAFE

DROP ASSEMBLY PMA
ALTER DATABASE UTIL SET TRUSTWORTHY ON
create ASSEMBLY PMA from 'C:\StoredProcedureZip.dll'
WITH PERMISSION_SET = UNSAFE

ALTER DATABASE UTIL SET TRUSTWORTHY ON
GO
Drop PRocedure zip
go
CREATE PROCEDURE ZIP
@SourcefilePath nvarchar,
@DestinationFilePath nvarchar,
@EveryFile bit
WITH EXECUTE AS OWNER
AS
EXTERNAL NAME PMA.[StoredProcedureZip.PMA.Zip].Compress

exec UTIL.dbo.ZIP 'C:\test zip\','c:\test.zip',1
cstrattonAsked:
Who is Participating?
 
swapneel_d14Connect With a Mentor Commented:
Check that account under which you are running your SP has access to C:\
0
 
lenordisteConnect With a Mentor Commented:
as swapneel says, it's a permission issue.

=> you'll also need WRITE permissions it seems
=> one more thing, C: is the C drive where sqlserver is running not the one from which you are calling the store procedure
0
 
cstrattonAuthor Commented:
Ive looked in services and added every account with full permissions. where do I find the account that SP is running under?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Anthony PerkinsConnect With a Mentor Commented:
Look for the MSSQLSERVER service.  It is either:
1. Running as a Local System account or
2. An account with not sufficient permissions to write the file or
3. You have not enabled CLR Integration.
0
 
cstrattonAuthor Commented:
ok great, I have a different error now:

I gave permission to the whole c:\ folder and I'm not looking/running anything in that folder?

The process cannot access the file 'C:\WINDOWS\system32\C' because it is being used by another process.
0
 
swapneel_d14Commented:
what is thwe file name/path?
0
 
cstrattonAuthor Commented:
c:\test zip\ saving a file to this directory
0
 
cstrattonAuthor Commented:
also moved it to c:\program files\test zip\    same security error
0
 
lenordisteCommented:
i think there must be a problem with how you pass the name of your file:
The process cannot access the file 'C:\WINDOWS\system32\C' because it is being used by another process.

it looks as if it's trying to run from c:\windows\system32 and appending C (the drive letter) to the path...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.