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.
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.
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.
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
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!
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!
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
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!
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.
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
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.
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...
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.