CLR Accessing a UNC Share

Question

CLR Accessing a UNC Share

Matt-461091

SSC Enthusiast

Points: 133
More actions
July 5, 2007 at 3:23 pm

#174304

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?

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

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

Catherine Eibner Ten Centuries Points: 1364 More actions · Answer 1

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

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 2

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

bledu SSChampion Points: 12335 More actions · Answer 3

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.

Matt-461091 SSC Enthusiast Points: 133 More actions · Answer 4

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)

Matt-461091 SSC Enthusiast Points: 133 More actions · Answer 5

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.