EXEC xp_cmdshell error

  • 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

  • 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.

  • 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.

  • 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)

  • 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.

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

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