Dynamic Worksheet producing SQL Error using Admin account

All replies

• 

  

  0

  Sign in to vote

  Anyone have any ideas on this?

  Friday, March 19, 2010 3:48 PM
• 

  

  0

  Sign in to vote

  It looks like something is not correct in your ODBC settings as the connection is not able to find the SQL server.  Can you provide the steps you are using to 'create the dynamic worksheeet' and we should be able to provide some direction.

  ---

  Best Regards | Twitter: edwardsdna

  Sunday, March 21, 2010 4:54 PM
• 

  

  0

  Sign in to vote

  I suppose I am doing it normally.

  Open up a list > Select Excel > Select Dynamic Worksheet (Export Data to Excel) > Excel button > Open > Sheet Opens > Select "Options" at top of Excel > Enable

  Then I end up with the error message. I installed Excel on the server as well and got the same result?

   

   

  ---------------------------
  Microsoft SQL Server Login
  ---------------------------
  Connection failed:
  SQLState: '01000'
  SQL Server Error: 11001
  [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
  Connection failed:
  SQLState: '08001'
  SQL Server Error: 6
  [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL server not found.

  ---------------------------
  OK  
  ---------------------------

  Monday, March 29, 2010 12:35 PM
• 

  

  0

  Sign in to vote

  Can you try and save the Excel file first, as an xls or xlsx file and then try the enable button.  If that doesn't work, please follow the steps below and post your connection string

  • Select data from the top navigation menu in your Excel file
  • Select Connections from the top Ribbon
  • Select the Properties button from the right
  • select the Definition tab

  The Connection String should look something like this:

  DRIVER=SQL Server;SERVER=CRM_SQL_Server_Name;UID=User_Login;Trusted_Connection=Yes;APP=Microsoft Office 2003;WSID=User_Workstation;DATABASE=CRMOrg_DB_Name;Network=..........

  You can also have a look at this KB or this one to see if either help.  They look like they might.

   

  ---

  Best Regards | Twitter: edwardsdna

  Monday, March 29, 2010 4:42 PM
• 

  

  0

  Sign in to vote

  Donna, here is the connection string, btw this is SQL Server 2008 on Windows Server 2008:

  DRIVER=SQL Server;APP=Microsoft Office 2003;Network=DBMSSOCN;Trusted_Connection=Yes;SERVER=SDCZCRM01;DATABASE=Stakeholder_Test_MSCRM

  I am checking the KB Articles, however they seem to relate to SQL Server 2000, I'll see what I come up with.

  Tuesday, March 30, 2010 5:29 PM
• 

  

  0

  Sign in to vote

  Did you try saving the file to your hard drive first before trying to run it?  If not, please try that first.

  if saving the file does not work, try supplying your CRM user name to the connection in this format ;UID=CRM_User_Name;

  Look at my connection string post and you will see how the UID is nested in the connection string.  I also don't see the WSID (Workstation ID in your connection string).

  Something is preventing the connection to the SQL Server.  You could try setting up a regular ODBC connection from the workstation experiencing the issue and see if you are able to get a successful test connection.  You could also try installing Excel on the CRM or SQL server and see if you are able to create the connection using a dynamic worksheet export. 

  You can also have a look at the event viewer to see if there is any additional error information.

  ---

  Best Regards | Twitter: edwardsdna

  Tuesday, March 30, 2010 8:40 PM
• 

  

  0

  Sign in to vote

  Kinda old but I am now just able to get back to this error.

  ---Saving the file does not work. I was prompted with the same error as in my first post on this string. I added a UID as below, no luck.

  I went ahead and edited the server connection.  DRIVER=SQL Server;APP=Microsoft Office 2003;Network=DBMSSOCN;Trusted_Connection=Yes;UID=FIRSNAME.LASTNAME;SERVER=SDCZFAPWPCRM01;DATABASE=StakeholderEngagement_MSCRM

  ---I tried to create a new ODBC Connection, however I am in a nework with multiple domains and trusts set between the two domains in Active Directory. My client machine isn't in the same domain as the CRM Server. I wasn't successful, the CRM Server wasn't available in the list during the wizard and when I typed it in the connection stil failed.

  ---Static Excel exports work just fine

  --- There is nothing in the Event Viewer

   

  Now I did find this Q&A for CRM 3.0. I do not get this specific error, but does this still apply? Terrible if so?

  Q. When I export to a Microsoft Office Excel dynamic worksheet or PivotTable, why do I receive a "Connection failed. Reason: not associated with a trusted SQL connection" error? 
  A. This error occurs if you are accessing Microsoft Dynamics CRM from a different domain than the domain that Microsoft Dynamics CRM is installed on.

  To export a list of records to a dynamic Microsoft Office Excel worksheet (a file that refreshes data from the Microsoft Dynamics CRM database each time it is opened) or PivotTable, your computer must be on the same domain as the domain of the Microsoft Dynamics CRM server. If you are on a different domain, you can export a worksheet static (a file that does not change when data changes in Microsoft Dynamics CRM).
   

   

  Thursday, April 29, 2010 6:15 PM
• 

  

  0

  Sign in to vote

  SQL Named Pipes active? SQL Browser Service started?

  And check this cause it´s a Windows 2008 System

   

  ---

  Gruß Carsten Groth http://carstengroth.spaces.live.com

  Thursday, April 29, 2010 6:23 PM
• 

  

  0

  Sign in to vote

  Not being connected to the Domain is new information.  You need to be connected to the Domain in order to do what you are attempting.  The other option is to configure CRM for IFD.  Here is a post on protecting data over the web which touches on the ability to leverage this feature with IFD. 

  ---

  Regards, Donna

          Windows Live Blog

  • Proposed as answer by Donna EdwardsMVP Friday, April 30, 2010 4:13 PM
  • Marked as answer by Jim Glass Jr Friday, April 30, 2010 4:29 PM

  Friday, April 30, 2010 4:13 PM
• 

  

  0

  Sign in to vote

  None of the users are actually in the same domain as CRM, this was intential due to multiple domians. CRM's domain has trusts set between the various domains. Static Worksheets do work, however dynamic worksheets do not.

  I suppose the only way to be able to do this is to use IFD as suggested, however I would not need external access for CRM. Outside the concerns addressed in the posting you sent, what other concerns are noteworthy when using IFD in terms of user differences?

  Another question, wont IFD require each user to log into CRM every time they want to use it internally?

  Friday, April 30, 2010 6:17 PM
• 

  

  1

  Sign in to vote

  Hi,

  Unfortunately, I don't have experience with your particular environment where there are multiple trusted domains and no users are in the CRM domain so I am not going to waste your time with my best "guesses".

  My best suggestion is for you to start a new post and give this scenario,

  "Why are Dynamic Excel worksheets not working with trusted domains".  Users are not in the CRM Domains but are in Trusted Domains.

  Post the error that you have in this thread and hopefully you'll get a good response from someone who has experience with your type of environment.

  ---

  Regards, Donna

          Windows Live Blog

  • Proposed as answer by Donna EdwardsMVP Friday, April 30, 2010 6:38 PM

  Friday, April 30, 2010 6:38 PM
• 

  

  0

  Sign in to vote

  Thanks~

  Monday, May 3, 2010 12:18 PM