none
bcp cannot write file to network folder with map credential

    Question

  • Hi,

    I am trying to write a file across the network from a table using a local account.  But I ketp getting an error

    'Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file'

    I think this an access deny error.

    I want to use domain account, but my boss said local account is sufficient.

     

    Here is a the step I took:

     

    On the destination server 2008 (let call it DestServer), I create an OS account call test_user with password 'MyP@ssword'

    On SQL Server 2008, I execute the following code:

    USE [master]
    GO
    CREATE CREDENTIAL [test_user] WITH IDENTITY = N'DestServer\test_user', SECRET = N'MyP@ssword'
    GO
    
    USE [master]
    GO
    CREATE LOGIN [test_user] WITH PASSWORD=N'MyP@ssword'
    GO
    ALTER LOGIN [test_user] ADD CREDENTIAL [test_user]
    GO
    EXEC master..sp_addsrvrolemember @loginame = N'test_user', @rolename = N'sysadmin'
    GO

    So test_user is map to DestServer through credential test_user.

    Now on SQL Server, I login as test_user and execute this query:

    USE tempdb
    CREATE TABLE XML_SAMPLE (XmlColumn xml)
    GO
    
    INSERT INTO XML_SAMPLE(XmlColumn)
    SELECT '<Root>
              <ProductDescription ProductModelID="5">
                 <Summary>Some Text</Summary>
              </ProductDescription>
    </Root>'
    
    --turn on xp_cmdshell
    EXEC sp_configure 'show advanced options', 1
    RECONFIGURE
    EXEC sp_configure 'xp_cmdshell', 1
    RECONFIGURE
    
    
    exec xp_cmdshell 'bcp tempdb.dbo.XML_SAMPLE out \\DestServer\share\\xml_sample.xml -T -c'
    
    --turn off xp_cmdshell
    EXEC sp_configure 'xp_cmdshell', 0
    RECONFIGURE


    After executing bcp above, I got this error:

    SQLState = S1000, NativeError = 0

    'Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file'

    NULL

     

    I appreciate any assistance.



    • Edited by chmat Tuesday, January 10, 2012 7:15 PM
    Tuesday, January 10, 2012 7:12 PM

Answers

  • Hello,

    http://msdn.microsoft.com/en-us/library/ms175046.aspx

    The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account.

    [...]

    When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

    The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password.

    So if your login is sysadmin, it is the sql service account that needs to have ACL on the directory. Remove the login from the sysadmin role, configure ##xp_cmdshell_proxy_account## and then in Master GRANT exec ON xp_cmdshell ... Follow the steps from the link above.
    • Marked as answer by chmat Tuesday, January 10, 2012 10:58 PM
    Tuesday, January 10, 2012 7:46 PM

All replies

  • Hello,

    http://msdn.microsoft.com/en-us/library/ms175046.aspx

    The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account.

    [...]

    When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

    The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password.

    So if your login is sysadmin, it is the sql service account that needs to have ACL on the directory. Remove the login from the sysadmin role, configure ##xp_cmdshell_proxy_account## and then in Master GRANT exec ON xp_cmdshell ... Follow the steps from the link above.
    • Marked as answer by chmat Tuesday, January 10, 2012 10:58 PM
    Tuesday, January 10, 2012 7:46 PM
  • Thanks Rudi.

     

    Sound like an answer, but let me check.  I tried to grant

     

    EXEC

    sp_xp_cmdshell_proxy_account 'DestServer\test_user','MyP@ssword'

    But got an error:

    Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1

    An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '0'.

    I'll check again. Thanks.

     
    Tuesday, January 10, 2012 8:44 PM
  • Are you logged in as a sysadmin member ?
    Tuesday, January 10, 2012 8:56 PM
  • Are you using default / named instance ? If it is named instance you must use -S parameter and supply Instance name like this

    exec xp_cmdshell 'bcp tempdb.dbo.XML_SAMPLE out \\DestServer\share\\xml_sample.xml -T -c -SInstanceName'


    http://uk.linkedin.com/in/ramjaddu
    Tuesday, January 10, 2012 9:08 PM
  • Rudi,

    Yes, I logged in as sysadmin member.

     

    RamJaddu,

    This is a default instance.

     

    Thanks.

    Tuesday, January 10, 2012 9:12 PM
  • If you want to write network shared folders you must use domain account having enough permission on the shared folders - If you want to run this thru SQL Job then SQL Agent service account should have permissions on the shared folders.
    http://uk.linkedin.com/in/ramjaddu
    Tuesday, January 10, 2012 9:35 PM
  • Check that : http://blogs.msdn.com/b/psssql/archive/2009/04/07/troubleshooting-sp-xp-cmdshell-proxy-account-errors.aspx
    it might be a UAC problem

     

    look also there : http://social.msdn.microsoft.com/Forums/en/transactsql/thread/40d67d22-28ac-44ab-b7d2-0eec25f873a6

    and try to create the system credential manually, as suggested :

    create credential ##xp_cmdshell_proxy_account## with identity = 'Domain\DomainUser', secret = 'password'

    Tuesday, January 10, 2012 9:42 PM
  • I want to use domain account, but my boss said local account is sufficient.

    Your boss is wrong. SQL Server needs to run under a domain account which has permission to the share.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, January 10, 2012 10:48 PM
  • If I right click on ther SQL Server and click on property and click on security, I can enable a proxy account and input my username and password there.  But I still got access denied.

    However, If I used:

    exec xp_cmdshell ('net use p: \\DestServer\share password /User:DestServer\test_user')

    before I bcp, everything work.  The only problem is I have to expose the password.

     

    I will try to do it another way or I just have to expose the password.  Instead of using bcp, I will to use Create Assembly.  But, I will post it on another forum.

     

    Thank you both.

    Tuesday, January 10, 2012 10:58 PM
  • He won't give a domain account for this.  Thanks.
    Tuesday, January 10, 2012 11:34 PM
  • Just to be sure, after creating the credential in the server properties, did you try with a non-sysadmin login ? Remember : the login must not be sysadmin for xp_cmdshell to use the credential.

    Tuesday, January 10, 2012 11:53 PM
  • Thanks again Rudi

     

    Remove sysadmin like you suggested and grant:

     

    EXEC

    sp_grantdbaccess test_user

    GRANT

    exec ON xp_cmdshell TO test_user

     

    As test_user:

    exec

     

    xp_cmdshell

    'bcp tempdb.dbo.XML_SAMPLE out \\DestServer\share\xml_sample.xml -T -c'

    But got this error:

    An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1326'.

     
    Wednesday, January 11, 2012 12:34 AM
  • it will work, eventually :)

    bad password ? password changed ?

    if you look here : http://blogs.msdn.com/b/psssql/archive/2008/04/10/troubleshooting-xp-cmdshell-failures.aspx

    xp_cmdshell is working, but Windows gives you an error when executing the command. If you do in a command shell :

    NET HELPMSG 1326

    you get : logon failure : unknown user name or bad password.

    Windows cannot validate the account.

    Try this : run anything on your box with a "run as" as the user / password your defined for the credential, to see if it works.

    Wednesday, January 11, 2012 9:21 AM
  • Just to update:

    I didn't know MS SQL Agent can run OS command without using xp_cmdshell.

     

    Here are the steps that actually work:

    1. Create 2 OS local accounts with identical user and password (test_user).

    2. Create credential on SQL Server.

           CREATE CREDENTIAL [test_user] WITH IDENTITY = 'SqlServer\test_user',SECRET = 'MyP@ssword'

    3. Create login on SQL Server.

          CREATE

    LOGIN [SqlServer\test_user] FROM WINDOWS

    4. Create Microsoft SQL Server Agent proxy account:

    USE [msdb]
    GO
    EXEC msdb.dbo.sp_add_proxy @proxy_name=N'test_test',@credential_name=N'ivvs_user', @enabled=1
    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'test_test', @subsystem_id=3
    

    @subsystem_id=3 meant Operating System.  It's better to use GUI (MS agent-->proxies--> New Proxy).

    5. Create a job step with type "Operating System (CmdExec)" and run as Test_User created in step 4. For command windows, use a batch file instead (e.g. c:\Myscript\RunJob.bat).  Command windows seems to only except one line.

    6. Create a batch file (RunJob.bat) such as:

    set d=%date%
    set t=%time%
    set yyyy=%d:~10,4%
    set mm=%d:~4,2%
    set dd=%d:~7,2%
    set hh=%t:~0,2%
    set mi=%t:~3,2%
    set fileout=xml_sample_%yyyy%%mm%%dd%T%hh%%mi%.xml
    
    bcp tempdb.dbo.XML_SAMPLE out \\DestServer\share\\%fileout% -T -c
    

    So I don't need to use xp_cmdshell or expose any password in the scripts.

     

    Thanks Rudi and everyone for your help.  I learn alot from this post.

    Friday, January 13, 2012 12:21 AM