Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

EXEC xp_cmdshell error Expand / Collapse
Author
Message
Posted Friday, June 28, 2013 9:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 12, 2014 7:43 PM
Points: 6, Visits: 67
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
Post #1468609
Posted Monday, July 01, 2013 3:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:47 AM
Points: 69, Visits: 557
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.
Post #1468982
Posted Monday, July 01, 2013 8:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 12, 2014 7:43 PM
Points: 6, Visits: 67
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.
Post #1469074
Posted Monday, July 01, 2013 6:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:43 PM
Points: 35,953, Visits: 30,242
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1469247
Posted Monday, July 01, 2013 6:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 12, 2014 7:43 PM
Points: 6, Visits: 67
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.
Post #1469260
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse