SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Copy File SP - "Access Denied"?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Xeon5
Starting Member

7 Posts

Posted - 06/09/2014 :  13:25:22  Show Profile  Reply with Quote
Hi,

I have designed a stored procedure which will update our testing server with the latest backup of our live server by using query's and batch files.

The problem I am currently having is running the batch file which handles the copying, it keeps saying Access is Denied when trying to access the live server from the test server. ('xp_cmdshell' is already enabled before running the batch file)

I can run this fine when Im doing it directly from my account but when it is set up as an automated job it throws a hissy fit.

Is there something I am doing wrong? (If I haven't made this clear please let me know).

Here is my SP:

-- || Enable advanced options and xp_cmdshell for DOS commands || --
exec NTTMI..sp_configure 'show advanced options', 1
reconfigure
exec NTTMI..sp_configure 'xp_cmdshell',1
reconfigure

-- || Run batch file to delete current backup file and copy latest backup file from Buffalo to Penguin || --

exec master..xp_cmdshell 'batCopyBuffaloBackup.bat'

-- || Disable XPCMDShell || --

exec NTTMI..sp_configure 'show advanced options', 0
reconfigure
exec NTTMI..sp_configure 'xp_cmdshell',0
reconfigure

And here is the code in the BAT file:

@echo off
setlocal disableDelayedExpansion
pushd \\BUFFALO\j$\Backup\NTTMI
set "newest="
for /f "eol=: delims=" %%F in ('dir /b /od /a-d *.trn') do set "newest=%%F"
del \\PENGUIN\f$\Backups\NTTMI\*.trn
if defined newest copy "%newest%" \\PENGUIN\f$\Backups\NTTMI
popd
%quit%

The problem happens when the BAT file gets to the 'pushd...' part and tries to communicate with the other database. It will run from my machine but not from the SP above.

Edited by - Xeon5 on 06/09/2014 13:26:22

tkizer
Almighty SQL Goddess

USA
36601 Posts

Posted - 06/09/2014 :  13:40:52  Show Profile  Visit tkizer's Homepage  Reply with Quote
The SQL Server service account needs permissions to the share. I do not recommend using admin shares, such as j$ as that requires elevated permissions. Instead, create a new share on NTTMI or Backup and grant access to that. Then exclude everything between \\BUFFALO\ and the share name.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Xeon5
Starting Member

7 Posts

Posted - 06/09/2014 :  14:53:06  Show Profile  Reply with Quote
Hi Tara,

Thanks for the quick reply.

As im still new to sql, could you advise me on how to do what you mentioned?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36601 Posts

Posted - 06/09/2014 :  15:23:02  Show Profile  Visit tkizer's Homepage  Reply with Quote
RDP to the database server and check the SQL Server service account (Contral Panel/Admin Tools/Services). That's the account that needs the permissions.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Xeon5
Starting Member

7 Posts

Posted - 06/16/2014 :  11:07:32  Show Profile  Reply with Quote
So is it possible just to grant PENGUIN the access to that one folder on BUFFALO?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36601 Posts

Posted - 06/16/2014 :  12:19:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
No, what matters is the SQL Server service account.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Xeon5
Starting Member

7 Posts

Posted - 06/17/2014 :  04:13:44  Show Profile  Reply with Quote
Ok, well the service account is MSSQLSERVER. So do I need to give the folder where the backups are stored permissions to that user? Or is it something on the PENGUIN server I have to set?

Sorry for all the questions.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36601 Posts

Posted - 06/17/2014 :  12:41:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
What do you mean MSSQLSERVER? Do you mean Network Account or Local System Account? Or did someone name an AD account MSSQLSERVER? In order to answer your question, need to know the specifics of the service account.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Xeon5
Starting Member

7 Posts

Posted - 06/18/2014 :  03:52:55  Show Profile  Reply with Quote
As mentioned in one of your previous posts, I went to the services tool and scrolled down to where the SQL services are and thats where I found 'MSSQLSERVER':



Also if its any help here is the code from the .bat file which is being called from the System32 folder on PENGUIN:

@echo off
setlocal disableDelayedExpansion
pushd \\BUFFALO\j$\Backup\NTTMI
set "newest="
for /f "eol=: delims=" %%F in ('dir /b /od /a-d *.trn') do set "newest=%%F"
del \\PENGUIN\f$\Backups\NTTMI\*.trn
if defined newest copy "%newest%" \\PENGUIN\f$\Backups\NTTMI
popd
%quit%


And here is an image of the message I get when I try to run the SP:


Edited by - Xeon5 on 06/18/2014 03:59:02
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36601 Posts

Posted - 06/18/2014 :  12:38:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
You are using Local System for the SQL Server service account. You'll need to change that in order to grant access to a remote resource. You can use the Network Resource (I think it's called) and then grant the computer account the access (DomainName\HostName$). Or change the service to use a domain account and then grant that the access.

Changing the service account is not trivial. Well changing it is trivial, but things might not work afterwards unless you grant local admin to a domain account. Local admin is not recommended though, so there are some steps to do if you don't use local admin (outside of the scope of this thread).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Xeon5
Starting Member

7 Posts

Posted - 06/19/2014 :  11:05:55  Show Profile  Reply with Quote
I had a look on the internet and found something which is actually on this forum aswell:

"Local System : Completely trusted account, like the administrator account. There is nothing on a single box that this account cannot do and it has the right to access the network as the machine (this requires Active Directory and granting the machine account permissions to perform something)"

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=126871

Does this mean that PENGUIN server has to be part of the active directory?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36601 Posts

Posted - 06/19/2014 :  15:17:04  Show Profile  Visit tkizer's Homepage  Reply with Quote
I am not positive, but I believe the answer is yes. I think this would be a question for a server/network admin.

When we use the Network option for the service account, we just had to grand the machine account (DomainName\HostName$) the access to the remote resource.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Xeon5
Starting Member

7 Posts

Posted - 06/20/2014 :  05:26:48  Show Profile  Reply with Quote
After discussing with my supervisor, we decided to use a new account with better privileges to run the service account. This solved the problem and I was able to copy the file from PENGUIN to BUFFALO using a SQL Query and BAT file.

Thanks for all your help Tara.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36601 Posts

Posted - 06/20/2014 :  12:44:40  Show Profile  Visit tkizer's Homepage  Reply with Quote


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000