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 AUTHORITY\ANONYMOUS LOGON'
or when setting Integrated Security to Yes or True
Validation failed. No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21)
1. Standard Security (User / password combination, Trusted Security = OFF)
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.
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 integrated security.
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:
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
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 NTLM was designed.
NTLM cannot delegate / pass impersonated credentials. This will fail as it will pass depending on the situation NT Authority\Anonymous Logon OR null credentials.
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 scenario.
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 above)
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 Directory (AD)
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 here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;810572
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.
General Workaround
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.