K2.net™ 2003 Database Security

KB Number: KB000074
Date Published: 12/1/2004 12:00:00 AM
K2.net™ 2003 Server uses Microsoft® SQL Server 2000 with Service Pack 3 as its private data store. The complete data store is made up of 2 physical databases namely the transaction runtime database (called “K2” by default) and the K2.net™ 2003 log database (called “K2Log” by default). The two databases can be installed on a single physical server or different physical servers within a logical network domain. It is recommended, however, that the implementations should occur on the same LAN to maximize the performance capabilities of the software. Microsoft® SQL Server can be installed on a clustered or non-clustered server platform. Each database should have a unique name per implementation. K2.net™ 2003 Server needs to gain access to the databases with the relevant privileges in order to log the state information based on the process that is currently in progress. The following article discusses the security architecture between the various K2.net™ 2003 components and databases.
 
 
K2.net™ 2003 Studio:
The only communication between K2.net™ 2003 Studio and K2.net™ 2003 Server is via the “K2MNG.dll”, and this is when processes get exported from K2.net™ 2003 Studio to K2.net™ 2003 Server. At NO time will K2.net™ 2003 Studio directly access the databases.

K2.net™ 2003 Service Manager: (Not Listed in Image)
The K2.net™ 2003 Service Manager acts as the user interface for “K2MNG.dll”. At NO time will K2.net™ 2003 Service Manager directly access the databases. All administration functionality exposed by the K2.net™ 2003 Service Manager is handled by K2.net™ 2003 Server.

K2.net™ 2003 Worklist:
The K2.net™ 2003 Worklist accesses K2.net™ 2003 Server via “K2MIS.dll”. “K2MIS.dll“ makes use of “K2ROM.dll” to return the correct Worklist items to the user. At NO time will the K2.net™ 2003 Worklist directly access the databases.

K2.net™ 2003 Reports: (Connection "B" in image)
The K2.net™ 2003 Reports is the only component that opens a connection to the “K2Log” database without any interaction from the K2.net™ 2003 Server. The credentials used in the connection between Workspace and the “K2Log” database depend on the authentication mode selected during the installation:
  SQL Authentication:
In a scenario where SQL Authentication was selected during the installation you will find that the actual account is stored in clear text in the various configuration files. Workspace will use this account to open a connection to the database regardless of the user account under which Workspace is running (Note that Workspace is running under integrated auth in IIS, which is a requirement and can at no time be altered). The SQL Authentication account is stored in the "<drive>:\Program Files\K2.net 2003\K2WS\WorkspaceService\Web.config":
  
<appSettings>
  <add key="DSN" value="Data Source=(local); initial catalog=K2Log; User ID=sa;Password=password" />
</appSettings>
  Windows Authentication:
In a scenario where Windows Authentication was selected during the installation you will find that no account is stored in the various configuration files. Workspace is running under integrated authentication in IIS. Therefore, once a user opens Workspace, Workspace will run as the user logged onto the client computer and the actual connection to the SQL Server database will be opened as the account that Workspace is running under. This is not ideal as you will have to give every user making use of Workspace the necessary rights on the “K2Log” database. You can change the following configuration in such a way that you use a dedicated account for Workspace without having to expose any user account in the configuration file by doing the following.
  1)  Create a dedicated application pool in IIS.
  2)  Change the Identity of the application pool to run as a Windows account.
  3)  Change the “WorkspaceService” Site application pool to the newly created application pool (Restart IIS).
  4)  Give the Windows account used in the application pool the necessary rights on the “K2Log” database.
  5)  Open the following configuration file in notepad "<drive>:\Program Files\K2.net 2003\K2WS\WorkspaceService\Web.config".
  6)  Locate and remove the following section:
    
<authentication mode="Windows" />
  7)  Make sure that the “appSettings” section is similar to the example below:
    
<appSettings>
  <add key="DSN" value="Data Source=(local); initial catalog=K2Log; Integrated Security=SSPI"/>
</appSettings>
 
  Custom Applications:
Any custom applications (Windows or Web) designed for workflow processes make use of the “K2ROM.dll” to access K2.net™ 2003 Server. Under no circumstances should any custom application have access to any one of the K2.net™ 2003 databases. It is advised that any K2.net™ functionality required in custom applications be exposed by the available K2 Objects (K2ROM, K2MNG).

K2.net™ 2003 Server: (Connection "A" in image)
K2.net™ 2003 Server is the only component accessing the K2 Transaction and Log databases directly (Insert, Update, Delete and Execute). The credentials used in the connection between K2.net™ 2003 Server and the transaction and log database depends on the authentication mode selected during the installation:
 
  SQL Authentication:
In a scenario where SQL Authentication was selected during the installation you will find that the actual account is stored in clear text in the various configuration files. This account will be used at all times by K2.net™ 2003 Server to access the respective databases. The SQL Authentication account for K2.net™ 2003 Server is stored in the "<drive>:\Program Files\K2.net 2003\bin\K2Server.config" file:
  
<Database ConnectionString="server=(local);database=K2;uid=sa;pwd=;pooling=true;enlist=false; Min Pool Size=5" />
  <LogDatabase ConnectionString="server=(local);database=K2Log;uid=sa;pwd=;pooling=true;enlist=false; Min Pool Size=20" />
  Windows Authentication:
In a scenario where Windows Authentication was selected during the installation you will find that no account is stored in the various configuration files. K2.net™ 2003 Server Service runs as a Windows Service on the machine. The account used by the Service on startup needs to have the necessary rights on the databases, as this is the account used during all connections made to the respective databases. Below an example of the connection information stored in the "<drive>:\Program Files\K2.net 2003\bin\K2Server.config" file:
  
<Database ConnectionString="server=SQLServer ;database=K2;Integrated Security=SSPI;pooling=true;enlist=false; Min Pool Size=5" />
  <LogDatabase ConnectionString="server=SQLServer;database=K2Log;Integrated Security=SSPI;pooling=true; enlist=false;Min Pool Size=5" />
 
  
Important: After changing or updating the configuration settings in the Configuration file, restart the K2.net™ 2003 Server for these changes to take effect.
PRODUCT
K2.net 2003
APPLIES TO
Microsoft SQL
Security
K2.net Workspace
K2.net Server
K2.net 2003