Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Exec SF_Replicate 'SALESFORCE', 'Account' Failure

I'm trying to replicate / create a local copy of the account table in salesforce, when the code below is used I get a credential error message, which is strange as I can read from this table with the same login details using either an SQL select or an OpenQuery. The credentials are currently set with the password followed by the security token.

use [salesforce backups]
Exec SF_Replicate 'SALESFORCE', 'Account'

--- Starting SF_Replicate for Account
12:32:37: Drop Account_Previous if it exists.
12:32:37: Create Account_Previous with new structure.
12:32:37: Run the DBAmp.exe program.
12:32:37: DBAmp Bulk Operations. V2.13.7 (c) Copyright 2006-2011 forceAmp.com LLC
12:32:37: Replicating into Account_Previous , TTBINSIGHT / salesforce backups .
12:32:37: DBAmp is using the SQL Native Client.
12:32:37: Opening SQL Server rowset
12:32:38: Error: Failed to execute command to retrieve session id.
Source: Microsoft SQL Server Native Client 10.0
Description: Cannot initialize the data source object of OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE".
Source: Microsoft SQL Server Native Client 10.0
Description: OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE" returned message "Error 1 : INVALID_LOGIN: Invalid username, password, security token; or user locked out.".
12:32:38: Error: Unable to populate salesforce session id. Retrying...
12:32:38: Error: Failed to execute command to retrieve session id.
Source: Microsoft SQL Server Native Client 10.0
Description: Cannot initialize the data source object of OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE".
Source: Microsoft SQL Server Native Client 10.0
Description: OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE" returned message "Error 1 : INVALID_LOGIN: Invalid username, password, security token; or user locked out.".
12:32:38: Error: DBAmp.exe was unsuccessful.
12:32:38: Error: Command string is C:\"Program Files"\DBAmp\DBAmp.exe Export "Account_Previous" "TTBINSIGHT" "salesforce backups" "SALESFORCE"
--- Ending SF_Replicate. Operation FAILED.
Msg 50000, Level 16, State 1, Procedure SF_Replicate, Line 198
--- Ending SF_Replicate. Operation FAILED.
1 person has
this problem
+1
Reply
  • Can you send me a screenshot of the Security page of the link server?

    Also, is this a new install or a long standing install ?

    Also, do all replicates fail?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • This is currently a new install so its the first time I've tried using the replicate command. I've tried replicating all the tables within Salesforce and chosen a few different ones I come back to the same issue everytime, when I try the SF_BulkOps I seem to be getting the same error too, I've attached an a screen shot of the security page, any ideas what it maybe?

    --- Starting SF_BulkOps for Contact_missing
    19:28:36: Run the DBAmp.exe program.
    19:28:36: DBAmp Bulk Operations. V2.13.7 (c) Copyright 2006-2011 forceAmp.com LLC
    19:28:36: Inserting Contact_missing (TTBINSIGHT / salesforce backups).
    19:28:36: DBAmp is using the SQL Native Client.
    19:28:37: Error: Failed to execute command to retrieve session id.
    Source: Microsoft SQL Server Native Client 10.0
    Description: Cannot initialize the data source object of OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE".
    Source: Microsoft SQL Server Native Client 10.0
    Description: OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE" returned message "Error 1 : INVALID_LOGIN: Invalid username, password, security token; or user locked out.".
    19:28:37: Error: Unable to populate salesforce session id.
    19:28:37: Error: DBAmp.exe was unsuccessful.
    19:28:37: Error: Command string is C:\"Program Files"\DBAmp\DBAmp.exe insert Contact_missing "TTBINSIGHT" "salesforce backups" "SALESFORCE"
    --- Ending SF_BulkOps. Operation FAILED.
    Msg 50000, Level 16, State 1, Procedure SF_BulkOps, Line 97
    SF_BulkOps Error: 19:28:36: DBAmp Bulk Operations. V2.13.7 (c) Copyright 2006-2011 forceAmp.com LLC19:28:36: Inserting Contact_missing (TTBINSIGHT / salesforce backups).19:28:36: DBAmp is using the SQL Native Client.19:28:37: Error: Failed to execute command to retrieve session id.Source: Microsoft SQL Server Native Client 10.0Description: Cannot initialize the data source object of OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE".Source: Microsoft SQL Server Native Client 10.0Description: OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE" returned message "Error 1 : INVALID_LOGIN: Invalid username, password, security token; or user locked out.".19:28:37: Error: Unable to populate salesforce session id.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited

  • Is it possible to remove all the mappings and simply have "Be made using this security context" checked with an single sf credential entered below that ?

    That is the recommended setup as outlined in Chapter 1 of the DBAmp manual.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited

  • I'm receiving the same error but have to use individual mappings for the remote logins. Is there any other cause / resolution to this issue?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • In that case, what you need to know is that when SQL Server runs something that requires xp_cmdshell ( like sf_replicate or sf_bulkops), the cmd window is really running under the sql server service account. Then the exe running tries to login back in using the SQL Init string from the DBAMp Configuration Program. Since this is normally Trusted Connections = YES, the SQL Server Service Account is then the account in control.

    So now we get to the mapping. There needs to be a mapping for the SQL Server Service Account to some sf credentials. Otherwise you will get the error above.

    So, in short, you need to add a mapping for the SQL Server Service Account for these procs to work.

    Alternatively, you can use the sp_replicate3 stored proc instead of sf_replicate. SF_Replicate3 does NOT use xp_cmdshell and therefore has none of the above issues.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited

  • I should clarify that I'm receiving this error when using the BulkOps executable which I'm running through a CmdExec SQL Agent job step. I'm also receiving the error intermittently.

    I have confirmed that credentials are mapped for the SQL Agent Service Account and that this job has successfully run - with no changes - a few times this week.

    I've also confirmed other settings related to the DBAmp provider and Linked Server configuration. Is there anything else you can suggest?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Then maybe the issue is on the salesforce.com side and this userid is being locked out. Can you check the login history and see if there are any messages around that time that match up with a failed login attempt?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited

  • I pulled the login history for the user in question. No failed login attempts.

    Can I enable tracing for error messages at the DBAmp level (ie. the Salesforce API) level rather than at the stored procedure / SQL Agent job level? I tried using the tracing 'All' option but this produced so much additional logging that it was impossible to easily parse.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited

  • Let's do a web meeting so I can see what is going on.

    Please send me an email to support at forceamp.com
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Unable to run the replicate

    exec sf_replicate 'SALESFORCE','Account'

    The error is

  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated