JunHOV

Hello,

I am trying to connect to the SQL server 2005 (enterprise edition) from the SharePoint Designer. The SQL server is installed on the same machine with MOSS and SharePoint Designer. Following the instruction provided in book "MicroSoft SharePoint Building Office 2007 Solucations in C# 2005", page 161, I use connection string as

Integrated Security=SSPI;Persist Security Info=False;Data Source=[server name];

in the Configure Database Connection window (user custom connection string). I get error message as below:

Server Error: An error occurred while retrieving the list of Databases from [server name]: The server for the data source returned a non-specific error when trying to execute youe query. Check the format and content of your query and try again. If the problem persists, contact the server administrator.

I have tried to add the admin user (administrator account) and network service account to the SQL server login and grant the db owner to them on some databases, but the error stays the same. If I provide the server information and authentication information (Administrator/password) in the Configure Database Connection window, then I will get the authentication error like:

.... Your login information may be incorrectly entered, you may not have permission ot access this data source, or the requested authentication method may not be supported.....

Please help with this issue.

Thanks




Re: SharePoint - Design and Customization error with connecting to SQL server 2005 from Sharepoint Designer

Kev-ster

I notice that this thread has gotten no reply. I am now facing the same error while trying to connect to database on the same server from SharePoint Designer. At first glance, I think SharePoint Designer is terrible but I've been trying to stomach it and hopefully find some usefulness and then this happened.

Has anyone found a solution for this






Re: SharePoint - Design and Customization error with connecting to SQL server 2005 from Sharepoint Designer

Kev-ster

To follow up, I was able to get this query to work by editing the automatically generated SQL to a custom one.

I'm not sure at all if this is the issue as I do not have time to test it, however my assumption is that it had something to do with SharePoint Designer not writing the fully qualified name of the table ( [SalesOrderHeader] instead of [AdventureWorks].[Sales].[SalesOrderHeader] ) .

It would have been 100 times more helpful if instead of a generic exception message in the Designer like:

The server returned a non-specific error when trying to get the data from the data source. Check the format and content....

That they would have provided something more meaningful.






Re: SharePoint - Design and Customization error with connecting to SQL server 2005 from Sharepoint Designer

Mike Walsh MVP

Thanks for your follow-up with your solution.

I'm moving this thread from the Programming forum to the forum (Design and Customization) for SPD 2007 questions.

That might well be the reason why there was no answer to it from anyone else and anyway the solution you have now posted will be of more interest to SPD 2007 users than to the Programming guys.

Mike Walsh