When the SQL server is physically separate from the Netwitness Endpoint server, permissions become more complicated. It is recommended that the QueuedData folder be placed on the SQL server in this situation, but this not always feasible. If the QueuedData directory is on the Netwitness Endpoint server it must be shared and you must specify the path to QueuedData in UNC form in the ConsoleServer.exe.config file.
ConsoleServer service downloads the new KernelData.csv file from liveecat.rsa.com
KernelData.csv is written to the QueuedData directory
ConsoleServer connects to the SQL server and calls a stored procedure
Within the stored procedure is code to bulk insert KernelData.csv into the database
Because the KernelData.csv file is on a non-local share, this becomes a Kerberos double-hop authentication issue.
When the SQL Server service attempts to bulk insert the KernelData.csv file it fails with the following error:
12/20/2016 7:11:49 AM
LIVE Kernel Download failed.
Cannot bulk load because the file "\\ECATServer\QueuedData\KernelData.csv" could not be opened. Operating system error code 5(Access is denied.).
SQL Error Code: 80131904
SQL Error 4861 in (2): Cannot bulk load because the file "\\ECATServer\QueuedData\KernelData.csv" could not be opened. Operating system error code 5(Access is denied.).
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
Although Windows will allow you to use impersonated credentials to access local resources, it won't allow you to use impersonated credentials to access a remote resource by default.
The SQL Server service account needs to be trusted for delegation to allow double-hop authentication.
Delegation is the act of a principal (Service) impersonating another principal (user) to gain access to a 3rd principal (QueuedData share). By enabling delegation, the SQL server is allowed to use the credentials of the ConsoleServer service account to access \\NWEServer\QueuedData\kerneldata.csv
Kerberos delegation is the act of a principal (Service) impersonating another principal (Console Server service account) to gain access to a 3rd principal (QueuedData share). By enabling delegation, the SQL server is allowed to request a Kerberos ticket-granting it access to \\ECATServer\QueuedData\kerneldata.csv on behalf of the ConsoleServer service account. The TGT and TGS session key are forwarded to SQL by the ConsoleServer service account and it uses them to authenticate the connection to the QueuedData share.
To configure the needed delegation for this scenario, change the radio button in AD as shown below: Image description
Note: The configuration of constrained delegation (the “Trust this user for delegation to specified services only” radio button) is beyond the scope of this document and has not been tested as of the writing of this document.
In some cases, there may not be a delegation “tab” present. The service account must have a Service Principal Name (SPN) attribute value set before the Delegation tab appears.