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?