We have begun an initiative to minimize service account privileges in our development environment and have started with the Laserfiche service. We are referencing the whitepaper, Building a Secure Laserfiche Ecosystem for Laserfiche, specifically the bottom of page 12:
After a repository has been created, the Laserfiche Server service account requires the following roles:
db_ddladmin to create tables for temporary data such as search results.
db_datawriter to write to the database.
db_datareader to read from the database.
We made those changes and were faced immediately with errors:
Details:
Error Code: 9008
Error Message: Error executing SQL command.
General database error. [9008]
------------ Technical Details: ------------
LFSO:
Call Stack: (Exception)
ProcessResponseHeaders
InternalDoLogin
LFSession::Login
CLFConnection::Create
Additional Details:
HRESULT: 0xc0042330 (ProcessResponseHeaders, lfsession.cpp:4942)
(LFSO/10.4.1.81)
LF.exe (10.4.1.193):
Call Stack: (Exception)
CLoginDialog::AttemptLogin
CLoginDialog::LoginToServer
CFolderTreeView::EnsureValidDatabase
Call Stack: (Current)
CLoginDialog::LoginToServer
CFolderTreeView::EnsureValidDatabase
Additional Details:
Exception: 0x80042330 [9008] (Error executing SQL command.) (CLoginDialog::AttemptLogin at logindialog.cpp:796)
Call History:
CLFClientAutomation::ExecuteAutomationCommand (GetWindows)
CLFClientAutomation::ExecuteAutomationCommand (GetWindowInfo)
GetRepositoryProperties
CLFClientAutomation::ExecuteAutomationCommand (GetInstanceInfo)
CLFClientAutomation::ExecuteAutomationCommand (GetWindows)
CLFClientAutomation::ExecuteAutomationCommand (GetWindowInfo)
GetRepositoryProperties
CLFClientAutomation::ExecuteAutomationCommand (GetInstanceInfo)
From the event viewer:
An error occurred when executing an SQL query, retrieving the results of a query, or otherwise communicating with the database server. Session ID=0; Dialog ID=0; Repository="ESD112-DEV"; Function=SysLogin; Message="[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The EXECUTE permission was denied on the object 'trustee_get_all_groups', database 'LF_Laserfiche', schema 'dbo'."; Statement="{call trustee_get_all_groups(:trustee_id<int,in>)}"; SQL Variable=""; SQL State="42000"
The above mentioned object, 'trustee_get_all_groups' is a stored procedure that is proprietary to Laserfiche. It is from within the Laserfiche database.
We actually predicted something like this would happen regarding stored procedures, so it wasn’t a big surprise.
To clarify, we are seeking to know specifically what minimized settings the Laserfiche service needs on the SQL server to run all the stored procedures. Is there a select list of stored procedures that need specific permission? We are looking for the minimum privileges required for the Laserfiche application to fully work.
Is there any documentation out there that actually paints the full picture better than the above mentioned whitepaper? I don't want to bash the whitepaper, it was good. I just need additional details.
Backstory: I am in the K-12 Educational sector and we are configuring an environment to be FERPA compliant.
Thank you in advance!