Copy File SP - "Access Denied"? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Copy File SP - "Access Denied"?

Author

Topic

Xeon5
Starting Member

10 Posts

Posted - 2014-06-09 : 13:25:22

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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-09 : 13:40:52

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/

Xeon5
Starting Member

10 Posts

Posted - 2014-06-09 : 14:53:06

Hi Tara,

Thanks for the quick reply.

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-09 : 15:23:02

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/

Xeon5
Starting Member

10 Posts

Posted - 2014-06-16 : 11:07:32

So is it possible just to grant PENGUIN the access to that one folder on BUFFALO?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-16 : 12:19:06

No, what matters is the SQL Server service account.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Xeon5
Starting Member

10 Posts

Posted - 2014-06-17 : 04:13:44

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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-17 : 12:41:58

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/

Xeon5
Starting Member

10 Posts

Posted - 2014-06-18 : 03:52:55

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:

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-18 : 12:38:37

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/

Xeon5
Starting Member

10 Posts

Posted - 2014-06-19 : 11:05:55

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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-19 : 15:17:04

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/

Xeon5
Starting Member

10 Posts

Posted - 2014-06-20 : 05:26:48

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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-20 : 12:44:40

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Xeon5
Starting Member

10 Posts

Posted - 2014-12-12 : 11:11:47

Hi all,

Having an issue with the backup procedure again, it is now saying "The specified network name is no longer available. 0 file(s) copied.". Could this be the result of a time out?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-12 : 12:59:19

I would be suspicious of a network issue.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Xeon5
Starting Member

10 Posts

Posted - 2014-12-15 : 04:10:04

That's what I thought. It worked before, maybe the file is too big? The backup file I am copying is about 24GB.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-15 : 12:06:25

That's a "small" file. If you get that error when it's doing the copy, then the network needs to be investigated. You should be able to copy hundreds of gigabytes for one file without being disconnected, even terabytes really.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources