February 12, 2008 at 9:39 am
Hi All,
Using xp_cmdshell running some command prompt commands in sql server from other server.
c:\> copy \\abc\c$\temp\*.* c:\temp\ ----> running good without errors.
in Sql server
------------
Declare @sql varchar(1000)
Set @sql='copy copy \\abc\c$\temp\*.* c:\temp\'
exec master..xp_cmdshell @sql
Error
-----
Permission denied
But we are having administrator previlages to connect other servers.
Please help me.
Thanks
Kishore
February 12, 2008 at 12:05 pm
The SQL Server/Agent Service account must have enough access privilege on OS level on the specified servers. If not and that account have only in MS SQL level privilege then definitely you'll get this error.
warmest
Jay...
Thanks
Jay
http://www.sqldbops.com
February 12, 2008 at 12:21 pm
with sql2005 you nee to enable xp_cmdshell !
This is not advized !
You'd be better off by shifting this action to a sqlagent job,
and run that job with a proxy that has access to the cmdshell.
but since you're sysadmin anyway...
set nocount on
declare @CmdShellSettingBefore table (cfgname varchar(128), minimum varchar(128), maximum varchar(128), config_value varchar(128), runvalue varchar(128))
insert into @CmdShellSettingBefore
EXEC sys.sp_configure N'xp_cmdshell'
-- enable cmdshell if it is disabled on the instance
if exists(select * from @CmdShellSettingBefore where cfgname = 'xp_cmdshell' and runvalue = '0')
begin
EXEC sys.sp_configure N'xp_cmdshell', N'1'
RECONFIGURE WITH OVERRIDE
end
-- now perform your cmd stuff
Declare @sql varchar(1000)
Set @sql='copy copy \\abc\c$\temp\*.* c:\temp\'
exec master..xp_cmdshell @sql
-- disable cmdshell if it was disabled befor this script
if exists(select * from @CmdShellSettingBefore where cfgname = 'xp_cmdshell' and runvalue = '0')
begin
EXEC sys.sp_configure N'xp_cmdshell', N'0'
RECONFIGURE WITH OVERRIDE
end
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 13, 2008 at 4:04 am
After run this script also i am getting same error "Access Denied"
I had added Proxy server credentials in my sql server server.
EXEC sp_xp_cmdshell_proxy_account ' ';
GO
February 13, 2008 at 5:34 am
USE [master]
GO
CREATE CREDENTIAL [CMDShellSQLAgentPROXY] WITH IDENTITY = N'awindowsdomain\account', SECRET = N'itspassword'
GO
USE [msdb]
GO
/****** Object: ProxyAccount [CMDShellPROXY] Script Date: 02/06/2007 11:30:44 ******/
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'CMDShellPROXY',@credential_name=N'CMDShellSQLAgentPROXY',
@enabled=1,
@description=N'Provides CMDShell functionality for non-sysadmin-users'
GO
-- grant subsystem CMDShell
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'CMDShellPROXY', @subsystem_name= 'CmdExec'
GO
-- grant the login
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'CMDShellPROXY', @login_name=N'Theloginnamethatneedstheproxy'
GO
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 13, 2008 at 6:36 am
Hi
Thanks for your help.
I added above proxy server information in sql server MSDB.
and i ran the
exec master..xp_cmdshell 'DIR \\swodap1\c$\*.*'
but same error "access denied".
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'CMDShellPROXY', @login_name=N
From where i am trying to get the files that server is not a sql server serve it is a application server.
Please help me.
February 13, 2008 at 7:44 am
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'CMDShellPROXY'
, @login_name=N'thejobowner'
(jobonwer as stated in the job!)
Also keep in mind the windows account that you provided for proxy
needs to have rights to read/write to the path you want to use !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply