How BDLC can be used with Integrated Security / Trusted Connection in SharePoint 2007 / 2010?
Business Data List Connector for SharePoint solves many issues that
still exist with SharePoint out-of-the-box external data integration. It makes
it as easy as possible to connect native SharePoint lists to almost any external
data source, codeless and bi-directional. All native SharePoint list features
are available for the connected list.
Note: The following is valid
for SharePoint 2007 or newer systems without Claim Authentication. See specific
FAQs about changes for SharePoint 2013/16.
Generally Integrated Security / Trusted Connection is supported by the BDLC.
This FAQ discusses some typical issues with NTLM that causes
the follwing errors
when setting Integrated Security to SSPi or Trusted_Connection=Yes
Validation failed. Login failed for user 'NT
or when setting Integrated Security to Yes or True
Validation failed. No error message available, result code:
1. Standard Security (User / password combination, Trusted Security =
Whenever possible, start with the "classic" username / password
combination in the connection string, to evaluate the BDLC. This is
very easy to configure and you have a very clear user context. You can use the
sa / dba account for test, but don't forget to replace it later on. Possibly you
can setup a defined user in the external data source system for that connection.
Please give that user sufficient (read) access rights, to view your data.
You can restrict access to the BDLC list configuration dialog with
SharePoint security to hide the connection string. The
connection string is stored encrypted in the BDLC Configuration
List for security reason. Additionally you can use
secure store to completely hide the connection string.
2. Integrated Security or Trusted Connection
If your external data source don't support a user / password combination
(e.g. for security reasons in your specific configuration) then you have to use
First you have to specify this in the connection string using the Integrated
Security or Trusted Connection attribute. When false, User ID and Password must
be specified in the connection. When true, the current Windows account
credentials are used for authentication. Recognized values are true, false, yes,
no, and sspi (strongly recommended), which is equivalent to true. If User ID and
Password are specified and Integrated Security is set to true, the User ID and
Password will be ignored and Integrated Security will be used.
For SQL Server the connection string looks like:
If integrated security is used, you have to think carefully about the
"current user". If some user starts a BDLC action (e.g.
update), then his or her credentials are used. If the timer job starts the
update in background, it's credentials are used. In both cases, the current
credentials must be valid users of your external data source system
with appropriate read access rights.
3. The NTLM double hop issue and possible solutions
If you are using NTLM for user login and your data source is hosted on an
external server system, then integrated security would not
work, because of the well known NTLM double hop issue.
It occurs because of the way Microsoft NTLM was designed (not
Layer2 product related).
NTLM cannot delegate / pass impersonated credentials. This will fail as it
will pass depending on the situation NT Authority\Anonymous Logon OR
So, accessing a SQL Server instance running on another machine (other than
the web server) will result in a logon failure error. It would be the
second hop: the web server would have to pass the user's credentials to
the SQL Server machine. For security reasons this is not allowed. Imagine that
you access a site through your browser and the site collects your credentials
without your knowledge (Integrated security) and then starts doing whatever the
developer desires with your identification. Clearly, this is not a good
Ok, and now that we identified the problem, how do we workaround it?
Here you'll find possible solutions to this problem. Note
that you should carefully analyze each of these options and choose whichever
fits your requirements.
- A - Basic Authentication (consider HTTPS in this case)
- B - Kerberos and Trust computer for
delegation privilege in Active Directory (AD)
- C - Specify explicit credentials (Standard Security, see
A - Basic Authentication
When using basic authentication, users are asked to insert their credentials
when accessing the site. In this case, they are authenticating against the web
server and the machine can now use the credentials provided to access SQL
Server. You can think of this as the user giving permission to the machine to
use his/her identification. This doesn't change anything in the code, it's only
an IIS setting. Careful though, because the credentials are sent in clear text
between the client machine and the web browser, so you should think of getting a
SSL certificate and using https to secure communications. In most cases it is
not an option for SharePoint to use basic authentication.
B - Kerberos and Trust computer for delegation privilege in Active
Kerberos is a little bit more complex to handle, but it would allow you to
hop the user's identification between servers. You'll also have to give the web
server the "Trust machine for delegation" privilege in AD. You'll need to ask a
domain admin to do this. Normally they don't really like to accept this type of
request. It's not a question of bad attitude but concerns for security. If your
domain admin refuses your request, feel happy that he knows what he's doing ;)
Take a closer look
C - Standard Security
This is the solution more commonly used. Basically, you do not connect to SQL
Server using a trusted connection, you specify a username and a password in the
connection string (see above).
The username has nothing to do with a windows
user, it's for the data source system only.
Generally you can specify these
values in the impersonation settings too and still use a trusted connection. But
this mostly doesn't fit in a SharePoint scenario.
Please keep in mind, that the NTLM double hop issue occurs only if
users are starting the BDLC actions manually and the data source in on an
external system. As a workaround you possibly can configure the BDLC in
a browser on the web server directly. That would work, because
there are no double hops in this case.
The timer job, if configured once, will work in every case, because
there are never double hops for this action.
Summary: No problem to use and configure integrated security for
SQL Server to make use of it in the Layer2 Business Data List Connector. Please
contact [email protected] for
any questions and support.