The Kaseya installation process will not run without being given the SA password to the server – even if the Windows user ID the installer is running in has sysadmin rights to the SQL server.
As part of the installation, it creates its own database (fine, no problem with that), and then creates its own user ID (again, not a problem). However, it grants to that user ID a dizzying array of privileges which it really shouldn’t need:
So what’s happened is the Kaseya installation has granted its user account “god” privileges over the SQL Server, and is using that to derive the permissions it actually needs on its own database, rather than limiting itself to just the rights it actually needs. Needless to say, I’m not impressed.
There are other issues here. The Kaseya system by default puts in its own backup & dbcc routines – sure, this is a great idea in an environment where there isn’t a SQL Server administrator; however, there doesn’t seem to be an option within the system to tell it that it’s on a server that is properly looked after, and there’s no need for it to do this itself. So every night, there are two backups done of this database; similarly, there are two lots of integrity checks, there are two lots of index rebuilds etc.
The database files themselves. The .mdf file is created in the SQL Server\data directory, neatly overwriting the default settings provided by SQL Server. The .ldf file is, however, created where we’ve carefully configured SQL Server to put LDFs. We asked Kaseya about this, and said that we wanted it moving to a different location; they pointed us to a Wizard that would migrate the database to a separate server. (Indeed, the database name is hard-coded to “ksbuscribers” rather than something indicating Kaseya, or the purpose of the database…)
As yet, no response from Kaseya about exactly what permissions are needed. We need to tie this down so we can maintain operational security over this server. As it is, I am reluctant to put anything else on it – particularly a *real* application.
Update: A week or so later, we had a response from Kaseya:
Following the recommended standards from Microsoft on SQL setup process, when installing K2 it creates a new db logon for ourselves. That credential does everything from that point forward including:
- Create the ksubscribers database
- Automatically perform all the database backups
- Schema creation and updates
- Create other credentials to support db view access
- Roll the password for this account, etc…
As you can see from the list above, this logon needs a lot of rights. You are welcome to change any permission keeping in mind that Support won’t be able to assist you when K2 stops functioning or affect performance, because we have not tested this and do not know what the consequences are of restricting that credential.
- The installation is the only process that needs these rights – it can create the databases, logins, user credentials, and permissions for the kaseya database user to create schema objects etc.
- The database server maintenance plan will handle database backups; this can always be created by the installation script if required.
So… given this response from Kaseya, I am reluctant to allow any other databases on that particular server…
…and, a few days later, as a timely addendum, I just saw this in the activity logs for the server:
Server Status Database spid BlkBy Name wt dur cmd login ------- -------- ------------- ----- ------ ------------------------------ --- ---- ---------------- ---------------- W2SUPP Waiting ksubscribers 56 86 SQLDMO_1 10 10 BACKUP LOG Administrator W2SUPP Blocker ksubscribers 86 0 Internet Information Services 0 12 BACKUP DATABASE KaseyaVSA7D8314 W2SUPP Blocker master 86 0 Internet Information Services 0 12 BACKUP DATABASE KaseyaVSA7D8314 W2SUPP Blocker master 86 0 Internet Information Services 0 12 BACKUP DATABASE KaseyaVSA7D8314
And finally… I bet the Kaseya team has never been faced with this set of questions: http://www.straightpathsql.com/archives/2009/01/dba-questions-to-ask-a-vendor/