Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Dynamic Export to Excel - Connection Failed RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi all,

    I'm having an issue at a client's implementation.  Everything seems to be functioning fine with the exception of dynamic exports to Excel.  When a user attempts to dynamically export data to Excel, when they go to bring in the data from CRM they get the following error:

    Connection failed:
    SQLState: '01000'
    SQL Server Error: 10060
    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
    Connection failed:
    SQLState: '08001'
    SQL Server Error: 17
    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.

    If I create a new spreadsheet and create a connection to SQL directly it works fine.

    Environment:
    Server
    CRM 4 - On Premise Roll-up 12
    Windows Server 2008
    SQL Server 2008 R2

    Workstation
    Windows XP SP2
    Office 2003
    CRM Client Roll-up 12

    Both the workstations and server are connected to the domain and functioning as expected with the exception of this error.

    Any thoughts?

    Wednesday, December 1, 2010 7:30 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Okay, looks like we were barking up the wrong tree.  I had a sneaking suspicion that this was a firewall issue.  Sure enough, when I disabled the Windows Firewall on the CRM server it worked as expected.  Turn it back on, and it fails again with the error above.  After enabling logging of discarded packets, I noted that any traffic on port 53021 was being rejected.  This was the only traffic that was being rejected, so I sensed that this might be it.  Sure enough, when I created a rule to allow incoming traffic on the CRM server for port 53021, the Dynamic Export to Excel began to work as expected.

    Looked like it's fixed!

    Thanks anyway for the help so far.

    Regards, Adrian
    • Marked as answer by avdlaan Friday, December 24, 2010 9:52 PM
    Friday, December 24, 2010 9:52 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    FYI, I have run the script (batch file) from KB 968872 to open the SQL ports on Server 2008.
    Wednesday, December 1, 2010 7:32 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Ok, interesting.

    From the client workstation, can a system DSN authenticate to the SQL Server and the database or do you get a 18456 error for that user in the SQL application log?

    Or is the 18456 error for the account of the application pool of the CRM server instance?

     

    Curtis J Spanburgh
    Wednesday, December 1, 2010 10:18 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Have you made sure that SQL server accepts remote connections?

    PP

    Microsoft MVP Dynamics CRM | My Twitter: http://twitter.com/pabloperalta | My blog: http://weblogs.asp.net/pabloperalta
    Thursday, December 2, 2010 4:24 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    That's a good Idea Pablo.   Often if TCP sockets is only allowed in the SQL Surface configuration I add named pipes.

    Using Network Monitor 3.4 will also show any failed access to the SQL server on the client machine as well as a SQL trace while the connection attempt is being done.

     

    Curtis J Spanburgh
    Thursday, December 2, 2010 5:07 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Ok, interesting.

    From the client workstation, can a system DSN authenticate to the SQL Server and the database or do you get a 18456 error for that user in the SQL application log?

    Or is the 18456 error for the account of the application pool of the CRM server instance?

     

    Curtis J Spanburgh


    Hi, sorry about the delay in responding, for whatever reason this forum isn't alerting me anymore to replies...

    I have attempted a system DSN connection and it authenticates fine.

    Friday, December 17, 2010 1:57 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    That's a good Idea Pablo.   Often if TCP sockets is only allowed in the SQL Surface configuration I add named pipes.

    Using Network Monitor 3.4 will also show any failed access to the SQL server on the client machine as well as a SQL trace while the connection attempt is being done.

     

    Curtis J Spanburgh


    Are you guys talking about which protocols are enabled?  If so the following is set:

    Shared Memory - Enabled
    Named Pipes - Enabled
    TCP/IP - Enabled
    VIA - Disabled

    Friday, December 17, 2010 1:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    If those are open then look to see if there is any software on the work station or configuration that is blocking the ports.

    It looks like the SQL server is ready to receive.

    If other workstations can access the SQL server via ODBC DSN, then look at what is being blocked at this client's machine.

    If this is not the case make sure you have the Service packs for Office up to date.    After eight years , I can't remember seeing CRM interaction with office fail.

     

    Curtis J Spanburgh
    Friday, December 17, 2010 2:17 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Curt,

    Just to clarify the Dynamic Export to Excel is failing on all workstations, not just one.  The ODBC DSN seems to work from those systems though manually to SQL.

    All systems are up to November patches (both Office and Windows patches).  Desktops and servers are on the same subnet with no local firewalls.

    Just curious, other than the standard SQL ports, are there any ports that are required to be open for CRM, other than port 80?

     

    Regards, Adrian
    Monday, December 20, 2010 7:54 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    That SQL error also occurs when the surface control of the SQL server is not allowing for access to the SQL instance with both TCP sockets and Named Pipes.

    enable both and restart the SQL service.

     

    Curtis J Spanburgh
    Monday, December 20, 2010 10:04 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Curtis,

    As mentioned in the post above, both the TCP and Named Pipes protocols are enabled for this instance.  Is that what you're talking about?

    Regards, Adrian
    Friday, December 24, 2010 1:08 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Yes, and if you see failed kerberoso authentications but then they default to NTLM in the security log it's an indication that something is not right with the authentication process.

    Rollup 13 may solve the issue.

    Remember that any surface changes have to followed by a restarting of the SQL Services.

     

     

    Curtis J Spanburgh
    Friday, December 24, 2010 1:22 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Also don't discount the possiblity of the workstations machine account in active directory haveing a problem.

     

    I've had a network of 250 workstations have issues with CRM because of incorrect or corrupt machine accounts.

     

    Curtis J Spanburgh
    Friday, December 24, 2010 1:24 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Okay, looks like we were barking up the wrong tree.  I had a sneaking suspicion that this was a firewall issue.  Sure enough, when I disabled the Windows Firewall on the CRM server it worked as expected.  Turn it back on, and it fails again with the error above.  After enabling logging of discarded packets, I noted that any traffic on port 53021 was being rejected.  This was the only traffic that was being rejected, so I sensed that this might be it.  Sure enough, when I created a rule to allow incoming traffic on the CRM server for port 53021, the Dynamic Export to Excel began to work as expected.

    Looked like it's fixed!

    Thanks anyway for the help so far.

    Regards, Adrian
    • Marked as answer by avdlaan Friday, December 24, 2010 9:52 PM
    Friday, December 24, 2010 9:52 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi,

    Avdlaan, it seems your sort of on the right track but the TCP port seems to be different on different deployments. On our deployment for instance the TCP port was 63831. Why it is this port is very strange. I would have guessed that it should have used 1433 which is the normal SQL port. Ports this high (above 5000) are usually used by Outgoing TCP and not incoming why this is also not according to TCP/IP best practice.

    To find which port your system uses, bring up the command prompt by entering "cmd" in the run window. Then open the excel sheet and while it trying, as soon as possible in the command prompt, enter "netstat". The result should look something like this:

    C:\Users\administrator>netstat

    Active Connections

      Proto  Local Address          Foreign Address        State
      TCP    192.168.75.15:3389     192.168.75.131:29837   ESTABLISHED
      TCP    192.168.75.15:52422    192.168.75.13:5555     ESTABLISHED
      TCP    192.168.75.15:52426    WIN-LGEKH2VQ6FI:epmap  TIME_WAIT
      TCP    192.168.75.15:52427    WIN-LGEKH2VQ6FI:49155  TIME_WAIT
      TCP    192.168.75.15:52428    192.168.75.13:63831    SYN_SENT

    C:\Users\administrator>

    I have bold marked the line that shows the excel connection for my server and underlined the TCP port that you should look for. You will probably have some other five digit number.

    I will most probably also have the "SYN_SENT" state as this is the first part of the TCP handshake, and it is waiting for the other part to return the message, which will timeout, since the port is closed.

    Hope it helps. I will blog about this on my blog later today.

    Gustaf Westerlund, CEO and Chief Architect at CRM-Konsulterna in Sweden
    Blog: http://gustafwesterlund.blogspot.com
    Swedish Dyn CRM Forum: http://www.crmforum.se

    Friday, February 10, 2012 11:35 AM