InfoPath Dev

Hi All,

I'm about to pull out the rest of my hair because of what is supposed to be a fairly simple task.
Anyway, some background:

Hardware/Software Conf.
Server 2003(Dev2.Dom2) -- Moss 2007
Server 2003(Dev3.Dom2) -- SQL Server 2005
InfoPath 2007
All Up to Date with current SP's and patches.

I can log into Dev3 with SQL Server Management Studio and Express using both Windows authentication and SQL Server Authentication from computers both loged into Dom2 as well as outside using the appropriate Authentication.

My task was to develop a InfoPath form that retreives certain form fields from the SQL Server residing on Dev3 while entering other relevant information into the form for posting either into a seperate DB or into MOSS lists.  In searching MSDN I decided to test the concept using the example provided here: http://msdn2.microsoft.com/en-us/library/bb431899.aspx.

So, using the above process I developed and activated an xml file in a DCL library.  However each time I attempt to connect to the DB thru InfoPath's Data Connection Wizard it fails wih the cryptic message "InfoPath Cannot Connect to the data source".  And the details simply id "InfoPath has encountered an error. The operation failed.".  When reviewing the SQL Logs I get (at times) "Login failed for user ''. The user is not associated with a trusted SQL Server connection"

I have tried several suggestons from this site, i.e. rebooting but to no avail...  Are there some other tips you folks could share to help me debug this problem?  Any Help would be greatly appreciated.

Thanks,
Gus

 Hi

Whenever I connect to SQL from InfoPath I select a specific SQL Logon that my InfoPath forms use that exists only in SQL so I operate in Mixed mode.  Authenticated for me and SQL Login for the form.

Works a treat every time as you just add the full credentials into the data connection, select "save the password" and job done.

Try this to see if it works, if not then we can look at other avenues

Cheers
 

If you save the user credentials into the data connection, make sure that Embedded SQL Authentication is turned on in Central Administration on your SharePoint server (Central Administration > Application Management > Configure InfoPath Forms Service). You will also need to Approve the UDCX before any users can use the connect.

Good luck

Hi Guys,

Thanks for your replies...

I have tried both suggestions and they were not successful.  I MUST be doing something incorectly that is very basic.

Let me try and explain a bit further (Note I am not logged into the server's domain at this point): 

1.  I have published one of the standard forms to a forms library (Expense Report). 

2.  Once Published it displays and accepts input in a browser ot InfoPath perfectly.

3.  I then open the form "for re-design" from the site.  And, since it is in ReadOnly form I save it to a local Directory.

4.  I change the type of user-input box for one of the standard fields on the form (ID Number) to DropDown.

5.  I then attempt to alter the data properties of the DropDown as "look up values from an external data source"

6.  I choose to search for a connection on the SharePoint site> I select a Site>Expand selection>Choose an XML Connection>Next

7.  Now is tries to search for the data connection displaying "ADO Data Connection" "Connecting to Database" and..

8.  InfoPath cannot connect to the data source.... ARGH!!

This happens each time no matter how the connection string is defined whether the credentials are in the string or not..

Is it possible that I need to be logged into the domain for this to work as expected?  Since most of my time spent at customers sites, I have not tried this logged into the Sharepoint Domain.  But, I did not think it was necessary.

Anyway, I am at the end of the proverbial rope here.  Hopefully, the experts here can give me some more advice.

Thanks,

Gus