xp_cmdshell File Copy Failing - "Access Is Denied"
-
2 New Windows Servers 2016 running SQL 2016 Standard. No Active Directory.
Logged on as my local account (in Windows Administrator group)
Also logged on as Administrator, with same results.This code works in our old environment Running Windows Server 2003 and SQL 2005
I can manually drag the files across from 1 Windows folder to another.I want to copy SQL Backup files from Server A to Server B, and then restore backups, but can't copy the files. I enabled xp_cmdshell, then run:
DECLARE
@cmd varchar(2500),
@folderfull varchar(100),
@folderdiff varchar(100),
@fromfolder varchar(100)SET nocount on
SET @fromfolder = '\\192.xxx.xxx.xxx\C$\transfer\BackupDIFF\' -- Server A location
SET @folderdiff = 'C:\LiveDBDownload\DIFF\' -- Local Server B-- Delete any previous RedGate DIFF Backups
SET @cmd = 'del /Q ' + @folderdiff + '*.SQB'
select @cmd -- Display contents
EXEC master.dbo.xp_cmdshell @cmd
-- Display Remote Folder
SET @cmd = 'dir ' + @fromfolder
select @cmd -- Display contents
EXEC master.dbo.xp_cmdshell @cmd
-- Display Local Folder
SET @cmd = 'dir ' + @folderdiff
select @cmd
EXEC master.dbo.xp_cmdshell @cmd-- Copy the RedGate Backups from production to here
SET @cmd = 'copy /Y ' + @fromfolder + '*.SQB ' + @folderdiff
select @cmd -- Display contents
EXEC master.dbo.xp_cmdshell @cmdBut I get Access denied trying to empty the local folder, and access the remote server.
only the "DIR" command seems to work.-----------------------------------------------
del /Q C:\LiveDBDownload\DIFF\*.SQBoutput
---------------------------------------------
C:\LiveDBDownload\DIFF\DIFF_PROD_Database_20170210.sqb
Access is denied.
NULL-----------------------------------------------
dir \\192.xxx.xxx.xxx\C$\transfer\BackupDIFF\output
----------------------------------------------
Access is denied.
NULL-----------------------------------------------
dir C:\LiveDBDownload\DIFF\output
---------------------------------------------
Volume in drive C has no label.
Volume Serial Number is AEA3-D289
NULL
Directory of C:\LiveDBDownload\DIFF
NULL
02/13/2017 03:28 PM <DIR> .
02/13/2017 03:28 PM <DIR> ..
02/08/2017 12:00 AM 3,324,928 DIFF_PROD_Database_20170210.sqb
02/10/2017 07:55 PM 7 test.SQB.txt
2 File(s) 3,324,935 bytes
2 Dir(s) 95,311,486,976 bytes free
NULL-----------------------------------------------
copy /Y \\192.xxx.xxx.xxx\C$\transfer\BackupDIFF\*.SQB C:\LiveDBDownload\DIFF\output
--------------------------------------------
Access is denied.
NULL - homebrew01 - Monday, February 13, 2017 2:04 PM
Does the SQL Server Agent account have permissions on both boxes to perform the commands you are trying to do? If not, grant it permissions and you should see those errors go away.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
What YOU can do does not matter. What matters are the permissions that the account being used to run SQL Server holds.
If the service account does not have the proper permissions to the folders, then you will get this error.
Without a domain, you can grant permissions to the everyone group on these folders, but that's probably not a good solution.Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
So even though I'm running it in Management Studio with my Windows Authentication, it's using the SQL Agent permissions ??
- Michael L John - Monday, February 13, 2017 2:49 PM
You should be able to grant <PCname>\<user> permissions on the file/folder as well. I would not recommend the "everyone" group as then anybody who can get access to the machine has access to those folders.
You just need to know who SQL Server Agent (or is it the SQL Server Engine? I cannot remember offhand) runs as so you can set the permissions properly.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Looks like everything is "NT Services"
-
I'd change it then to a real user instead of any built-in users.
If it was me, I'd make at least 2 - one for SQL Server and one for SQL Server Agent. And then just need to grant those accounts permissions to read/write to folders across your network and you should be good to go.I don't like using any built-in accounts for anything like that plus any "NT anything" accounts are tricky to get working across a network. Same thing with "Local Service".
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. - homebrew01 - Monday, February 13, 2017 3:01 PMThat was the problem. THANKS !!
I temporarily changed the services to be Administrator, and looks like it's working.Looks like everything is "NT Services"
- bmg002 - Monday, February 13, 2017 2:56 PM
According to the docs, xp_cmdshell spawns a windows process under the SQL Server service account security context.
But I think another part of the problem in this case is that it's using admin shares and this probably requires creating a regular file share. And then granting permissions to that share.
Sue
- homebrew01 - Monday, February 13, 2017 3:01 PMLooks like everything is "NT Services"
Those are virtual accounts. Check this article which explains it more - the section under Virtual Accounts. It also explains how to grant network access using the machine account.
Configure Windows Service Accounts and PermissionsSue
-
Certainly this thread has raised a number of issues.
* If you run xp_cmdshell from an account that has SQL sysadmin authority then it will use the security context of the SQL Server service account.
* If you run xp_cmdshell from an account without sysadmin authority then it will use the security context of the Cmdshell proxy account.
* This security context behaviour has not changed since SQL2005
* The security context used by xp_cmdshell needs to be granted the required permissions on the folders and shares it will access.
* If you reference a file or folder via an admin share such as c$, then the account used must have local admin authority on the server hosting the admin share
* There is no requirement or the SQL Server service account to have Windows local admin authority on any server. I always install SQL Server to use an account that does not have local admin authority.
* Making an account local admin just to use xp_cmdshell is a horrible idea from a security perspective.
My suggestions would be:
a) Set up standard Windows shares to reference the folders you need to access via xp_cmdshell. This removes the need for local admin authority.
b) Use a DNS alias name instead of a server name to get to the server hosting the share. This allows you to move the data to another host without changing the xp_cmdshell command line
c) Consider running xp_cmdshell using an account that is not sysadmin, which in turn needs a proxy account set up. If you have to use xp_cmdshell then (for me) using a low-privilege account to do the work is the least-bad option.
d) If you are not using AD, then creating a local account on the SQL box and the remote box that has the same name and password should allow the authentication to work. Life is much easier if you can use a domain account.
e) If you are running on Windows 2012 or higher with SQL2012 or higher, consider using a gMSA account for the SQL service account. For SQL2012 and SQL2014 the gMSA account must end with a $ character but SQL2016 and above does not have this restriction. The combination of a gMSA account and Kerberos is part of delivering the highest level of security to your SQL environment.Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
I recently had to work around an issue like this by passing through credentials with access, hope this helps 🙂
$strScriptUser = [insert]
$strPass = [insert]
$PSS = ConvertTo-SecureString $strPass -AsPlainText -Force
$cred = new-objectsystem.management.automation.PSCredential $strScriptUser,$PSS
Invoke-Command -ComputerName [insert]-scriptblock {
[insert script]
} -Credential $cred
- EdVassie - Tuesday, February 14, 2017 7:33 AM
Thanks for the tips & Advice !
- EdVassie - Tuesday, February 14, 2017 7:33 AM
a) Set up standard Windows shares to reference the folders you need to access via xp_cmdshell. This removes the need for local admin authority.
d) If you are not using AD, then creating a local account on the SQL box and the remote box that has the same name and password should allow the authentication to work. Life is much easier if you can use a domain account.
I can't figure out how the non-Admin account on Server B will have permission to access folders on Server A. Having trouble getting a non-Admin Windows account to create a mapped drive to a workgroup server.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply