alzikan

I could not connect to SQL 2005 from SharePoint Designer 2007 using data source , any idea how , or why.

Even I could not select SQL Native Client.



Re: SharePoint - Design and Customization can't connect to SQL 2005 using data source in SharePoint Designer 2007

rick postmus

alzikan wrote:

I could not connect to SQL 2005 from SharePoint Designer 2007 using data source , any idea how , or why.

Even I could not select SQL Native Client.


Can you give more details about your problem like what you did and what you did to solve it. Also can you give more information on wat errors you get etc.. I think more people are able to help when you give more information.




Re: SharePoint - Design and Customization can't connect to SQL 2005 using data source in SharePoint Designer 2007

_shyman

Actually I have a similar if not the same problem.

I'll try to put the situation into detail

First I create a new ASPX page using Sharepoint Designer. It creates a new page with a blank form

I click on "Insert Data View" to connect this form to a data source.

Then I create my data source. I want a database connection datasource. When I'm prompted to make a connection I select the connection String option (by the way, I've seen through the internet that a Windows Authentication option is available, but I don't see it anywhere, if there is a way to enable it would be really appreciated if someone could say how).

Then I introduce the following connection String

Data Source=SOBREMESA026;Database=PruebaCajaChica2;Integrated Security=SSPI;Initial Catalog=PruebaCajaChica2;Provider=SQLOLEDB

Then I'm prompted to choose the database, the table I want to make the query from etc. So then, everything is fine (in theory)

But, when I click on "Show Data" on the new created DataSource, it cannot display the fields from the table and an unknown error is shown with a message similar to this "The server has returned a not specified error trying to retrieve the data from the data source. Check the query format and try again. If the problem persists, contact your server administrator".

So far, I haven't found any tidbits of why this could happen. I am using a SQL Server 2005 database engine, Sharepoint Designer 2007 and Sharepoint Services v3.

This problem is being quite frustrating indeed. If anyone could share some valuable information it would be really appreciated.

Thanks in advance





Re: SharePoint - Design and Customization can't connect to SQL 2005 using data source in SharePoint Designer 2007

MrtnZ

Hello!

You have to write two times the provider, cause the designer remove one of the providers from the connection string.

You can use the following connection string.

Data Source=SOBREMESA026;Database=PruebaCajaChica2;Integrated Security=SSPI;Initial Catalog=PruebaCajaChica2;

Provider=SQLOLEDB;Provider=SQLOLEDB

Good luck!





Re: SharePoint - Design and Customization can't connect to SQL 2005 using data source in SharePoint Designer 2007

Anonymous

I had the same issue but it worked when I entered the connectionString you suggested. Thanks a lot! I've been struggling with this problem for a long~~ time and I'm so excited that it's working now.

Odd that the Sharepoint designer removes one of the provider information from string. Anyhow, thanks for the tip!





Re: SharePoint - Design and Customization can't connect to SQL 2005 using data source in SharePoint Designer 2007

Anonymous

I was able to create a db connection to a table in sql 2005. But when I clicked on 'show data' menu, I got a 'This control does not allow connection strings with the following keywords; Integrated security, trusted_connection' message under data source details. I'm trying to use data view web part. I'm not sure where the problem ocurred. Anyone has any idea





Re: SharePoint - Design and Customization can't connect to SQL 2005 using data source in SharePoint Designer 2007

ajhopen

I get the exact same symptoms as above using:

Data Source=<mySQL2005SERVER>;Database=<myDB>;Integrated Security=SSPI;Initial Catalog=<myDB>;Provider=SQLOLEDB;Provider=SQLOLEDB

I was excited to get this far but when I tried to display the table I got an error in MS SharePoint Designer:

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

'Integrated Security',

'Trusted_Connection'

Are these connections unsupported by Sharepoint Services 3.0 If we have to use SQL authentication that would seem to go against MS best practices.

Any tip would be greatly appreciated!





Re: SharePoint - Design and Customization can't connect to SQL 2005 using data source in SharePoint Designer 2007

Robet

Hi, now I am facing exactly the same problem. I mean in WSS 3.0.

Have you got it solved . If yes, please let me know how. Thanks so much.





Re: SharePoint - Design and Customization can't connect to SQL 2005 using data source in SharePoint Designer 2007

Daren Coles

I get same issue as above.

For people like me who came to this hoping for an answer here are some things I've learnt on the way:

- don't even bother with using the option to select a user name and provider from the drop down lists - I could never get this to work.

- Use a connection string along the lines of the ones shown above.

- don't assume cos you can progress to the page where you select the DB tables or sprocs that it is working. Click on the option to 'show data' to be sure

- you must specify Trusted_Connection=yes in the connectrion string, otherwise you will get the "this user is not associated with a trusted connection" logon error on the database server.

- and this is where I get stuck, just like everyone else above I get the message "this control does not allow connection strings with the following keywords 'Integrated Security', 'Trusted_Connection'

has anybody succeeded in getting dataview to connect to SQL Server in a farm (separate web and db servers) and could you tell us how





Re: SharePoint - Design and Customization can't connect to SQL 2005 using data source in SharePoint Designer 2007

Robet

Hi, I think I found the solution, in fact I found this solution as a result of googling too much:

The problem is becuase of a line in we.config in WSS root folder which is:

<tagMapping>

<add tagType="System.Web.UI.WebControls.SqlDataSource, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" mappedTagType="Microsoft.SharePoint.WebControls.SPSqlDataSource, Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />

</tagMapping>

The solution is as below:

Exception Details: Microsoft.SharePoint.WebPartPages.DataSourceControlDisabledException: This control does not allow connection strings with the following keywords: ¡®Integrated Security¡¯, ¡®Trusted_Connection¡¯.

I was able to get rid of this by putting my VS 2005 built ASPX pages in a separate virtual directory (running as an application), and editing the web.config for my new application. Add the following section to the <system.web><pages> section:

<tagMapping>

<remove tagType="System.Web.UI.WebControls.SqlDataSource, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

</tagMapping>

I'm not sure if this would work for the smartpart, but since I had to use a separate web application anyway, it works for me. Essentially, it tells SharePoint to get out of the way when accessing data sources.

Hope this helps.





Re: SharePoint - Design and Customization can't connect to SQL 2005 using data source in SharePoint Designer 2007

Daren Coles

Good that you've found a solution for a custom app Robet. My situation is I am trying to drop a dataview web part onto a SharePoint 2007 web part page and connect it to a SQL Server database that lives on the same DB server as the SPS07 databases. I have a separate web server and db server. Has anyone had any success doing this using Windows Auth to connect to DB and could they please tell me how



Re: SharePoint - Design and Customization can't connect to SQL 2005 using data source in SharePoint Designer 2007

Robet

Daren, in fact, I had the same problem as what you are facing now, for which I didn't get any solution, but I just gave up as I had many different problems to look into. Has anyone solved the above problem. I will be glad to have the solution too!. Thanks



Re: SharePoint - Design and Customization can't connect to SQL 2005 using data source in SharePoint Designer 2007

Daren Coles

I've been told on another thread that if you've setup your farm for Windows Auth then the only way to get DataView web part to connect to SQl Server is using Single Sign On. This is what the Designer documentation also recommends. Thing is I couldn't get Single Sign on to work either, I could setup SSO on web server no problems but when try and setup a data source using this option, I get that non-specific error returned from server after entering in my application details. I've been trying to find some more detailed SharePoint Designer info on using SSO in a data source connection to sql server...





Re: SharePoint - Design and Customization can't connect to SQL 2005 using data source in SharePoint Designer 2007

Doug Lim

Hey guys,

I experienced the same problem and tried all the suggested solutions on this forum. However, if you take a step back and think about it a bit, the way to fix it is to create a new sql server user on your database server. And then instead of using integrated authentication in your data source, you use your corresponding sql server username and password.

This means that when you load up data into your gridview or other data view, it won't be tied to your windows credentials. This makes sense as not everyone viewing your sharepoint site will have the correct credentials to access the database server.

HTH
Doug




Re: SharePoint - Design and Customization can't connect to SQL 2005 using data source in SharePoint Designer 2007

Daren Coles

Hi Doug

Could you list the steps you performed to set this up I have created a Login with access to my database and set this to be the user when configuring the database connection in SharePoint Designer. Whether I enter the user details using the Authentication settings in the wizard or use a custom connection string, I get an error. In the event log on database server I get:

Login failed for user 'DataViewWebParts'. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.0.14]

I'm not good with SQL Authentication so any help you could provide would be much appreciated.