Sign up ×

As I'm sure you're already aware one of the biggest weaknesses of the data view web part is that it exposes connection strings for SQL Server connections.

Instead of using a web service to pass my data I was hoping to take advantage of integrated security (use Windows user authentication) to connect to my SQL server - is this possible?

I've tried using the option 'use custom connection string' and setting my connection string to the following:

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

This unfortunately returns the error:

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

Do I have to resort to a web service or is there a better way to do this?

share|improve this question

3 Answers 3

up vote 2 down vote accepted

First, Try to use the following connection string (Imp: note the Provider should be written twice as below).

Data Source=MyDB_Server;Database=MyDB;Integrated Security=SSPI;Initial Catalog=MyDB;Provider=SQLOLEDB;Provider=SQLOLEDB

If this does not work, You can use SSO. See here :

In MOSS (Sharepoint 2007) you can also use BDC to surface data from SQL Server. Check this out :

If you want to surface the data from SQL Server in SharePoint 2010,the best way to do it is via BCS. Check this : Also If you want to keep the credentials confidential, you can use Secure store service as well. Check this :

share|improve this answer
@Michael, Can you let us know which of the above option worked for you –  Amit Kumawat Mar 2 '12 at 7:50

In SQL, add a User Mapping to the SharePoint Content database in the properties of the login you are using for your Data View.

share|improve this answer

I am using sp2010 but it might work for 2007

you can set the asp datasource in this manner

    static void Main(string[] args)
        using (SPSite site = new SPSite("http://sps2010"))  // e.g. http://server/sites/asite

    using (SPWeb web = site.OpenWeb())
        SPFile file = web.GetFile({page contianig webpart});   // e.g. /sites/asite/default.aspx
        SPLimitedWebPartManager lwpm = file.GetLimitedWebPartManager(PersonalizationScope.Shared);
        SPLimitedWebPartCollection webParts = lwpm.WebParts;
        DataFormWebPart wp3 = (DataFormWebPart)webParts["g_5c1e1e6a_294b_4b4f_8e19_6c061542a952"];
        wp3.DataSourcesString = {DataSourceString}
       // g_5c1e1e6a_294b_4b4f_8e19_6c061542a952

share|improve this answer

Your Answer


