External Content Type SQL Authentication

Question

Having an issue with the way that SharePoint External Content Types are authenticating to a SQL server table. Currently we have to enable NT Authority\Anonymous Login in the SQL database to allow the content to be loaded. Due to having Foundation, I can not set up a Secure Store. Is there another way to allow a particular SharePoint Group to have access to reading and writing to a connected database? We want this to be as secure as possible.

Environment: 2 SharePoint Foundation 2010 SP2 w/ February CU Servers 1 External Database Server running SQL 2008R2

Answer 1

You will need to configure your environment to use Kerberos on both the SharePoint and SQL servers. Once you have done that you will then use the passthrough option when configuring your External Content Type. Instead of a SharePoint group you will then need to use an AD group.

Understanding Passthrough Authentication
Passthrough authentication is in the Impersonation and Delegation authentication model. Setting the value of the AuthenticationMode element to Passthrough causes BCS to use the credentials of the current user to access the external system. Passthrough is simple to set up by selecting Connect with User’s Identity when configuring the external system connection in SharePoint Designer, as was shown in the walk-through earlier in this chapter. However, Passthrough authentication is unlikely to be useful in many situations because of limitations inherent in Windows authentication.

-Business Connectivity Services security overview (SharePoint Server 2010)

Configuring Kerberos is a pretty complex process so I can only offer you some links as well as point you in the direction of some tools to help assist you.

• Identity delegation for Business Connectivity Services (SharePoint Server 2010)
• Download and install the Server 2003 Resource Kit tools and grab KerbTray
• The Kerberos Configuration Manager for SQL Server
• Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products and Technologies (white paper in MS word format)

You may also find this SQL query helpful to determine which accounts are legged in via NTLM and which via Kerberos.

SELECT
    c.session_id
   ,s.login_name
   ,c.auth_scheme
   ,c.net_transport
   ,s.host_name
   ,s.login_time

FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s on s.session_id = c.session_id