Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Jul 2002
    Posts
    87

    Unanswered: Stored procedure to write to UNC Path

    Hello everyone...
    I've ran into a little trouble shortly - I'm trying to write a text-file to a UNC Path directly from a stored procedure (actually a VB.NET Class).

    Now if I try to write to the network path I always get the following error :
    System.UnauthorizedAccessException: Access to the path \\...\share\test.txt is denied

    How can I find the username the VB.NET sp uses to access this UNC Path?

    Best Regards...

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The SUser_SName() function in SQL may be able to help.

    Can you use BCP to write to the text file isntead? *shrug*
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2002
    Posts
    87
    Hello gvee,
    SUSER_SNAME will only return the SQL Login - right?
    I'm not using windows authentication - so it only would return a virtual user int he SQL system.

    BCP can only be done on the command line - right?
    I'm really trying to realize this w/o xp_cmdshell

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You're right.

    So if a SQL login is calling your CLR proc, isn't the user running that proc the SQL login?

    Perhaps you can use an account local to your SQL server and grant that permissions to the UNC path?

    I'm afraid that this spills past my development knowledge in to administration land so please correct me when I'm talking rubbish
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2002
    Posts
    87
    My experience in Windows permissions and CLR-sps are quite zero - so you can't do anything wrong .

    The SQL Login which is executing the CLR is a SQL only login - no windows permissions for that login.
    I can write a file locally w/o any problems - but as soon as I try to write to a UNC path in the network it does not work...
    I've also tried to mount the network drive on the SQL server, but the CLR does not find that drive :/...

    So do you think I need to create a Windows User and give this one a role in SQL server?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    When you mount a network drive that's for you on that box, not all users of that box


    If you run the same code outside of the CLR, can you write to the UNC?
    George
    Home | Blog

  7. #7
    Join Date
    Jul 2002
    Posts
    87
    only if I give l/p...
    Is there a possibility to create a share which is r/w for everyone on windows server 2005?
    Do I need to activate the guest account for this?

    (sorry... I can imagine that this is kind of a noob question )

  8. #8
    Join Date
    Jul 2002
    Posts
    87
    I mean every user (not only the users in the system)

  9. #9
    Join Date
    Jul 2002
    Posts
    87
    I've just created a windows user which is accessible trough the sql server and has access to the share but I still cannot write to the share trough the stored procedure...

    I've created the CLR sp with the option EXECUTE AS and SUser_SName gives the correct reply - with the user logged in I can access the UNC path and write to it...

    Still the same error:
    System.UnauthorizedAccessException: Access to the path '\\srv\share\file.txt' is denied.
    ...
    ...


    Is there any setting im missing? (Like the bulk insert permission or similar?)

  10. #10
    Join Date
    Oct 2009
    Posts
    19
    Try setting your assembly to EXTERNAL_ACCESS

    CREATE ASSEMBLY FileAccess
    FROM 'E:\FileLoader.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS
    GO
    More information here
    Security in the CLR World Inside SQL Server
    -----------------------------------
    Free SQL server monitoring for DBA's
    SQL DBA manager by BlueThames.com

  11. #11
    Join Date
    Jul 2002
    Posts
    87
    Hello OxfordSmarty,
    I've created it originally UNSAFE which should be even less secure - right?

    I had the same error using the EXTERNAL_ACCESS option.

    My current creation t-sql:
    CREATE ASSEMBLY sqlUNCwrite FROM 'C:\sqlUNCwrite.dll' WITH PERMISSION_SET = UNSAFE
    GO
    CREATE PROCEDURE sp_sqlUNCwrite (@str nvarchar(500))
    WITH EXECUTE AS 'winTestAcc' AS EXTERNAL NAME sqlUNCwrite.[sqlUNCwrite.StoredProcedures].testWrite
    GO

  12. #12
    Join Date
    Oct 2009
    Posts
    19
    Have you set trustworthy on

    ALTER DATABASE $YOUR_DB
    SET TRUSTWORTHY ON
    GO
    -----------------------------------
    Free SQL server monitoring for DBA's
    SQL DBA manager by BlueThames.com

  13. #13
    Join Date
    Jul 2002
    Posts
    87
    no change in the result :-/

  14. #14
    Join Date
    Oct 2009
    Posts
    19
    -----------------------------------
    Free SQL server monitoring for DBA's
    SQL DBA manager by BlueThames.com

  15. #15
    Join Date
    Oct 2009
    Posts
    19
    Does the account that SQL server is running under have access to the share?
    -----------------------------------
    Free SQL server monitoring for DBA's
    SQL DBA manager by BlueThames.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •