Access denied error is logged in RSA NetWitness Endpoint when SQL attempts to insert KernelData.csv file

[#1]

Article Number

000001590

Applies To

RSA Product Set: NetWitness Endpoint, ECAT
RSA Product/Service Type: Database
RSA Version/Condition: 4.2.x
Platform: Windows
Platform (Other): SQL 2014 Standard/Enterprise, SQL 2012 Standard/Enterprise, SQL 2008 Standard/Enterprise

Issue

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.

<add key="QueuedDataPath" value="\\NWEServer\QueuedData"/>

The process to update the kernel data is:

• 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
[9] System.ComponentModel.WarningException:
LIVE Kernel Download failed.

 
Inner-Exception:

[9] System.ComponentModel.WarningException:
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)

 

Cause

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.

Resolution

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: