24 Replies Latest reply: Sep 19, 2014 3:57 PM by Chetan Chavan RSS

    QlikView Salesforce connector - problems running from server (Publisher)

      Using QlikView 10SR2 and the most current SalesForce connector.  Running Publisher on a x64 server.

       

      I have a simple extract file that selects records from a couple of Salesforce tables and creates QVDs.  The file works fine from my desktop, and also runs fine when run from the QlikView desktop on the server where publisher is installed.  However, when run as a publisher job, although it does not error or abend, it simply pulls 0 records.  So it seems to connect fine and the SELECT statements are fine, but nothing is retrieved. 

       

      I've re-installed the connector, played with proxy settings, restarted services, restarted the server, all without success.  Oddly, I tried to re-create the connection string along the way, and it actually ran ONCE from Publisher, but only that one time.  All other attempts have failed.

       

      When run from my workstation or from the Publisher/Server but using QlikView desktop, the log output is as follows:

       

      0020  CUSTOM CONNECT*Provider*SalesForceDLL.dll*XUserId*XPassword*

      0023  Portfolio:

      0024  SQL SELECT *

      0025  FROM Portfolio__c

             237 fields found: Id, IsDeleted, Name, RecordTypeId, [etc... omitted to simplify post], 603 lines fetched

      0027  STORE * FROM Portfolio INTO ..\Datasources\QVD\Salesforce\Portfolio.qvd (qvd)

      0029  DROP TABLE Portfolio

      0031  Account:

      0032  SQL SELECT *

      0033  FROM Account

             334 fields found: Id, IsDeleted, MasterRecordId, Name, [etc... omitted to simplify post], 1,774 lines fetched

      0035  STORE * FROM Account INTO ..\Datasources\QVD\Salesforce\Account.qvd (qvd)

      0037  DROP TABLE Account

      0039  exit script

           Execution finished.

       

      However, when run from Publisher, this same file generates a lot like the following, and retrieves no records:

       

      0020  CUSTOM CONNECT*Provider*SalesForceDLL.dll*XUserId*XPassword*

      0023  Portfolio:

      0024  SQL SELECT *

      0025  FROM Portfolio__c

             1 fields found: *, 0 lines fetched

      0027  STORE * FROM Portfolio INTO ..\Datasources\QVD\Salesforce\Portfolio.qvd (qvd)

      0029  DROP TABLE Portfolio

      0031  Account:

      0032  SQL SELECT *

      0033  FROM Account

             1 fields found: *, 0 lines fetched

      0035  STORE * FROM Account INTO ..\Datasources\QVD\Salesforce\Account.qvd (qvd)

      0037  DROP TABLE Account

      0039  exit script

           Execution finished.

       

      I've also tried using specific columns in the SELECT rather than the * wildcard, but no joy.

       

      Any ideas from folks using Salesforce in a Server/Publisher environment?  Thanks so much for any inputs or suggestions.

        • Re: QlikView Salesforce connector - problems running from server (Publisher)
          Fernando Suzuki

          Hi,

           

          I've never pulled data from SalesForce so no experience on that. But first thing I'd check is to see if the user configured to run the QV service has the same privileges as the user you logged in your workstation.

           

          Hope this helps you

          Fernando

          • Re: QlikView Salesforce connector - problems running from server (Publisher)
            Erich Shiino

            Hi,

            Did you try to use QlikTech's template for sales force?

            http://us.demo.qlik.com/detail.aspx?appName=Salesforce.qvw

             

             

            There, the connections string is similar but has an additional paramenter in the middle. I'm not sure if this may be thedifference.

            Did you include the 26-char token in the password?

             

             

            CUSTOMCONNECT TO "Provider=SalesForceDLL.dll;F21;UserId=$(SF_Username);Password=$(SF_Password);";

             

            Regards,

             

            Erich

            • Re: QlikView Salesforce connector - problems running from server (Publisher)
              Shemeel Basheer

              HI,

               

              Did you leased a license to the Service Account?

               

              Assign a license to Service account and lease it by logging into the Service account. if you have a cluster you have to lease in the machines running QDS. Hope this helps

              • Re: QlikView Salesforce connector - problems running from server (Publisher)

                Thanks so much to all, resovled this, but wanted to update the information here for the potential benefit of others.

                 

                In my case, it was an organizational firewall issue that was preventing the SalesForce connector from reaching their datacenter.  I found helpful information by searching the Salesforce site for "Mirrorforce" (which is the technology they use for the API Server).  In that document, I found the ports that I needed to request open.

                 

                Here are the IP address blocks for the Salesforce relevant datacenters.  Once I had our security folks open these, the connector worked as it should:

                 

                1. 204.14.232.0/23   East Coast Data Center (set one)
                  204.14.237.0/24   East Coast Data Center (set two)
                  96.43.144.0/22    MidWest Data Centers
                  96.43.148.0/22    MidWest Data Centers
                  204.14.234.0/23   West Coast Data Center (set one)
                  204.14.238.0/23   West Coast Data Center (set two)
                  182.50.76.0/22    Japan Data Center

                 

                Ports:  80, 443

                • Re: QlikView Salesforce connector - problems running from server (Publisher)

                  Hi Donald,

                                Thanx for sharing the information about the SF data centers.Hope you can help me out with one more issue.

                  I too am extracting data from SF,and its working fine as long as the proxy settings are configured in IE,

                  But we have a option of specifying the proxy in the connection string itself.However its not fetching anydata.

                  eg

                  CUSTOM CONNECT TO "Provider=SalesForceDLL.dll;F21;UserId=<uid>;Password=<password>;Proxy=http=http://<proxy:port>;ProxyBypass=<local>;";

                   

                  What i found in the refernce manual is that we need to use the syntax describe by windows which says that we need to encapsulate the proxy string in double quots (") ie."Proxy=http=http://<proxy:port>;ProxyBypass=<local>"

                  but that conflicts with the other two " already in the string.

                   

                                  Anyone any idea??

                    • Re: QlikView Salesforce connector - problems running from server (Publisher)

                      alchemist,

                       

                      I am able to specify the Proxy server in the CUSTOM CONNECT string using any of the following formats:

                       

                      CUSTOM CONNECT TO "Proxy=10.10.10.10:880;Provider=SalesForceDLL;F21...etc..."

                       

                      CUSTOM CONNECT TO "Proxy=www.site.com:8080;Provider=SalesForceDLL;F21...etc..."

                       

                      CUSTOM CONNECT TO "Proxy=http://www.site.com:8080;Provider=SalesForceDLL;F21...etc..."

                       

                      So just add the proxy server with port with the other parameters in the connect string, and it doesn't matter where in the string (putting it first as I have done works fine).  You should not add any extra quote marks, nor shoud you need the extra "http=" like you see in the Salesforce reference guide.

                       

                      Don

                    • Re: QlikView Salesforce connector - problems running from server (Publisher)

                      Donald, I am having the exact same issue... However, we performed your suggested fix as shown below but the problem still creates blank QVD files when run through publisher.

                       

                       

                       

                      The 2 internal IP Addresses have rules to all of those Saleforce networks using ports 80 and 443.  Below shows that neither of those servers have used the firewall rules.  

                       

                      There is no proxy exception for salesforce.com so anything that is configured to use the proxy server will go through WebSense and not use these firewall rules.

                       

                      cch-fw1/corp/act/pri# show access-list | in 10.0.3.112

                      access-list inside_access_in line 521 extended permit tcp host 10.0.3.112 object-group SalesForce object-group WEB 0x620cde33

                        access-list inside_access_in line 521 extended permit tcp host 10.0.3.112 204.14.232.0 255.255.254.0 eq www (hitcnt=0) 0x6ab2a1a0

                        access-list inside_access_in line 521 extended permit tcp host 10.0.3.112 204.14.232.0 255.255.254.0 eq https (hitcnt=0) 0x11e4b943

                        access-list inside_access_in line 521 extended permit tcp host 10.0.3.112 204.14.237.0 255.255.255.0 eq www (hitcnt=0) 0x1c30e022

                        access-list inside_access_in line 521 extended permit tcp host 10.0.3.112 204.14.237.0 255.255.255.0 eq https (hitcnt=0) 0xcb795718

                        access-list inside_access_in line 521 extended permit tcp host 10.0.3.112 96.43.144.0 255.255.252.0 eq www (hitcnt=0) 0x76f4eaea

                        access-list inside_access_in line 521 extended permit tcp host 10.0.3.112 96.43.144.0 255.255.252.0 eq https (hitcnt=0) 0xf8223438

                        access-list inside_access_in line 521 extended permit tcp host 10.0.3.112 96.43.148.0 255.255.252.0 eq www (hitcnt=0) 0xa4c300b0

                        access-list inside_access_in line 521 extended permit tcp host 10.0.3.112 96.43.148.0 255.255.252.0 eq https (hitcnt=0) 0x4025aee8

                      access-list inside_access_in line 521 extended permit tcp host 10.0.3.112 204.14.234.0 255.255.254.0 eq www (hitcnt=0) 0x9b6c71ee

                        access-list inside_access_in line 521 extended permit tcp host 10.0.3.112 204.14.234.0 255.255.254.0 eq https (hitcnt=0) 0x597e69fb

                        access-list inside_access_in line 521 extended permit tcp host 10.0.3.112 204.14.238.0 255.255.254.0 eq www (hitcnt=0) 0xa24da4cf

                        access-list inside_access_in line 521 extended permit tcp host 10.0.3.112 204.14.238.0 255.255.254.0 eq https (hitcnt=0) 0x5df0259e

                        access-list inside_access_in line 521 extended permit tcp host 10.0.3.112 202.129.242.0 255.255.254.0 eq www (hitcnt=0) 0xb10ac25c

                        access-list inside_access_in line 521 extended permit tcp host 10.0.3.112 202.129.242.0 255.255.254.0 eq https (hitcnt=0) 0x014629b3

                        access-list inside_access_in line 521 extended permit tcp host 10.0.3.112 182.50.76.0 255.255.252.0 eq www (hitcnt=0) 0x4f99e54a

                        access-list inside_access_in line 521 extended permit tcp host 10.0.3.112 182.50.76.0 255.255.252.0 eq https (hitcnt=0) 0x97fe5d24

                       

                      cch-fw1/corp/act/pri# show access-list | in 10.0.25.199

                      access-list inside_access_in line 522 extended permit tcp host 10.0.25.199 object-group SalesForce object-group WEB 0xfe10e266

                        access-list inside_access_in line 522 extended permit tcp host 10.0.25.199 204.14.232.0 255.255.254.0 eq www (hitcnt=0) 0xe483e1a1

                        access-list inside_access_in line 522 extended permit tcp host 10.0.25.199 204.14.232.0 255.255.254.0 eq https (hitcnt=0) 0xb972f1d7

                        access-list inside_access_in line 522 extended permit tcp host 10.0.25.199 204.14.237.0 255.255.255.0 eq www (hitcnt=0) 0xb1db40c9

                        access-list inside_access_in line 522 extended permit tcp host 10.0.25.199 204.14.237.0 255.255.255.0 eq https (hitcnt=0) 0x4ee8008e

                        access-list inside_access_in line 522 extended permit tcp host 10.0.25.199 96.43.144.0 255.255.252.0 eq www (hitcnt=0) 0xeb921b66

                        access-list inside_access_in line 522 extended permit tcp host 10.0.25.199 96.43.144.0 255.255.252.0 eq https (hitcnt=0) 0x32b02cb8

                        access-list inside_access_in line 522 extended permit tcp host 10.0.25.199 96.43.148.0 255.255.252.0 eq www (hitcnt=0) 0x060d8397

                        access-list inside_access_in line 522 extended permit tcp host 10.0.25.199 96.43.148.0 255.255.252.0 eq https (hitcnt=0) 0x2ea476c8

                        access-list inside_access_in line 522 extended permit tcp host 10.0.25.199 204.14.234.0 255.255.254.0 eq www (hitcnt=0) 0x26c41ae3

                        access-list inside_access_in line 522 extended permit tcp host 10.0.25.199 204.14.234.0 255.255.254.0 eq https (hitcnt=0) 0x034f53fd

                        access-list inside_access_in line 522 extended permit tcp host 10.0.25.199 204.14.238.0 255.255.254.0 eq www (hitcnt=0) 0xaf1c0230

                        access-list inside_access_in line 522 extended permit tcp host 10.0.25.199 204.14.238.0 255.255.254.0 eq https (hitcnt=0) 0x471de855

                        access-list inside_access_in line 522 extended permit tcp host 10.0.25.199 202.129.242.0 255.255.254.0 eq www (hitcnt=0) 0x7fd7e0f1

                        access-list inside_access_in line 522 extended permit tcp host 10.0.25.199 202.129.242.0 255.255.254.0 eq https (hitcnt=0) 0x3a00a95c

                        access-list inside_access_in line 522 extended permit tcp host 10.0.25.199 182.50.76.0 255.255.252.0 eq www (hitcnt=0) 0x0b2539f2

                        access-list inside_access_in line 522 extended permit tcp host 10.0.25.199 182.50.76.0 255.255.252.0 eq https (hitcnt=0) 0x34bc2f98

                      • Re: QlikView Salesforce connector - problems running from server (Publisher)

                        Hi Donald,

                         

                        As of now i have tried to turn off the firewall just to assure that all IP and ports are allowed, however i am still getting

                        0 line fetched when reloading from publisher.. well of course with the same scenario of yours by manually reloading the file itself it works, i hope you can give some advice btw im also using windows 2008 server r2 on 64 bit, Thanks!

                         

                         

                        Add note : i am using different credentials

                         

                        When connecting to the windows server 2008 r2

                             --qvdadmin

                        When connecting to salesforce

                             --user1@someemail.com (sample only for confidentiality)

                          • Re: QlikView Salesforce connector - problems running from server (Publisher)

                            Hi Jayson Xxx, Donald,

                             

                            Thank you for sharing info. I am also having exactly the same issue and my credentials are the same aas Jayson Xxx.

                            Can someone please advise if any solution for this, please?

                             

                            Thank you in advance,


                            • Re: QlikView Salesforce connector - problems running from server (Publisher)

                              Jason and Shanti...

                              I'm not sure what to suggest behond what I've posted above.  If indeed it works fine from this same server when you logon and manually re-load, then again I would verify that it still works if you logon to your server with whatever credentials you have specified for the Publisher/Distribution service on the server.  You can do this in one of two ways:

                               

                              1) change the credentials for the Publisher/Distribution server to your own account, and stop/restart the service, then retry to reload using Publisher, or

                              2) use the credentials that you've specified for the Publisher/Distribution service when you interactively logon to the server, then manually try to reload using those credentials (instead of your own).

                               

                              If you are using a service account where interactive logon is not permitted for your Distribution service, then option #1 will be your easiest way to verify.

                            • Re: QlikView Salesforce connector - problems running from server (Publisher)

                              managed to get this working on my laptop on the corporate network but not on the server.

                               

                              On the server we need to force use of the proxy server, but the suggested syntax is comes up with an error

                               

                              ...Proxy=http=http://<proxyIP:proxyPort>;....

                               

                              Any suggestions?

                              • Re: QlikView Salesforce connector - problems running from server (Publisher)

                                Hi Donald,

                                I have the connector in place for SFDC which is extracting data on a daily basis.

                                the problem is.. with the same settings, there are some days( about 1 or 2 days in a month) where the data fetch is incomplete for which we do a manual reload post which the data fetches completely.

                                The networks are working just fine.

                                Any clue why this must be happening? or any solution to it.

                                Looking forward for a revert. thanks