none
Error "Msg 6522, Level 16, State 1" receives when call the assembly from Store Procedure to create a Text file and to Write text

    Domanda

  • Hi,
    I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below

    I write a code in class to create a text file and write text in it.
    1) I creat a class in Visual Basic.Net 2005, whose code is given below:
    Imports System
    Imports System.IO
    Imports Microsoft.VisualBasic
    Imports System.Diagnostics
    Public Class WLog
    Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
    Dim w As StreamWriter = File.AppendText(LogName)
    LogIt(newMessage, w)
    w.Close()
    End Sub
    Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
    wr.Write(ControlChars.CrLf &
    "Log Entry:")
    wr.WriteLine(
    "(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
    wr.WriteLine(
    " :")
    wr.WriteLine(
    " :{0}", logMessage)
    wr.WriteLine(
    "---------------------------")
    wr.Flush()
    End Sub
    Public Shared Sub LotToEventLog(ByVal errorMessage As String)
    Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
    log.Source = "My Application"
    log.WriteEntry(errorMessage)
    End Sub
    End
    Class

    2) Make & register its assembly, in SQL Server 2005.
    3)Create Stored Procedure as given below:

    CREATE PROCEDURE
    dbo.SP_LogTextFile
    (
    @LogName
    nvarchar(255), @NewMessage nvarchar(255)
    )
    AS EXTERNAL NAME
    [asmLog].[WriteLog.WLog].[LogToTextFile]

    4) When i execute this stored procedure as
    Execute SP_LogTextFile 'C:\Test.txt','Message1'

    5) Then i got the following error
    Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0
    A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile':
    System.UnauthorizedAccessException: Access to the path 'C:\Test.txt' is denied.
    System.UnauthorizedAccessException:
    at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
    at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
    at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options)
    at System.IO.StreamWriter.CreateFile(String path, Boolean append)
    at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
    at System.IO.StreamWriter..ctor(String path, Boolean append)
    at System.IO.File.AppendText(String path)
    at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)


    mercoledì 21 giugno 2006 06:52

Risposte

  • 'default imports
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Data.SqlTypes
    Imports Microsoft.SqlServer.Server

    'custom imports
    Imports System.IO
    Imports System.Diagnostics


    Partial Public Class StoredProcedures
        Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
            'impersonate the calling user
            Dim newContext As System.Security.Principal.WindowsImpersonationContext
            newContext = SqlContext.WindowsIdentity.Impersonate()

            Try
                Dim w As StreamWriter = File.AppendText(LogName)
                LogIt(newMessage, w)
                w.Close()
            Catch ex As Exception
                'any error handling code goes here
            Finally
                newContext.Undo()
            End Try
        End Sub
        Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
            wr.Write(ControlChars.CrLf & "Log Entry:")
            wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
            wr.WriteLine(" :")
            wr.WriteLine(" :{0}", logMessage)
            wr.WriteLine("---------------------------")
            wr.Flush()
        End Sub
        Public Shared Sub LotToEventLog(ByVal errorMessage As String)
            Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
            log.Source = "My Application"
            log.WriteEntry(errorMessage)
        End Sub
    End Class

    mercoledì 21 giugno 2006 15:43

Tutte le risposte

  • You need to perform impersonation in SQLCLR routines to access external resources. By default your managed code is running under the SQL Server security context.

     

    // Use current user's context to access external resource - unless

    // logged in using SQL authentication where WindowsIdentity will

    // be null and will use SQL Server service account


    WindowsImpersonationContext newContext = null;
    WindowsIdentity newIdentity = SqlContext
    .WindowsIdentity;

    try
    {
       if (newIdentity != null) newContext = newIdentity.Impersonate();

       // do external access here
    }
    catch
    {
       
    // ...
    }
    finally
    {
       if (newContext != null) newContext.Undo();
    }

    Hope this helps,

    Derek

    mercoledì 21 giugno 2006 13:32
  • Dear Derek,

    Where should i write the above code? can you tell me in further detail.
    thanks.

    mercoledì 21 giugno 2006 13:39
  • 'default imports
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Data.SqlTypes
    Imports Microsoft.SqlServer.Server

    'custom imports
    Imports System.IO
    Imports System.Diagnostics


    Partial Public Class StoredProcedures
        Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
            'impersonate the calling user
            Dim newContext As System.Security.Principal.WindowsImpersonationContext
            newContext = SqlContext.WindowsIdentity.Impersonate()

            Try
                Dim w As StreamWriter = File.AppendText(LogName)
                LogIt(newMessage, w)
                w.Close()
            Catch ex As Exception
                'any error handling code goes here
            Finally
                newContext.Undo()
            End Try
        End Sub
        Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
            wr.Write(ControlChars.CrLf & "Log Entry:")
            wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
            wr.WriteLine(" :")
            wr.WriteLine(" :{0}", logMessage)
            wr.WriteLine("---------------------------")
            wr.Flush()
        End Sub
        Public Shared Sub LotToEventLog(ByVal errorMessage As String)
            Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
            log.Source = "My Application"
            log.WriteEntry(errorMessage)
        End Sub
    End Class

    mercoledì 21 giugno 2006 15:43
  • and it goes without saying but I am going to say it anyway...you must be using Windows Authentication for this to work properly. Otherwise you might as well assign the SQL Server Account permissions to the directory/files and allow all calling users via Sql Authentication to perform the actions.
    mercoledì 21 giugno 2006 16:41
  • Thanks, It is working now. it is realy helpful for me
    Thanks again
    Best Regards,
    giovedì 22 giugno 2006 06:49
  • Please mark this "ticket" as solved then.

    Derek

    giovedì 22 giugno 2006 13:16
  • How does mark the "Ticket" as solved?
    giovedì 22 giugno 2006 13:45
  • There should be an option for you to mark any one particular reply as the ANSWER, this is what I meant by "close the ticket".

     

    giovedì 22 giugno 2006 13:55
  • could u plz tell me what is seeeting / permission resquired in sql 2005 for this solution
    giovedì 16 novembre 2006 07:22
  • Two settings are required as given below:
    1) Your database config level must be 90
    2) Trustworthy option of the database must be ON

    giovedì 16 novembre 2006 09:34
  • could u plz tell me what is trustworthy and where is this option , how se set this
    giovedì 16 novembre 2006 11:15
  • Just Do this:

    alter Database <YOURDATABASENAME> set TRUSTWORTHY ON;

    Just before the:

    CREATE ASSEMBLY GetYahooNews
    FROM 'xxxxx\mmmm'
    WITH PERMISSION_SET = SAFE;
    go
    Select * from sys.assemblies
    go
    CREATE FUNCTION dbo.GetYahooNews()
    RETURNS
     TABLE ( title nvarc.....

    Hope it was usefull.

    Cheers.

    martedì 13 novembre 2007 11:36