Hi

Has anyone succeeded in creating a datasource in SPS Designer that connects to a SQl Server 2005 database on a separate server

I have tried to create a datasource using the wizard and set the connection string as follows:

Data Source=dbserver;Initial Catalog=customdb;Integrated Security=SSPI;Provider=SQLOLEDB

and this seems to successfully access the database. I can choose the table I am looking to display in a dataview web part but when I try and change the fields I want to select I get this error popup:

The server for the datasource returned a non-specific error...

so I try using default set of fields and click OK in the wizard, then select 'Show Data' after right clicking on the new connection in the Data Source Library in task pane and get this error appear in the taskpane:

This control does not allow connection strings with the following keywords:

'Integrated Security',

'Trusted_Connection'

Unfortunately using Integrated Security=SSPI; was the only way of getting as far as I did through the wizard. I am using Windows Auth and have a small MOSS server farm. I haven't found anyone who has been able to do this ... can anyone help

Thank you, I tried using Single Sign and this fails at the point where I enter in the details for the application definition. I am able to select the application from the drop down in the create datasource wizard in Designer but when I enter in my fields 'User ID and 'Password'. I get the error:

The server for the datasource returned a non-specific error...

could you possibly list the steps you followed in setting up single sign-on for use in a dataview web part

Many thanks

we're not using single sign on, but I wish we were. According to the SPS 2003 Admin Help:

Enabling Single Sign-On

To enable the single sign-on service on the server farm, you must enable it on each front-end Web server, on the job server, and on any server running the single sign-on service. After you enable the service, you can configure single sign-on and application definitions.

Note The Microsoft Single Sign-on service (SSOSrv) must run as a member of the local Administrators group or as a member of the STS_WPG and SPS_WPG local groups. The account under which the service is running must also be a member of the Single Sign-On Administrator group or account, have the public right on the configuration database for Microsoft Office SharePoint Portal Server 2003, and be a member of the Server Administrators server role on the Microsoft SQL Server instance where the single sign-on database is located. For more information about configuring single sign-on, see Specifying Settings for Single Sign-On and Application Definitions.

Enable single sign-on

Do the following on each front-end Web server, job server, and any server running the single sign-on service:

1. On the taskbar, click Start, point to Administrative Tools, and then click Services.
2. On the Services management console, double-click Microsoft Single Sign-on Service.
3. Click the Logon tab.
4. Under Log on as, click This account.
5. In the This account box, type an account name that is either a member of the local Administrators group or a member of the STS_WPG and SPS_WPG local groups. This user must also be a member of the Single Sign-On Administrator account.
6. In the Password and Confirm password boxes, type the password.
7. Click Apply.
8. Click the General tab.
9. In the Startup type list, click Automatic.
10. In the Service status section, if the service status does not display Started, click Start.
11. Click OK.

After many woes I eventually got SSO to work with both SharePoint designer and the BDC.

Forever, i had two different MOSS servers I was trying to configure with SSO and both exhibited various problems, one of which was your error. Eventually what fixed it on one server was to re-create the encryption key and then re-start the service. Then suddenly everything started working.

Since I had essentially configured everything in parallel, I figured i'd messed something up along the way and it had been cached in some way, but when doing this fix on the second box, it didn't help. It was still was giving me an authentication error in my logs every time i tried to connect.

I found that in order to get it to work, I had to delete everything SSO and start over, configuring SSO in this exact order:

1. Make sure the SSO is running under an account that meets the requirements outlined in the Central Administration Help. Start the service.
2. In the Central Admin SSO area, Configure the settings under Manage Server Settings. Make sure these accounts match the requirements in Central Administration help too.
3. In the Application definintion section, create an application definition using Windows Authentication. I used a group account. You should be able to leave the rest as is. You'll choose which group account to use in the next step.
4. Go to the Manage account information for enterprise application definition settings. Select your new definition and add the Group account you want users to authenticate to. This is the client-end account. The user trying to connect to the db must be in this group.
5. Click Set. In the Logon Information, enter the Username and Password that SSO will use to connect to the DB. I don't know if this gives you any feedback if you enter the information wrong. There is little feedback when you get it right, i know that. Enter the info carefully and click OK. That should be all you have to do.
6. In SharePoint Designer, connect to a SQL datasource using SSO. In the SSO box, enter the SSO Application ID, "Username", and "Password".

I think that if you get something wrong along the way, it sort of messes everything up, and the whole thing doesn't work unless you start over. That is how it seems to work for me at least. This was one of the biggest PITAs so far for me and my relationship with MOSS.

unfortunately, now that i successfully got that working, i can retrieve data, but cant' actually write back to the DB using SharePoint designer and SSO. I have a post here about that.

Good luck to you, hope this helps.