Post reply

xp_cmdshell dir another server

  • May 23, 2007 at 11:57 am

    #117158

    I'm getting the old Logon failure: unknown user name or bad password when trying to do a dir  from server 'N' to a share on server 'A' who are both on the same domain.

    I am trying to do this via a scheduled job.  I have the agent running on a domain account from server 'N' and setup a share on server 'A' that the agent has full permissions to see.  I can do a dir from the command prompt from server 'N' and it works ok.

    The command I am running is

    DECLARE @chvCommand varchar(255)

    SET @chvCommand = 'dir \\A\bo$\*.bak /b'

    INSERT INTO files  EXEC master..xp_cmdshell @chvCommand

    So i think the problem lies around the permissions for xp_cmdshell, but have run out of ideas.  How can I tell that the agent account can run xp_cmdshell .

    Any help would be much appreciated.

    Nickie

  • May 23, 2007 at 10:19 pm

    #708522

    Remember this phrase, "Security Context".

    SQL Server is a service just so it requires you to create an account on your OS so that it can login to the OS and run. Many people use the default of the localsystem account, and by doing so they violate one of the most basic security dont's but I'll rail about that some other time , others setup an account on the server for the SQL Server service to login as. In any case, the server you are attempting to connect to has to recognize the account the SQL Server serivce is logged in as before it will grant access to resources. When there's request across server boundaries Windows passes the credentials (aka Security Context) of the user making the requesting to the server the request is intended for. My guesses:

    1 - SQL Server is running as the localsystem account. Server 'A' isn't going to recognize SERVERN\Localsystem.

    2 - SQL Server is running as an account local to Server 'N'. Server 'A' might recognize domain accounts but not accounts which are local to Server 'N' such as SERVERN\SQLAgentAccount.

    3 - SQL Server is running as a domain account but that account doesn't have permission to access the resources on Server 'A' which you are attempting to access.

    You first step is to find out how the SQL Server service logs into your OS. You can do that by launching Enterprise Manager, then right clicking your server, clicking Properties, the clicking on the Security tab. At the bottom of that tab is the account the SQL Server service is using to log into the OS. If "System account" is selected then 1-shame on you and 2-that means SQL Server is logged in as the localsystem account. If "This account" is selected then it's running as whatever account is specified. You can also check the account by going to Control Panel -> Administrative Tools -> Services -> MSSQLSERVER.

    Your next step is to change the way the SQL Server service logs in to your OS and then grant the account being used to run the SQL Server service the appropriate rights on server 'A'.

  • May 24, 2007 at 5:09 am

    #708582

    Fantastic answer.  This is exactly the problem whereby the SQL server service was using a local admin account.  It now uses a domain account.

    Thanks for you answer.

    Cheers

    Nickie

Viewing 3 posts - 1 through 2 (of 2 total)

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