calling xp_cmdshell with an unregistered exe
-
i am working on dynamically trying to set spns in the directory with the setspn.exe utility. It does not register itself on install so it can be called from any cmd prompt, but requires the prompt to be at c:\Program FIles\Resource Kit". When I call xp_cmdshell as shown here
exec master.dbo.xp_cmdshell '"C:\Program Files\Resource Kit\setspn"'
I get a null record returned. When executing the cmd from an actual cmd prompt copied and pasted from the above code, I get the help instructions from setspn. I think it has to do with the spaces in the path name, and have tried using the old dos path of c:\Progra~1\blah. I am using the same service account in QA and the local log on.
Anyone see this behaviour before?
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000 -
hi,
you may want to try making a VARCHAR to put the string in then passing the param to the command
Just be carefull the xp_cmdshell is a dangerous proc as it is operated with the boxes authority. I have created System Admins on poorly secured SQL boxes with relatively little code. You may want to do a search on "XP_Cmdshell exploits"
Tal Mcmahon
-
I have tried passing a param into the exec command without success. I get the same null row as a return. I have studied xp_cmdshell, and realize the power it posseses, thank you for the heads up though
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000 -
As a test, I re-installed setspn to c:\. It had the same result. From what I can see, this is the expected behaviour of setspn when called from xp_cmdshell. While setspn still does what ever it is told, the results are not returned in a row set to QA.
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000 -
It has to do with the way SETSPN.EXE writes output. Note that even from the command line, you cannot redirect the output to a file. For example, run
C> SETSPN -L computername >output.txt
Then,
C> type output.txt
The file will be empty. (Or, just dir output.txt to see a zero-length file).
Unfortunately, I don't think you can capture the output from SETSPN.
-
two things:
1) double quotes since the directory path needs spaces.
(your installation path may be different)
2) .exe at the end of setspn
Here's an example and it's output from QA
exec master.dbo.xp_cmdshell '"C:\Program Files\Support Tools\setspn.exe"'
output
----------------------------------------------------------------
Usage: C:\Program Files\Support Tools\setspn.exe [switches data]
Where "computername" can be the name or domain\name
NULL
Switches:
-R = reset HOST ServicePrincipalName
Usage: setspn -R computername
-A = add arbitrary SPN
Usage: setspn -A SPN computername
-D = delete arbitrary SPN
Usage: setspn -D SPN computername
-L = list registered SPNs
Usage: setspn [-L] computername
Examples:
setspn -R daserver1
It will register SPN "HOST/daserver1" and "HOST/{DNS of daser
setspn -A http/daserver daserver1
It will register SPN "http/daserver" for computer "daserver1"
setspn -D http/daserver daserver1
It will delete SPN "http/daserver" for computer "daserver1"
NULL
(20 row(s) affected)
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
rudy,
did your QA happen to be local to the SQL Server, or was it on a remote client? Interesting.
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000 -
I run 2K3 server on my desktop with 2 local named sql instances. I will install the setspn utility on a remote server and re-execute just to verify things for you.
By the way, the output appears to be truncated (and it is). My QA output results for testing are set to 32 charachert per column. Also, xp_cmdshell is limited in it's output to 255 characters in with.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Below is the query executed locally in QA:
exec master.dbo.xp_cmdshell '"C:\Program Files\Support Tools\setspn.exe"'
and the resulting output from a remote production server:
output
----------------------------------------------------------------
Usage: C:\Program Files\Resource Kit\setspn.exe [switches data]
Where "computername" can be the name or domain\name
NULL
Switches:
-R = reset HOST ServicePrincipalName
Usage: setspn -R computername
-A = add arbitrary SPN
Usage: setspn -A SPN computername
-D = delete arbitrary SPN
Usage: setspn -D SPN computername
-L = list registered SPNs
Usage: setspn [-L] computername
Examples:
setspn -R daserver1
It will register SPN "HOST/daserver1" and "HOST/{DNS of daser
setspn -A http/daserver daserver1
It will register SPN "http/daserver" for computer "daserver1"
setspn -D http/daserver daserver1
It will delete SPN "http/daserver" for computer "daserver1"
NULL
(20 row(s) affected)
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
first of all, thanks for the input rudy
I have tried the different iterations that you described above without sucsess. I do have some differences in environment that may be causing the issue. I am running SQL 2k sp3a MDAC 2.8 on a W2000 SP 4 machine and my QA client is a 2K pro SP4 machine. This may be causing it. Also my version of setspn is 5.2.3630.0 what is yours?
I may have to accept the lack of feedback from setspn, it would be nice to be able to setspn -L before making any modifications though, that way i could parse through the output to determine if any changes needed to be made.
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000 -
Cody,
My setup is almost identical to yours, and I get no results either. Just one row with the value NULL. (I think blank lines get returned as NULL when using xp_cmdshell, based on testing with the DIR command). I originally was using SETSPN.EXE version 5.00.2184.1, but downloaded the newest version, 5.2.3630.0. Didn't make any difference. Other commands work fine (like dir *.*, etc) and return results. I logged onto 2 of my servers to test this, and it doesn't work locally either.
Microsoft released PRB regarding the failure of the file compare utility (FC) when run with xp_cmdshell, but that program works for me.
I still find it interesting that even from a command window, the output cannot be redirected to a file.
Mike
-
I saw the same thing happen yesterday, when trying to output to a text file from the cmd prompt or from xp_cmdshell. I am beginning to think this is a difference between 2k and 2k3 OS since Rudy is getting output rows. I mean, what else could it be? Looks like I will just have to let this dog lie for now. At least I am still able to modify spns on the fly, I just can't SEE that I am doing it! Thanks to everyone for their added insights.
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000 -
I've a mixed environment here as well. Servers are Win2K SP3, MDAC 2.6 and 2.8; Win2KSP4 MDAC 2.6 and 2.8; and Win2K3 MDAC 2.8. My client is Win2K3 Server, MDAC 2.8; and I've even tried it on a laptop Win2K Pro SP4, MDAC 2.8. The version of setspn that I an using is 6.0.2448.0. All SQL Servers are SQL 2K, SP3 w/hot fix 818.
Also, my apologies, I totally missed the parameter part. So here's an example with the one you need:
exec master.dbo.xp_cmdshell '"C:\Program Files\Support Tools\setspn.exe" -L PMH-MGMT-04'
An item of note the drive\directory\filename is in double quotes, the parameter is outside of it.
And the results.
output
----------------------------------------------------------------
Registered ServicePrincipalNames for CN=PMH-MGMT-04,OU=Managemen
SMTPSVC/PMH-MGMT-04
SMTPSVC/pmh-mgmt-04.pmh.portermemorial.org
HOST/PMH-MGMT-04
HOST/pmh-mgmt-04.pmh.portermemorial.org
NULL
(6 row(s) affected)
Other things that need to be compared might be service accounts. We have common but separate accounts for the SQL Server service and SQL Agent service which are domain users and are in a group that is in the Local Administrators group on each SQL Server. In this example (interactively via QA) only the SQL Server service account needs to be a domain user and in the Local Administrators group on each SQL Server. If you want to perform this as a scheduled task then you'll need to do the same for the SQL Agent service account.
Also, I recently had some SPN issues and found out that even as a Domain Administrator I could not Add or Delete SPN's for my servers with setspn, however Listing them worked just fine. We had to have the AD engineer here perform the modifications using ADSIEdit on the Domain controllers and wait for the changes to be replicated.
I am also able to capture the output from a DOS window as well (example below):
C:\Program Files\Support Tools>setspn -L PMH-MGMT-04 > t.txt
C:\Program Files\Support Tools>dir t.txt
Volume in drive C is WIN2K3
Volume Serial Number is D008-1DA9
Directory of C:\Program Files\Support Tools
02/09/2005 02:46 PM 258 t.txt
1 File(s) 258 bytes
0 Dir(s) 20,779,543,040 bytes free
C:\Program Files\Support Tools>type t.txt
Registered ServicePrincipalNames for CN=PMH-MGMT-04,OU=Management,OU=Servers,OU=PMH,DC=pmh,DC=portermemorial,DC=org:
SMTPSVC/PMH-MGMT-04
SMTPSVC/pmh-mgmt-04.pmh.portermemorial.org
HOST/PMH-MGMT-04
HOST/pmh-mgmt-04.pmh.portermemorial.org
C:\Program Files\Support Tools>
Hope that this additional information ... helps
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply