Dataview Web part and SQL - Way to use trusted connection?

Question

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?

Answer 1

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 : http://blogs.msdn.com/b/sharepointdesigner/archive/2007/08/27/an-introduction-to-single-sign-on-sso-with-data-views.aspx

In MOSS (Sharepoint 2007) you can also use BDC to surface data from SQL Server. Check this out : http://msdn.microsoft.com/en-us/library/bb736296(v=office.12).aspx

If you want to surface the data from SQL Server in SharePoint 2010,the best way to do it is via BCS. Check this : http://msdn.microsoft.com/en-us/magazine/ee819133.aspx. Also If you want to keep the credentials confidential, you can use Secure store service as well. Check this : http://blogs.msdn.com/b/sridhara/archive/2010/01/27/setting-up-bcs-with-secure-store-application-impersonation.aspx

Answer 2

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

Answer 3

I am using sp2010 but it might work for 2007

http://msdn.microsoft.com/en-us/library/ff647396.aspx

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
        lwpm.SaveChanges(wp3);
    }

}