v2008.... fileexists

I have this running on three other machines right now, so I just cannot figure out what the heck i'm missing

DECLARE @fileExists INT
EXEC master.dbo.xp_fileexists '\\server\share\file.csv', @fileexists OUTPUT
SELECT @fileExists

Run it as my login (admin), i get the 1 back I am looking for.  (the file exists)
Run it as the app login, i get back 0.  It cannot see the file.

xp_cmdshell is enabled
proxy account is the account the service runs under, admin to the local box, that's where the share is

login has appropriate permissions.  no error is returned, i just get zero, it can't see that share using that login/user

what did I miss?
LVL 18
dbaSQLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbaSQLAuthor Commented:
anybody know what it is?
0
dportasCommented:
I think xp_fileexists is an undocumented feature from 2000/2005. As far as I know there is no equivalent in 2008 but you could do it using a CLR proc. Undocumented features are really best avoided.
0
Anthony PerkinsCommented:
It is undocumented, however it is still availale in 2008.  
It has to be a permissions problem.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

RiteshShahCommented:
try this.


DECLARE @fileExists INT
exec master.dbo.xp_fileexist 'd:\emps.txt', @fileExists OUTPUT
SELECT @fileExists



you have misspeeled SP name

DECLARE @fileExists INT
exec master.dbo.xp_fileexist 'd:\emps.txt', @fileExists OUTPUT
SELECT @fileExists

Open in new window

0
RiteshShahCommented:
BTW, change your path in above script ;)
0
Anthony PerkinsCommented:
>>you have misspeeled SP name<<
Good catch.
0
dbaSQLAuthor Commented:
i'm agreeing w/you, ac.  it has to be permission specific.  exactly the same execution works under my login, yet produces no output (can't see the file), when run w/the application login

DECLARE @fileExists INT
EXEC master.dbo.xp_fileexists '\\server\share\file.csv', @fileexists OUTPUT
SELECT @fileExists

I cannot, however, find the variance.
0
Anthony PerkinsCommented:
Did you not see RiteshShah's comment (no points please) about the way you had spelled the extended stored procedure?
As in, it should be:
DECLARE @fileExists INT
EXEC master.dbo.xp_fileexist '\\server\share\file.csv', @fileexists OUTPUT
SELECT @fileExists
0
dbaSQLAuthor Commented:
yes, i saw that, ac.  my bad, i should have conveyed myself differently.  i saw ritesh's post, but we all know that had i run it like that, it wouldn't have worked anyway --- giving me an error about 'could not find stored procedure 'master.dbo.xp_fileexists'....

my initial post was type-o'd.  this is precisely my text -- run it  under my login, it works just fine, the OUTPUT = 1.  run it under the app login, OUTPUT = 0, because it cannot see the file

DECLARE @fileExists INT
EXEC master.dbo.xp_fileexist '\\server\share\file.csv', @fileExists OUTPUT
SELECT @fileExists


0
Anthony PerkinsCommented:
True, enough.

Have you verified the account used by the SQL Server service is not a Local System account and instead is a Domain account that has access to that folder?
0
dbaSQLAuthor Commented:
yep.  the service account is a domain login, and it is local admin on the box.  the folder is local to the server

the newness of 08 is still a problem at times...  
i'm looking at the application login  under which this thing is supposed to run, but i don't see anything obviously wrong there.  maybe the proxy acct?
0
Anthony PerkinsCommented:
Unfortunately, you are not going to get any support for this from Microsoft and it may well be that it no longer functions the way it used to do, if at all.
0
dbaSQLAuthor Commented:
I understand what you're saying, ac, but i think it does work the way it used to.  remember, i can login as my user account or as sa, and it works precisely as it always has.  it's simply w/the application login that i am having this difficulty

i'm going to up that login's privileges in the dev bed and see if it works.
0
RiteshShahCommented:
do you have "DiskAdmin" fixed server role for your user? It may impact.
0
dbaSQLAuthor Commented:
the app login is a member of 'bulkadmin' server role
it is also a member of db_datareader, db_datawriter and db_ddladmin within the user db
it is also valid user within the master db, and is privileged to xp_cmdshell

These settings are the same on the old instance (v2000, currently in production and functional), and the new instance, which is v2008 -- not yet functional.    

All the code works fine, unless or until executed from the application layer.  He receives 'success', yet he does nothing.  I monitored execution, he thought all was well because of 'success'  --  i confirmed no files were moved/loaded.  i then logged in as that login and fired the same execution, I received this:         'The filename.csv does not exist.'

This is the intended output from my procedure:

DECLARE @fileExists INT
EXEC master.dbo.xp_fileexist '\\server\share\file.csv', @fileExists OUTPUT
IF(@fileExists = 1)
BEGIN
       ....load data, do all the work
END
ELSE
BEGIN
        PRINT 'The filename.csv does not exist.'
END

So i logged in as that login and just did this:

DECLARE @fileExists INT
EXEC master.dbo.xp_fileexist '\\server\share\file.csv', @fileExists OUTPUT
SELECT @fileExists

I get 0.

I login as myself, I get 1.  Because the file is there.
0
dbaSQLAuthor Commented:
important to note;  he doesn't receive a permissioning error -- like 'execute permission denied on object 'xp_cmdshell', database 'master', owner 'dbo'.  

he's getting in.... it's just that when the session is under that login, it does not see the files on disk.  
0
RiteshShahCommented:
so I guess, your user name is having permission over network but application use doesn't seems to have permission. why don't you create one login, in SQL Server, of your AD account with full permission over network and try using that user with your application.

All I can say is, your user is not having network permission but it do have permission on database and xp_cmdshell.
0
dbaSQLAuthor Commented:
also important --- at one point, as we were setting things up, he received this:

" The xp_cmdshell proxy account information cannot be retrieved or is invalid.  Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information."

When I saw that, I set the proxy account to the sql server service account -- which is just like it is on all of my other machines, including the one in production where this thing works --  the service was restarted.

But the problem persists.
0
dbaSQLAuthor Commented:
the damn thing works when i make the app login a member of sysadmin
i'm looking at the other box -- production, v2000, where all the same code is in place and functional.
that app login is not a memeber of sysadmin

this is frustrating.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RiteshShahCommented:
try removing "sysAdmin" and making "DiskAdmin"
0
dbaSQLAuthor Commented:
nope.  doesn't work.  i pulled him from sysadmin and put him into diskadmin.  exec the proc returns 'filename does not exists'

put him back in sysadmin, exec the proc, it works fine

surely there is something soooo trivial that i am missing here.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.