Inadequate Permission On Hard Drive 1

[tomschafer]

I am trying to run a batch file using xp_cmdshell that uses files from a remote server. But I'm even having problems running simple dos commands. For example...

master..xp_cmdshell 'dir \\remoteserver\c$\*.*'

Output= "Access Is denied."

Logged in to SQL as SA, Logged in to NT as Admin on both boxes. On an NT 4.0, SQL 6.5 network. SQL is using integrated security. I think the problem may be the permission I have on the remote hard drive, but I can not get this to work even if I give "Everyone" "Full Control".
Thanks!!!

 

[RickCole]

Have you tried setting up an NT Share name on the remote server where the batch files reside and access them through the share.

Also the server from where you running xp_cmdshell will be accessing the share from the login account name which SQL Server is running under not what you are logged in as.

Rick.

 

[tomschafer]

Thanks for the response Rick. The NT share name already exists. And I knew that it would be ran under the SQL account. But I'm having a heck of a time determining which user SQL is mapped to.

 

[RickCole]

From your response I guess you may already know all this but just in case:

Logon onto your SQL Server and then go to Services in Control Panel and then to the service MSSQLServer. The box 'This Account' will show what domain and login ID SQL Server is running under.

At my site this login ID is controlled by the DBA's and has it’s password set accordingly. This login ID is also an administrator of the NT server it lives on.

Now logon onto the server using the SQL Server logon ID and use explorer to get to your remote files. If you can get hold them without any problems then I would say it's not the permissions that are at fault.

Rick.

 

[tomschafer]

Again thanks. I have a pretty good grasp at what's going on in the world of SQL but am still waiting for the blind-fold to be removed so I can see NT. Anyway SQL is running as a service- the System Account. Also I can get to the files in explorer or from a dos prompt, just not with the system account.

 

[RickCole]

That's where the problem lies with SQL server running under the system account. You need to click on the ‘This Account’ radio button and use a NT account which has already been set up. This account should be just for your SQL Server as mentioned in my previous post. It's also a good idea to have the SQLExecutive service running under this account as well.

This is a quote from BOL about the System account:

Important A service that is running under the system account has no user account information associated with it. This will prevent certain network server software from allowing the Windows NT service to access resources. Microsoft LAN Manager user-level security servers and Novell® NetWare® servers will not allow a Windows NT service that is running under the system account to access their file shares.

Rick.