If you haven’t set a connection string for your Reporting module, some of your Kentico administrators can run database-changing SQL on your site. Don’t worry! This security concern is easily fixed, and you can do it on your site now – it only takes a few minutes.

When you create a new Report in Kentico, or when you add a new query to a report, you write SQL which is, by default, executed against the main Kentico CMS connection string in your web.config file. When you grant someone Administrator or Global Administrator privilege, or if you grant sufficient permission to create or edit reports, they will be writing those SQL statements as part of report creation.

If those reports are using the default connection string, the database connection permits not only reads on data, but changes to data – and data structure changes! That’s right, it’s possible for an editor to create a report which adds, modifies or deletes data or tables. Sounds not-good, right?

While you may trust your editor not to do that to your database (you brave person!), this means if someone else gets access to their account for some reason, they can escalate privileges, change data, and even nuke your site. Ultimately, reports should always be read only – there’s no reason for a reporting tool to have such powerful access to the database. Luckily for us, Kentico has way for us to easily change the reporting connection string!

The gist of it is this:

1. Set up a new DB user which only has the privileges required (e.g. read-only access to data, access to execute user defined functions and stored procedures used in reports)
2. Add a new connection string for this user to your web config, something like:

   <add name="CMSReportConnectionString" connectionString="Persist Security Info=False;database=DBName;server=ServerName;user id=ReadOnlyDBUser;password=password;Current Language=English;Connection Timeout=240;" />

3. In Kentico admin, go to Settings > Security & Membership > Reporting, and select the reporting connection string
   

   Select the read-only connection string

4. Enjoy the comfort of knowing your site is safe from someone running damaging SQL in your Kentico admin area.

You should ensure that each connection string has only the privileges it needs – both in terms of read/write access, but also which objects they have access to. You might, for example, choose to deny the reports connection string access the CMS_USer table directly, and instead grant permission to a view which doesn’t include password field or other sensitive information. Restricting by certain objects might impact some of Kentico’s out-of-the-box reports, but they can be easily adjusted if you decide you want to be thorough security of the reporting connection string. If you’ve set up additional connection strings for reports, perhaps to other databases, you should check their DB users only have the permissions they need as well.

Happy (and safe) reporting!