help with the xp_cmdshell
-
Trying to run a .vbs file from SQL/SQL job. The vbs file runs fine when I click on it.
I am using SQL 2008R2
Exec xp_cmdshell 'cscript //nologo
"\\servername\G$\path\ad_groups.vbs"'
This is the output Input Error: There is no script file specified.
-
xp_cmdShell doesn't use your personal credentials. it uses the account SQL starts with instead, and in your case that account doesn't have access to the network path:
this is a common security misconception/"gotcha". The problem is that when you access any resource OUTSIDE of SQL server, like network shares, local hard drives and folders,xp_cmdshell,sp_OA type functions etc, it doesn't matter what YOUR credentials are. Whether you are Domain Admin,Local Admin , logged in as sa, administrative login on a laptop, etc, because SQL will not carry those credentials to the "outside of SQL" security context.
SQL WILL pass your credentials to a linked server, but anything else is using an account you did not intuitively expect it to use.
SQL Server uses either the account set up as the proxy account, or if that is left blank(the default) it uses account it starts with to try and access the resource:
or if the above was blank, the account in services:
That account is often an account which has never logged into the domain, and was never assigned permissions to get to the local disk or network share.
As a result, you usually need to create a domain account in Active Directory, specifically grant it share access if it doesn't inherit it from Domain\Users or Domain\AuthenticatedUsers and change the account SQL Server starts with to that account.
Once that is done, and you stop and start the SQL service to make it use that account instead of old running values, your linked server/xp_cmdshell would work.
you can prove this is the issue by simply putting in your credentials, with your domain account and password, and confirm the external object you were trying to access/use works when SQL is run your credentials, so you'd know you need a domain account to access the resource.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell thanks for the fast response. I was trying to run this via a t-sql command in SSMS first to see if SQL had any issues. Then after I passed this test I would move it to a job. The command in SSMS should be running under my login so I should not have any permission issues. Or I think I should not have permission issues.
-
The command in SSMS should be running under my login ....
That is the gotcha!
xp_cmdshell will not use your login credentials. nope. not a chance.
you expect it to use it, but it doesn't....it's very counter-intuitive.
that's why it works from a command prompt, but fails in tsql...security. it won't work until till you change the login in one of the two places i screenshot-ed.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
sweet
Thanks
-
Hi
Lowell is correct.
If you are a sys admin then XP_CmdShell uses the credentials of SQL Server Engine Service account. It should have all the required privileges on the windows side.
If you are a non sysadmin then you have to begranted Execute privileges on XP_Cmdshell and configure a Proxy account for non-sysadmins.
Check this link.
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx
Thank You,
Best Regards,
SQLBuddy
-
Thanks guys
I have the permissions set correctly now I think. I will post the script I am trying to run so you can see if I am off or not. The script runs just fine if I click on it to run it. When I run this via a sql job the job does not fail but never completes. The script takes a few seconds to run outside of SQL.
' *****************************
' * List All Groups in the Domain and
' * List All Members of each Group
' * group name <tab> type <tab> member name <tab> type
' * Prompt for text file name.
' * Written by James Anderson, July 2009
' *****************************
' Variables
Const MY_DOMAIN = "dc=??
' *****************************
' Start Main
On Error Resume Next
Const ADS_SCOPE_SUBTREE = 2
Const ADS_GROUP_TYPE_GLOBAL_GROUP = &h2
Const ADS_GROUP_TYPE_LOCAL_GROUP = &h4
Const ADS_GROUP_TYPE_UNIVERSAL_GROUP = &h8
Const ADS_GROUP_TYPE_SECURITY_ENABLED = &h80000000
Const E_ADS_PROPERTY_NOT_FOUND = &h8000500D
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
' Setup the output file
If UCase( Right( WScript.FullName, 12 ) ) = "\CSCRIPT.EXE" Then
WScript.StdOut.Write MYPROMPT & " "
strMyFileName = WScript.StdIn.ReadLine
Else
strMyFileName = "\\serverpath\adoutput.txt"
End If
if strMyFileName = "" then
wscript.quit
end if
Set WshShell = CreateObject("WScript.Shell")
Set WshSysEnv = WshShell.Environment("PROCESS")
strMyFileName = "\\serverpath\adoutput.txt"
Set WshSysEnv = nothing
Set WshShell = nothing
if objFSO.FileExists(strMyFileName) then
'objFSO.DeleteFile(strMyFileName)
wscript.echo "That filename already exists"
wscript.quit
end if
' Get a recordset of groups in AD
Set objMyOutput = objFSO.OpenTextFile(strMyFileName, ForWriting, True)
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.CommandText = _
"SELECT ADsPath, Name FROM 'LDAP://" & MY_DOMAIN & "' WHERE objectCategory='group'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
' For each Group, Get group properties
Do Until objRecordSet.EOF
Set objGroup = GetObject(objRecordSet.Fields("ADsPath").Value)
strGroupName = objRecordSet.Fields("Name").Value
If objGroup.GroupType AND ADS_GROUP_TYPE_LOCAL_GROUP Then
strGroupDesc = "Domain local "
ElseIf objGroup.GroupType AND ADS_GROUP_TYPE_GLOBAL_GROUP Then
strGroupDesc = "Global "
ElseIf objGroup.GroupType AND ADS_GROUP_TYPE_UNIVERSAL_GROUP Then
strGroupDesc = "Universal "
Else
strGroupDesc = "Unknown "
End If
If objGroup.GroupType AND ADS_GROUP_TYPE_SECURITY_ENABLED Then
strGroupDesc = strGroupDesc & "Security group"
Else
strGroupDesc = strGroupDesc & "Distribution group"
End If
' Check if there are members
err.clear
arrMemberOf = objGroup.GetEx("Member")
If Err.Number = E_ADS_PROPERTY_NOT_FOUND then
' Write a line to the outputfile with group properties and no members
objMyOutput.WriteLine(strGroupName & vbtab & strGroupDesc & vbtab & "<null>" & vbtab & "<null>")
Else
' For each group member, get member properties
For Each strMemberOf in arrMemberOf
Set objMember = GetObject("LDAP://" & strMemberOf)
strMemberName = right(objMember.Name,len(objMember.Name)-3)
' Write a line to the outputfile with group and member properties
objMyOutput.WriteLine(strGroupName & vbtab & strGroupDesc & vbtab & strMemberName & vbtab & objMember.Class)
set objMember = nothing
Next
End If
objRecordSet.MoveNext
Set objGroup = nothing
Loop
objMyOutput.close
'wscript.echo "Done!"
-
Hi
If you are a sysadmin, then create a Activex Script proxy with your windows account as the credential for that proxy and run the script as an ACTIVEX Script Step and with "Run as" the above created proxy.
As the Script is being run from the remote server, SQL Server Engine account on the local computer must have access to the file path on the remote server.
Thank You,
Best Regards,
SQLBuddy.
-
I have this error now
The @output_file_name parameter is not valid for a job step of type 'ActiveScripting'. (Microsoft SQL Server, Error: 14545)
-
Hi
What is @output_file_name parameter ?
What command are you typing in the Activex Script Task?
If you are using TSQL command like Exec xp_cmdshell .... then change the step type to TSQL and run the Job.
You don't need any proxies for the TSQL job step.
Thank You,
Best Regards,
SQLBuddy
-
' Setup the output file
If UCase( Right( WScript.FullName, 12 ) ) = "\CSCRIPT.EXE" Then
WScript.StdOut.Write MYPROMPT & " "
strMyFileName = WScript.StdIn.ReadLine
Else
strMyFileName = "\\serverpath\adoutput.txt"
End If
if strMyFileName = "" then
wscript.quit
-
1
-
Hi
Whenever you are adding a Job step "@output_file_name parameter" is not valid for a job step of type 'ActiveScripting'.
That's the reason for the error.
The @output_file_name parameter is not valid for a job step of type 'ActiveScripting'. (Microsoft SQL Server, Error: 14545)
So try to create a TSQL step and run the command
Exec xp_cmdshell 'cscript //nologo
"\\servername\G$\path\ad_groups.vbs"'
TSQL step doesn't need a proxy. It runs under the account that has created the Job.
Since you are a SA, XP_Cmdshell runs under SQL Eng account and also make sure that SQL Eng account has access to the remote .vbs file
Thank You,
Best Regards,
SQLBudy.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply