SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


EXEC xp_cmdshell error


EXEC xp_cmdshell error

Author
Message
deftone_tim
deftone_tim
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 73
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
liteswitch
liteswitch
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 598
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
deftone_tim
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 73
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
Jeff Moden
SSC Guru
SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)

Group: General Forum Members
Points: 205415 Visits: 41952
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
deftone_tim
deftone_tim
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 73
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search