stored proc and file system access

Question

stored proc and file system access

axes2000

SSC-Addicted

Points: 433
More actions
May 11, 2011 at 1:08 pm

#250161

hi all,
I do have a problem where the same procedure executed on my machine on a local instance of SQL Server being logged as domain user (and having OS authentication) is getting the correct results but when I am trying to execute the same proc on my server being logged with sa it does not work anymore - the offending line is the one below:
Select top 1 [path] FROM dbo.dir('\\prodserver\public\repository\2011\05\10\')
I have appropriate permission when logged with my domain account (the reason for the proc behaving when executed on my machine). It is also obvious to me that on the server the MSSQLServer service runs under some local account with very limited permissions.
How am I supposed to solve the problem?
How am I going to run my proc so that even under sa I still get the needed permissions for accessing the file system? I do have a SQL Server account in my database that is a domain account with appropriate permissions (can read/write to that path).
I tried to alter my dbo.dir function WITH EXECUTE AS 'myDomainUser' and I didn't get anywhere - the proc executes, I get no errors however, where I should get some stuff from the OS I am getting nulls.
Anybody can tell me what I am doing wrong?
Thanks,
kowalsky

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

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

AppleMouth SSC-Addicted Points: 450 More actions · Answer 1

This has got to be a permissions issue. Add a step to the code to pump out an email reporting the logged in user. (or write it to a table, or whatever.) It's running under the Server account and that account dose not have permissions.

select SUSER_NAME(), SUSER_ID(), HOST_NAME()

A nod is as good as a wink to a blind bat.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

I agree with applemouth - it is a permissions issue of the account that is running the job. Test it with that account - verify permissions of that account.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

axes2000 SSC-Addicted Points: 433 More actions · Answer 3

Thank you for the answer.

I am somewhat confused now.

I added the line to my proc just to see the facts confirmed!!!

The user IS the domain user with appropriate permissions, I have full access to that path when logged as that user ...

Strangely enough, if I substitute that particular path with a similar one, suddenly it starts working. So there must be something about that path...

Any ideas?

Thanks,

kowalsky

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

I would check both ntfs permissions and share permissions. Is the share on a domain or is it on a computer that is not a member of a domain?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

axes2000 SSC-Addicted Points: 433 More actions · Answer 5

Thanks for the reply,

I checked all permissions, they look fine. However, I need to makes things clear for myself ...

The fact that MSSQLSERVER service runs under the Local System Account on my server makes it so that NO MATTER who is the login who executes

EXEC xp_cmdshell 'dir \\myServer\myDirectory\public\'

the command will still be executed under the security context of that Local System Account?

If so, no wonder ... it will never get to that path!

I tried to create my stored proc with execute as myPowerfullLocalaccount which account lists the contents of that directory without any problem.

I also checked the authentication mode on the server is 2 which means is mixed.

What am I missing?

again: executing dir ...my path from the DOS prompt gets everything,

I stick the same line in EXEC xp_cmdshell 'dir ...' I get Access Denied!

How do I make it to run with the myPowerfullLocalaccount instead of the Local System Account (programmatically),

thanks,

kowalsky

Red8Rain Hall of Fame Points: 3312 More actions · Answer 6

axes2000 (5/12/2011)
What am I missing?
again: executing dir ...my path from the DOS prompt gets everything,
I stick the same line in EXEC xp_cmdshell 'dir ...' I get Access Denied!
How do I make it to run with the myPowerfullLocalaccount instead of the Local System Account (programmatically),
thanks,
kowalsky

when you are executing dir ...my path from a dos prompt, you are using your credential which has access to the share. when you execute xp_cmdshell, you are using the credential that is running sql server, which is local system, in your case.

to run it with your 'myPowerfulLocalAccount,' make sql server run under that account, grant the serverA\myPowerfulLocalAccount access to the remote share.

-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.