sp_fileexist

Question

sp_fileexist

BSavoie

SSCertifiable

Points: 6309
More actions
October 14, 2009 at 6:50 pm

#136684

sp_fileexist does not seem to work on a mapped drive, or a network share. Anybody have any alternatives?
.

Viewing 10 posts - 1 through 9 (of 9 total)

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

BSavoie SSCertifiable Points: 6309 More actions · Answer 1

I've probably answered my own question. Looks to me like it's due to the fact that SS 2005 is running under "LocalSystem", and LocalSystem really cannot see the outside world. In fact, I can't even get the "FileSystemObject" scripting object to see the outside world. I really need to figure out if a file exists on another machine. If anyone has any other ideas, I'm game.

(If I change SS2005 to logon using a real account, the service fails to start. I don't really have time to solve that one at the moment).

.

Nabha SSCrazy Eights Points: 8534 More actions · Answer 2

BrainMan (10/14/2009)
I've probably answered my own question. Looks to me like it's due to the fact that SS 2005 is running under "LocalSystem", and LocalSystem really cannot see the outside world. In fact, I can't even get the "FileSystemObject" scripting object to see the outside world. I really need to figure out if a file exists on another machine. If anyone has any other ideas, I'm game.
(If I change SS2005 to logon using a real account, the service fails to start. I don't really have time to solve that one at the moment).

Did you try creating a clr stored procedure and use System.IO.FileInfo? (Try mapping a drive to that location and then execute this sp).

---------------------------------------------------------------------------------

HowardW One Orange Chip Points: 29109 More actions · Answer 3

PP-564103 (10/15/2009)
BrainMan (10/14/2009)
I've probably answered my own question. Looks to me like it's due to the fact that SS 2005 is running under "LocalSystem", and LocalSystem really cannot see the outside world. In fact, I can't even get the "FileSystemObject" scripting object to see the outside world. I really need to figure out if a file exists on another machine. If anyone has any other ideas, I'm game.
(If I change SS2005 to logon using a real account, the service fails to start. I don't really have time to solve that one at the moment).
Did you try creating a clr stored procedure and use System.IO.FileInfo? (Try mapping a drive to that location and then execute this sp).

That won't help as you can't map a drive to a network share as the local system account (drive mappings are user specific).

The answer to this really is to have the service run under a domain account with the correct privileges to access the UNC path. Check the event viewer for messages as to why the service failed to start as a user account - is the domain account added as a local administrator on the server (or at least have sufficient privileges to the paths required to start a sql instance)?

P.S. the account used should not be a domain administrator account - it should have no special permissions on the network other than the UNC paths it has to access.

BSavoie SSCertifiable Points: 6309 More actions · Answer 4

Yea, the CLR was my next stop. I haven't tried that yet. Can the CLR play outside the SQL sandbox?

.

Nabha SSCrazy Eights Points: 8534 More actions · Answer 5

BrainMan (10/15/2009)
Yea, the CLR was my next stop. I haven't tried that yet. Can the CLR play outside the SQL sandbox?

Yes I think so. By setting the Permission level to 'External' or 'Unsafe'(not safe though) of the assembly.

---------------------------------------------------------------------------------

Nabha SSCrazy Eights Points: 8534 More actions · Answer 6

The answer to this really is to have the service run under a domain account with the correct privileges to access the UNC path.

Thanks.I am with Howard on this.

---------------------------------------------------------------------------------

BSavoie SSCertifiable Points: 6309 More actions · Answer 7

Yea, I know, but unfortunately, I inherited a pretty screwed up environment where the SQL Server basically lives in a "domainless" environment, so I'm pretty much stuck with hacks.

.

HowardW One Orange Chip Points: 29109 More actions · Answer 8

Well if you really need a hack (and it is a hack), you can map a drive under the context of the local system account by using xp_cmdshell and net use, specifying the username and password of an account in the net use command.

This is horrible for security for two reasons:

1) enabling xp_cmdshell is probably the biggest security risk on SQL Servers

2) You need to keep the account credentials in cleartext at some point or another in order to map the drive.

I'd strongly advise working with the domain admins to bring this sql server into the domain (assuming it's not where it is because it's web-facing) to save lots of headaches in the future.

BSavoie SSCertifiable Points: 6309 More actions · Answer 9

Interesting idea. I should have thought of that! I think I'm going to try the C# / CLR thing first.

.