September 3, 2003 at 9:34 pm
IMHO the question needed to have a little more clarification, in short it didn't indicate whether as a developer we were a member of the sysadmins role, which would mean that we execute xp_cmdshell as the win acct that starts SQL. If we were not (which I assumed) then we would be using the proxy acct (for SQL Agent) and so the answer would be option 5 not option 4.
Steve.
Steve.
September 3, 2003 at 11:29 pm
I do not get the error (invalid user/password) at all.
When running as local system, I can quite happily get to local shares but not to shares on another server (this gives "The network path was not found.").
This is using SQL 2K SP3a.
If I try to get to a share which the SQL Server account (not local system but a domain account now) does not have permission on, I get "Access Denied"
So I am left wondering how to actually get the error message that is the subject of this QOD !
September 4, 2003 at 12:23 am
Steve,
I totally agree with you.
Maybe the error message is different when it comes from Sql Agent? Anyone knows?
Salvor
Salvor
September 4, 2003 at 1:56 am
Steve,
I also agree with you. The question stated that the user was a developer and I'd assume this means not a DBA - I think the question was a little vague here.
Maybe the error is different but I think the question was a little misleading!
Lee
September 4, 2003 at 2:11 am
A better question would involve:
xp_sqlagent_proxy_account
Everyone who is a member of sysadmin executes
xp_cmdshell
as the account running SQL server. Anyone else with access to
xp_cmdshell
runs it as whoever the sqlagent proxy is. This should have been either clarified in the question or the actual subject it was about.
Keith Henry
Keith Henry
September 4, 2003 at 3:08 am
I can't agree with the given answer. I ran the query without having permission to xp_cmdshell and got the error stated. However, running the same query logged in as SA worked. How can this be anything to do with the account that started SQL Server?
September 4, 2003 at 4:45 am
Useful script:
/*
Following on from todays QOD (on xp_cmdshell permissions) I thought this might be useful.
Takes a directory or UNC path and returns the contents as a table.
eg:
exec sp_getdir '\\mypc\c$'
returns:
datestring timestring directory filesize nameoffile
---------- ---------- ----------- ----------- ----------------------
18/07/2003 10:45 1 NULL Documents and Settings
22/01/2002 18:06 0 0 IO.SYS
22/01/2002 18:06 0 0 MSDOS.SYS
10/03/2003 15:43 0 34724 NTDETECT.COM
10/03/2003 15:43 0 214432 ntldr
02/09/2003 10:50 0 268435456 pagefile.sys
18/07/2003 11:02 1 NULL Program Files
10/07/2003 17:03 1 NULL WINNT
It handles the error mentioned in the QOD with a useful message.
eg:
exec sp_getdir '\\mypc\c$' (where I'm not sysadmin)
might return:
Current user's login is NOT a member of the sysadmin role
Non sysadmin executions of xp_cmdshell currently run as: MYDOMAIN\AUSER
You can change this with xp_sqlagent_proxy_account N'SET', <domain>, <username>, <password>
*/
create proc sp_getdir (@networkpath varchar(1000)) as
begin
set nocount on
create table #temp([output] varchar(8000))
declare @cmdstr varchar(1000)
select @cmdstr = 'dir ' + @networkpath + ' /A:D /A:S /A:H /A:R /A:A /-C /N /4 '
insert #temp
exec master.dbo.xp_cmdshell @cmdstr
select left(t.[output],10) as datestring, substring(t.[output],13,5) as timestring,
case substring(t.[output],26,3) when 'DIR' then 1 else 0 end as directory ,
case substring(t.[output],26,3) when 'DIR' then null else cast(ltrim(substring(t.[output],20,19)) as int) end as filesize,
substring(t.[output],40,1000) as nameoffile
from #temp as t where t.[output] like '[0-9][0-9]%'
if @@error <> 0 or not @@rowcount > 0 goto doh
goto done
doh:
if exists(select * from #temp where rtrim(ltrim([output])) = 'The network path was not found.')
print @networkpath + ' was not found.'
else
begin
if exists(select * from #temp where rtrim(ltrim([output])) = 'Logon failure: unknown user name or bad password.')
begin
print 'Login failure to ' + @networkpath
if is_srvrolemember ('sysadmin') = 1
print 'Current user''s login is a member of the sysadmin role' + char(10) +
'The account MSSQL runs under does not have access to ' + @networkpath
else
begin
print 'Current user''s login is NOT a member of the sysadmin role'
declare @Domain sysname, @Username sysname
create table #temp2(Domain sysname, Username sysname)
insert #temp2
exec master.dbo.xp_sqlagent_proxy_account N'GET'
select @Domain = t.Domain, @Username = t.Username from #temp2 t
print 'Non sysadmin executions of xp_cmdshell currently run as: ' + isnull(@Domain + '\' + @Username, 'No user set')
drop table #temp2
print 'You can change this with xp_sqlagent_proxy_account N''SET'', <domain>, <username>, <password>'
end
end
else
select * from #temp
end
done:
drop table #temp
set nocount off
end
go
Keith Henry
Edited by - keithh on 09/04/2003 04:52:55 AM
Keith Henry
September 4, 2003 at 4:56 am
quote:
I can't agree with the given answer. I ran the query without having permission to xp_cmdshell and got the error stated. However, running the same query logged in as SA worked. How can this be anything to do with the account that started SQL Server?
If you try to run it without permissions to xp_cmdshell you get
EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo'.
Keith Henry
Keith Henry
September 4, 2003 at 8:30 am
I also agree that the question was unclear. I tried accessing directories using UNC that I was certain the SQL Server account would not have access to and received results like the following:
Volume in drive \\myservername\Departments is Data
Volume Serial Number is XXXX-XXXX
NULL
Directory of \\myservername\Departments\IT
NULL
File Not Found
NULL
(7 row(s) affected)
Trying the default share against a server that I was sure restricted resulted in:
Access is denied.
NULL
(2 row(s) affected)
After these attempts, and not seeing the error message in the QOD, I assumed that it must result when a non-system administrator attempts to access a restricted share using xp_cmdshell and I chose answer 5 rather than 4. Can we get some QA on QOD?
Regards...
September 4, 2003 at 11:14 am
quote:
After these attempts, and not seeing the error message in the QOD, I assumed that it must result when a non-system administrator attempts to access a restricted share using xp_cmdshell and I chose answer 5 rather than 4. Can we get some QA on QOD?
We do strive to QA it on as many different environments as possible. All I can assume here, is that Windows 2003 Server (which was the environment in the Q) must be outputting a slightly different error than you have on your system (WIndows 2000?). A lot of the obscurity in the questions is due to lack of creativity during my lunch break after already doing 60 questions :).I did correct the question to clarify that you were already a sysadmin after the newsletter went out on the web page (very good point).
Brian Knight
http://www.sqlservercentral.com/columnists/bknight
Brian Knight
Free SQL Server Training Webinars
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply