| Author |
 Topic  |
|
gsgill76
Posting Yak Master
India
137 Posts |
 Posted - 10/20/2006 : 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 @sMD
Returns:
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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 10/20/2006 : 04:56:26
|
Have you tried to create folder using same syntax from command line (command prompt) ? Is it working?
Harsh Athalye
India.
"Nothing is Impossible" |
 |
|
|
gsgill76
Posting Yak Master
India
137 Posts |
Posted - 10/20/2006 : 05:11:22
|
yes, its working, at server(VSNET1) i write at command prompt:
C:\MD \\vsnet5\c$\newww
this 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
India
137 Posts |
Posted - 10/20/2006 : 08:10:23
|
Adding more
EXEC master..xp_cmdshell 'DIR \\vsnet5\c'
again throws same error
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 10/20/2006 : 08:15:17
|
From BOL on xp_cmdshell
quote:
Remarks
xp_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
India
137 Posts |
Posted - 10/30/2006 : 02:52:48
|
Hi khtan
I 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
Sweden
29138 Posts |
Posted - 10/30/2006 : 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 Larsson
Helsingborg, Sweden |
|
|
|
gsgill76
Posting Yak Master
India
137 Posts |
Posted - 10/30/2006 : 03:43:16
|
hi Peter Larsson
still 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
Sweden
29138 Posts |
Posted - 10/30/2006 : 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 Larsson
Helsingborg, Sweden |
|
|
|
gsgill76
Posting Yak Master
India
137 Posts |
Posted - 10/30/2006 : 03:58:08
|
Both SQL Server and SQL Server Agent having service account as 'Local System'
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 10/30/2006 : 04:09:11
|
'Local System' account does not has rights to access network drive / resources
Create 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
India
137 Posts |
Posted - 10/30/2006 : 04:34:44
|
Hi khtan
i 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.
NULL
What else could be?
Regards,
Thanks.
Gurpreet S. Gill
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/30/2006 : 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 Larsson
Helsingborg, Sweden |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 10/30/2006 : 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
India
137 Posts |
Posted - 10/30/2006 : 05:53:14
|
Hi khtan
YES, I am able to execute 'DIR \\vsnet5\c'(logon as newly created account)
Hi Peso
I 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
Sweden
29138 Posts |
Posted - 10/30/2006 : 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 Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 10/30/2006 05:58:11 |
|
|
|
gsgill76
Posting Yak Master
India
137 Posts |
Posted - 10/30/2006 : 06:02:30
|
Hi Peter Larsson
I 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
Sweden
29138 Posts |
Posted - 10/30/2006 : 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 Larsson
Helsingborg, Sweden |
|
|
|
gsgill76
Posting Yak Master
India
137 Posts |
Posted - 10/30/2006 : 06:26:10
|
Peter Larsson
my 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 Server
its working fine.
but
EXEC 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
Sweden
29138 Posts |
Posted - 10/30/2006 : 06:35:50
|
Sigh.
Peter Larsson
Helsingborg, Sweden |
|
|
|
gsgill76
Posting Yak Master
India
137 Posts |
Posted - 10/30/2006 : 06:40:19
|
What?
quote:
Originally posted by Peso
Sigh.
Peter Larsson
Helsingborg, Sweden
|
|
|
|
Topic |
|
Access is denied (Creating a Folder)
