I am struggling a bit to get this to work. I am using SQL 2005 and can't seem to get data loaded. Everything seems to work fine until I get to add a new item. Currently when I click the picker, the screen loads but has an error that says CannotConnectException. I am assuming this is because of problems with my xml file. I can't seem to find an xml file to work with my db. I have installed adventureworks as well as adventureworksdw and nothing seems to work. I have attempted to create an adf, but since this is new to me its not working either and I would like to actually see something work before I sink more time in to it.
Can anyone offer some assistance?
jledhead,
Is your database on the same machine as the Web front end?
If it is on a different machine you will have to use RevertToSelf or SSO to authenticate (unless you run Kerberos) You can't use PassThrough.
This setting is in the ADF file
<Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>
The easies way is to use RevertToSelf, then you will connect to the database as the application pool account. But the limitation is that all users will connect using this account.
<Property Name="AuthenticationMode" Type="System.String">RevertToSelf</Property>
/Jonas
it is on a different server from my Web front. Reverttoself connects as the application pool account, so I am guessing that means I would need add that user in my sql server?
Correct
yep, that did it. Now for some playing :)
Glad to hear that you are up and running.
I have a similar kind of problem. I have the SQL 2000 on a different server. I can load the data into the column of the list.
Then when I want to enter a new row in the list, it comes with an error of the Bamboo column:
Failed to get value of the "City" column from the "Bamboo MashPoint" field type control. See details in log. Exception message: Object reference not set to an instance of an object..
Has this got to do with a wrong ADF file or is the problem of the apllication pool user?The application pool is a network service on my WSS.
Mindbenda,
Are you using RevertToSelf? If you do you have to grant access to the remote computers netwok service account in your database.
Yes, RevertToSelf.
Where do I grant this access? In SQL Server? What rights? Like owner or something?
Yes in the database you are trying to access. Reader is good enough unless you do updates.
Ok, I will try this. I think it is a strange error though, since I can see the data in the column but not in the list. I will keep you posted if it works.
Hmmm... I guess I didn't see it right. I can add the ADF to a column and I can choose the field from the table.When I try to add a new row to the list and I try to use the picker, I get the error message.
So to be exact, what user do I add? I can only find the NT Authority\Network Service and not the Remote computers Network Service. Is this the same one or not? If not where do I find the real one? Next to this problem I am still not quite sure if my ADF is correct. I have an example of a SQL2000 version of the Adventureworkds DB and changed it to Northwind. I changed the table names and the fields that are different for this DB, but I am not sure if I done all things needed.
Is there anybody who has a sample ADF of the Northwind (2000) because I have no testserver to install the Adventureworks 2000 version on. I only have a 2005 testserver. I have to test it on the SQL2000 server which has the Northwind on it.
I believe as explained above, revert to self means the connection from SharePoint to SQL will revert back to the user the SharePoint app pool is running. so you need to go into IIS on the SharePoint server and change the app pool account to one that has access to the SQL server. This probably means you need to be on a domain since you can not share local auth between 2 windows servers. when you select the user it should have a place to select location, you probably need to change that to domain instead of the local server.
Yes do what jledhead suggests, change the application pool account to a domain account. MAKE sure to do this using Central Administration.
Ok, just to stay on the safe side with this one. I add a user to the domain (active directory) that has at least rights for IIS or/and WSS as a user and this user must have at least read rights to the database(s) in the SQL server?
Remains my other question, is there somebody with ADF for Northwind?