SharePoint 2010 BCS ignores username/password in connection string

Question

Here's my scenario.

I've got SharePoint 2010 running on one server, with an SQL Server running on another. Both machines are on the same domain, but the logins involved are local to each machine. SharePoint is running as NT Authority\Network Service, if that makes any difference.

I've created a BCS that pulls data from the remove SQL Server. The connection string appears as follows: Data Source={0}; Initial Catalog={1};User Id={2}; Password={3}; Integrated Security=SSPI. I've confirmed that the connection string is well formed, once all the {x}'s are replaced.

This works just fine, provided you access the SharePoint site from a browser running on the same machine (the machine hosting SharePoint, that is to say). Once you connect to it from a browser on a different machine it fails.

Slapping a profiler on SQL Server reveals that when SharePoint is accessed 'locally', the user making queries is the logged-in account. When its running remotely, the user is 'anonymous'. Due to the account permissions, the former succeeds while the latter fails. In no case is the user actually the one provided in the connection string.

This answer indicates that the problem is probably related to connection pooling, but hasn't helped me resolve the issue.

So, in a nutshell, how can I get this BCS to actually honor the connection string username/password pair? Or, failing that, how can I achieve the equivalent end? The server setup (SharePoint on one, SQL Server on another) isn't negotiable; unfortunately.

Answer 1

You can have Integrated Security XOR you can have SQL authentication. But you can't mix them. Once you specified Integrated Security=SSPI, your user name and password will be ignored, as integrated security will be used instead. If you want to specify a SQL authentication user and password, remove the 'integrated security' part.

As with any integrated authentication connection, remote servers fall into the constrained delegation restriction and they authenticate as anonymous. To enable NT impersonated credentials to flow to the next hop (the SQL Server), constrained delegation must be enabled, see:

• How To: Use Protocol Transition and Constrained Delegation in ASP.NET 2.0
• How To: Use Impersonation and Delegation in ASP.NET 2.0
• How to Implement Kerberos Constrained Delegation with SQL Server 2008