Author |
Topic |
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-10-20 : 04:47:40
|
Hi, i am try to create at folder (login as 'sa', with default permission/rights) declare @sMD varchar(100),@sPath varchar(100) SET @sPath= '\\vsnet5\c$\newww' SET @sMD = 'MD ' + @sPath PRINT @sMD EXEC xp_cmdshell @sMDReturns:Access is denied.I had checked the network permissions, they are fine(i mean i can create the folder with 'MD \\vsnet5\c$\newww' command at c:\, from SQL Server)I know some of permission/rights need to set(i SQL Server), but i dont know which of them.Please tell me, what need to do for this to work?Regards,Thanks.Gurpreet S. Gill |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-10-20 : 04:56:26
|
Have you tried to create folder using same syntax from command line (command prompt) ? Is it working?Harsh AthalyeIndia."Nothing is Impossible" |
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-10-20 : 05:11:22
|
yes, its working, at server(VSNET1) i write at command prompt:C:\MD \\vsnet5\c$\newwwthis create the folder at VSNET5 with name 'newww'I think some of pemission/rights for the SQL Server account need to set to access the path.Regards, |
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-10-20 : 08:10:23
|
Adding moreEXEC master..xp_cmdshell 'DIR \\vsnet5\c'again throws same error |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-20 : 08:15:17
|
From BOL on xp_cmdshellquote: Remarksxp_cmdshell operates synchronously. Control is not returned until the command shell command completes.When you grant execute permissions to users, the users can execute any operating-system command at the Microsoft Windows NT® command shell that the account running Microsoft SQL Server™ has the needed privileges to execute.By default, only members of the sysadmin fixed server role can execute this extended stored procedure. You may, however, grant other users permission to execute this stored procedure. When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.
KH |
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-10-30 : 02:52:48
|
Hi khtanI understand that but how to set the rights/permission to do that?I am using the 'sa' user, what need to change?Regards,Thanks.Gurpreet S. Gill |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-30 : 02:55:06
|
He means the account that you use to run SQL Server, not the account you use to connect to sql server.Peter LarssonHelsingborg, Sweden |
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-10-30 : 03:43:16
|
hi Peter Larssonstill i am in fix, let me tell you, i am connected to SQL Server using SSMS(client tool only) by using login 'sa'. i am not using other login. This 'sa' login having all the default permissions/rights.So, now what i need to further to do that?Regards,Thanks.Gurpreet S. Gill |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-30 : 03:50:53
|
You need to investigate how SQL Server is running!Which account is used for SQL Server and SQL Server Agent?Check under SERVICES...Peter LarssonHelsingborg, Sweden |
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-10-30 : 03:58:08
|
Both SQL Server and SQL Server Agent having service account as 'Local System' |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-30 : 04:09:11
|
'Local System' account does not has rights to access network drive / resourcesCreate a windows account and make sure it has rights to access the \\vsnet5\c and use the a/c to startup the SQL Server Agent KH |
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-10-30 : 04:34:44
|
Hi khtani did create the Windows Account(with Administrator rights) & set the SQL Server Agent to that account(i mean to logon to that service, using newly created account), Restart both SQL Server & SQL Server Agent services, but getting the same Respose.EXEC xp_cmdshell 'DIR \\vsnet5\c'OUTPUT:Access is denied.NULLWhat else could be?Regards,Thanks.Gurpreet S. Gill |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-30 : 04:37:38
|
on the vsnet5 server, add the account used for sql server to the security tab and allow all to this account.Peter LarssonHelsingborg, Sweden |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-30 : 04:37:46
|
login to the Windows Server using the Windows Account. Go to command prompt and try to execute 'DIR \\vsnet5\c'. KH |
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-10-30 : 05:53:14
|
Hi khtanYES, I am able to execute 'DIR \\vsnet5\c'(logon as newly created account)Hi PesoI had created the new Login(using newly created account 'Rep'), now my Login is as 'VSNET5\Rep', but i am not able to login(using windows Auth. mode).Still with same.Regards,Thanks.Gurpreet S. Gill |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-30 : 05:55:41
|
No, no, no...Add to the c share on the vsnet5 machine, the account used on the SQL Server. Do NOT create a new account...Peter LarssonHelsingborg, Sweden |
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-10-30 : 06:02:30
|
Hi Peter LarssonI think i am very near to solve the problem, could you throw bit more light on this.I have SQL Server M/C named as 'VSNET5' & login 'sa', newly created Windows account 'Rep'.Please tell me where to add? what to Add?Regards,Thanks.Gurpreet S. Gill |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-30 : 06:06:17
|
Goto Windows Explorer and select the directory to where you want to export files from the SQL Server machine.Right click and select Sharing and Security.Add the SQL Server Windows account Rep to the tab and set permissions to Change in the security tab. Click ok.done.Peter LarssonHelsingborg, Sweden |
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-10-30 : 06:26:10
|
Peter Larssonmy SQL Server M/C is 'VSNET5' & i have client (SSMS) tool whos name is VSNET1. now when i write command in SSMS at Client(VSNET1) as EXEC xp_cmdshell 'DIR \\vsnet5\c' --this is Derive at SQL Serverits working fine.butEXEC xp_cmdshell 'DIR \\vsnet1\c' -- this is Drive at Client(where i want to store files)this throws the same.Regarding the quote: Originally posted by Peso Goto Windows Explorer and select the directory to where you want to export files from the SQL Server machine.Right click and select Sharing and Security.Add the SQL Server Windows account Rep to the tab and set permissions to Change in the security tab. Click ok.done.Helsingborg, Sweden
I am not able to create the share Folder, as the folder is at the client end(VSNET1) & user(Rep) is at the server(VSNET5). so now ?Regards,Thanks.Gurpreet S. Gill |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-30 : 06:35:50
|
Sigh.Peter LarssonHelsingborg, Sweden |
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-10-30 : 06:40:19
|
What?quote: Originally posted by Peso Sigh.Peter LarssonHelsingborg, Sweden
|
|
|
Next Page
|