Solved

Questions about connecting to a new on-premises SQL Server database

  • 31 December 2018
  • 4 replies
  • 6 views

Badge +3

Having received great advice and responses on my last question regarding setting up a custom SQL Server database to host content, I have some follow-up questions. First, a quick overview:

 

  • We're outgrowing SharePoint and have decided to stand up a new SQL Server 2016 database to host K2 content (totally separate from our K2 app database)
  • We're using K2 Five on-premises and our database will also be on-prem within our firewall
  • We have Active Directory on-prem, synched to Azure AD

Questions before we create the database:

  • Is SQL Server 2016 Standard edition a good choice or is another option recommended?
  • Does K2 care about what the new database or server is named?
  • In one of our K2 classes, we learned that either Impersonate or Service Account would normally be used to connect to SQL databases. Is one recommended one over the other? We want to ensure we’re setting up proper permissions to the database. I assume Impersonate would require granting large numbers of AD users read/write permissions to the database, while Service Account would just mean we’d open it to the K2 service account. Is that correct?
    • Also, would using Service Account present any challenges with auditing transactions in SQL (i.e., if all actions appear to be performed by the Service account?).

Thanks everyone!

icon

Best answer by DavidL1 31 December 2018, 23:50

View original

4 replies

Badge +11
  • You can find what software versions K2 supports by looking at the support matrix in the help files (2016 Standard is supported).
  • K2 does not care about the name of your database as long as it conforms to SQL Server standards.  It's more important to use a name that will be meaningful and unambiguous to your developers and DBAs.
  • How important is the security of the data?  Will it assist your competitors or hackers?  Do you need to audit the data?   If a "60 Minutes" camera crew shows up at your office with a printout of the data, will you try to escape out the back door?   If you answer yes to any of those questions you should use impersonate.  Every request will be made within the security context of the requestor and it can be audited.  That does mean more work defining groups for access.  Using service account is like saying anyone can access the data and you don't care because all access it is in the security context of the K2 Service Account.  Protect your data like there are bad people out there and your mom watches "60 Minutes."
Userlevel 5
Badge +13

That is a great way of explaining the third bullet... I will add to it that it is far simpler to use the Service Account approach and yes, you will lose automatical SQL auditing but I've found it almost easier to build in my own auditting just to avoid having to set up user access groups. In situations where auditing is important, I pass in the current user name on all of my inserts/updates/deletes and set up triggers to record that activity to a seperate table that is easy to report off of.

Badge +3

Thanks for your input! We're working with our DB team to find out what they recommend, and this helps us out.

Badge +3
Thanks, David! This all makes sense. Appreciate your input.

Reply