InfoPath Dev

Hello!

I have a problem.

I have created a form in InfoPath 2003, and it queries from two SQL tables that have a one-to-many relationship. We're using Microsoft SQL Server 2005. After a form is filled out, it can be submitted to SharePoint WSS 2007.

In trying to solve the "plain text file" password problem, we decided to try using Windows Authentication, since all our users are on the intranet. I have a login for myself set up on MS SQL Server, and when I use it to open a form in SharePoint, it works fine.

I added another user, with the same settings (I think), and he also tried to open the form from SharePoint. He got the usual password/trusted connection dialog box, and it gave him an error. When he hit "OK," it gave him the same box, and then the form came up but most of the data was missing. The available data was from my testPoints table, the "many" side of the one-to-many relationship.

I am willing to try something else to deal with the password problem, or even store the password in plain text if there's not actually any way for the users to access that file's contents. I am also trying to avoid any custom code if remotely possible.

The error is here:

InfoPath cannot open the following file: Form%20Name%20-%202011-09-13T13_40_19.xml.
InfoPath cannot open the form. To fix this problem, contact your system administrator.
Form template: http://intrashpt/formName/Form%20Name/Forms/template.xsn
The following DataObject either cannot be created or cannot be initialized: testMatrix The query cannot be run for the following DataObject: testMatrix InfoPath cannot run the specified query.
[0x80040E09][Microsoft OLE DB Provider for SQL Server] SELECT permission denied on object 'testPoints', database 'FormTestPlan', schema 'dbo'.
[0x80040E14][Microsoft OLE DB Provider for SQL Server] Deferred prepare could not be completed.

testMatrix is "one" and testPoints is "many" in the one-to-many relationship, if that's relevant. Thanks.

It looks like your user doesn't have the necessary database permissions on SQL Server.  Have you looked into that angle?

Is that different from just adding a login under Security on SQL Server? He's set to have the same permissions as I have.

Yes, it is different.  Once the login is created, you have to grant it permissions and roles before it will have any.

Under "Status" in SQL Server, under "Permission to connect to database engine," "Grant" is selected, and under "Login," "Enabled" is selected. He is also mapped to the database in question, with a dbo schema. I did go through all the options that can be seen under Properties when he was first set up...unless there's another menu somewhere with other stuff I have to check.

Just found the difference in our permission setups, and it's fairly huge. I've got the sysadmin role checked, and he doesn't. I don't want to give him a bunch of access over everything; I just want him to be able to fill out forms and read finished forms. Is there a specific place to do that?

Under "User Mapping," I added read/write permissions for him for that DB. I'm waiting on him to test it, but I bet that'll work. I can't believe it was something so simple.

Thanks for your time and speedy responses, Jimmy!

It does give me the password/trusted connection dialog box twice...is there something I can do to make that stop?