EXEC xp_cmdshell error

Question

EXEC xp_cmdshell error

deftone_tim

Old Hand

Points: 324
More actions
June 28, 2013 at 9:57 am

#274614

So I am trying to run a powershell script in SQL Management Studios:
netsh advfirewall firewall add rule name="SQL_Admin_IPs" dir=in action=allow protocol=TCP localport=any profile=any enable=
yes remoteip="127.0.0.1,127.0.0.2,127.0.0.3"
exit-pssession
set-item wsman:\localhost\Client\TrustedHosts -value 127.0.0.2 -force
$pw = convertto-securestring -AsPlainText -Force -String P@$$word
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist "administrator",$pw
$session = new-pssession -computername 127.0.0.2 -credential $cred
invoke-command -script {netsh advfirewall firewall add rule name="SQL_Admin_IPs" dir=in action=allow protocol=TCP
localport=any profile=any enable=yes remoteip="127.0.0.1,127.0.0.2,127.0.0.3"} -session $session
exit-pssession $session
set-item wsman:\localhost\Client\TrustedHosts -value 127.0.0.3 -force
$pw2 = convertto-securestring -AsPlainText -Force -String P@$$word
$cred2 = new-object -typename System.Management.Automation.PSCredential -argumentlist "administrator",$pw2
$session2 = new-pssession -computername 127.0.0.3 -credential $cred2
invoke-command -script {netsh advfirewall firewall add rule name="SQL_Admin_IPs" dir=in action=allow protocol=TCP
localport=any profile=any enable=yes remoteip="127.0.0.1,127.0.0.2,127.0.0.3"} -session $session2
exit-pssession $session2
It runs the first part of the script but it errors out when attempting the second part of the script:
----------------------------------------------------------------------------------------------------------------------
new-pssession : [127.0.0.2] Connecting to remote server 127.0.0.2
failed with the following error message : WinRM cannot process the request.
The following error with errorcode 0x8009030d occurred while using Negotiate
authentication: A specified logon session does not exist. It may already have
been terminated.
Possible causes are:
-The user name or password specified are invalid.
-Kerberos is used when no authentication method and no user name are
specified.
-Kerberos accepts domain user names, but not local user names.
-The Service Principal Name (SPN) for the remote computer name and port does
not exist.
-The client and remote computers are in different domains and there is no
trust between the two domains.
After checking for the above issues, try the following:
-Check the Event Viewer for events related to authentication.
-Change the authentication method; add the destination computer to the WinRM
TrustedHosts configuration setting or use HTTPS transport.
----------------------------------------------------------------------------------------------------------------------
At my wits end with this thing. I have scoured the interwebs but can't seem to find anything that pertains to this. Any help would be appreciated.
Using SQL Server 2008

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

liteswitch SSCommitted Points: 1824 More actions · Answer 1

Are you trying to run the powershell script against the local SQL Server? I know from experience when connecting using WMI (via any method) you cannot supply username/password for the local connection, you can only supply a username/password combo when connecting to remote machines. For local WMI access, it uses the credentials of the logged in user running it.

deftone_tim Old Hand Points: 324 More actions · Answer 2

I am running it against the local SQL Server. I had a feeling it was erroring out because of some credential based rule. I may try breaking the Powershell script up and creating a new SQL Server connection to that server and running the script on that local machine.

Jeff Moden SSC Guru Points: 1004593 More actions · Answer 3

I am curious a bit... how does this turn out to be an "EXEC xp_cmdshell error" like the title of this post suggests?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

deftone_tim Old Hand Points: 324 More actions · Answer 4

The powershell script is creating a Firewall rule on a different server. The script runs fine when I execute it in powershell, however when I execute the script via t-SQL command it gives me an authentication error. It only executes the portion of the script that pertains to the local server.

EXEC xp_cmdshell error

Cookies on SQLServerCentral