June 2, 2009 at 11:08 am
Hello,
I have a CLR stored procedure which reads bytes from a row in the database. It then creates a filestream and writes those bytes to disk. It works fine as long as its a local path. I'd like to have th CLR stored procedure write this file to a network share.
Below is the sql which creates the CLR stored procedure. When I execute the procedure using a network share path I get access denied. Anyone have any ideas?
USE master
GO
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'PALIMSRTFToPDFUser')
DROP LOGIN [PALIMSRTFToPDFUser]
GO
IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE name = N'LIMSKey')
DROP ASYMMETRIC KEY LIMSKey
GO
declare @path varchar(200)
declare@sql varchar(400)
set @path = 'd:\Projects\PALIMS\LimsSqlServer.dll'
set @sql = 'CREATE ASYMMETRIC KEY LIMSKey FROM EXECUTABLE FILE = '''+@PATH+''''
exec (@sql)
CREATE LOGIN PALIMSRTFToPDFUser FROM ASYMMETRIC KEY LIMSKey
GRANT EXTERNAL ACCESS ASSEMBLY TO PALIMSRTFToPDFUser
GO
USE [!!TargetDatabase!!]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConvertToPDF]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ConvertToPDF]
GO
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'LimsSqlServer')
DROP ASSEMBLY [LimsSqlServer]
GO
declare @path varchar(200)
set @path = 'd:\Projects\PALIMS\LimsSqlServer.dll'
CREATE ASSEMBLY [LimsSqlServer] AUTHORIZATION [dbo] FROM @path
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-------- ConvertToPDF ---------
CREATE PROCEDURE [dbo].[ConvertToPDF]
@InputFile [varbinary](max),
@DeleteTempPDF [bit],
@Extension [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [LimsSqlServer].[LimsSqlServer.StoredProcedures].[ConvertToPDF]
GO
if exists (select uid from sysusers where name = 'CAIHDC\USALIMS_ADMIN')
GRANT exec ON [dbo].[ConvertToPDF] TO [CAIHDC\USALIMS_ADMIN]
GO
if exists (select uid from sysusers where name = 'CAIHDC\USALIMS_TEAM')
GRANT exec ON [dbo].[ConvertToPDF] TO [CAIHDC\USALIMS_TEAM]
GO
GRANT exec ON [dbo].[ConvertToPDF] TO PALIMSAppUser
GO
June 2, 2009 at 11:38 am
That usually indicates that the account your SQL Server is runnung under does not have access to the share.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 2, 2009 at 11:40 am
How can I make sure that the account has access to that share?
June 2, 2009 at 3:05 pm
You would have to grant access to that Logon or setup SQL Server to run under an account that already had access.
To test it, you could either login to that account and try to access the share, or Execute the follwoing SQL Command from SA (or any other SQl Admin logon):
EXEC xp_CmdShell 'DIR \\sharename'
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply