CLR Accessing a UNC Share

  • Ok, so I'm pretty new to programming for SQLCLR.

    My problem is thus:

    I'm developing a user defined function in C# that returns a table of image paths. The database is on one server (db), the images are on another (web1) within the same domain. I'm using SQL Server authentication (this function will be used within stored procedures that a few of my websites use).

    For the life of me I can't determine which account I need to allow access to on the web1 share.

    Anyone have any suggestions?

  • Hi Matt,

    I am just curious as to why you are using a CLR for this and not just a user defined function? You can use the xp_FileExists function - for example:

    EXEC

    master..xp_fileexist @MyPath

    Which you can wrap in your own logic as necessary and retrun a table or scalar value?

    Thanks,

    Catherine


    Kindest Regards,

    Catherine Eibner
    cybner.com.au

  • Matt -

    See the section Accessing External Resources of http://msdn2.microsoft.com/de-de/library/ms345101.aspx.

    If you are running using a SQL Server login you're going to get stomped by SQL Server when you try to access external resources - if you're running using Integrated Security, the context will be that of the SQL Server service account.

    Joe

     

     

  • you will have to:

    create a signed assembly in .net  environment

    create an asymentic key on sql server for the .net assembly

    grant EXTERNAL_ACCESS or UNSAFE permission set to the .net assembly

    probably create a login for the asymetric key


    Everything you can imagine is real.

  • I'm using a CLR because I don't know if there are file(s) or not, nor do I know what their names would be. All I know is the path they should be at if they exist.

    So far I've:

    Set the assembly to have EXTERNAL_ACCESS (though I didn't sign it, I set the DB to trustworthy and had whatever the permission set I needed to do it)

    The access is via sql server authentication at the moment, I tried using windows authentication just to see if it would make a difference, but didn't seem to help (at the very least I still wasn't finding the correct account to give permissions to)

    I'll read up on that link and get back to y'all though.

    (Thank you very much for your kind replies)

  • PROBLEM SOLVED!

    By default, the SQL Server (via the clr) seems to access resources under whatever account it is running. Since by default it uses the builtin account "NETWORK SERVICE" I couldn't provide it with the correct permissions on the other server; as soon as I told the sql service to run under a domain account (that I created and assigned permissions to on both machines) it all started working beautifully.

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

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