Allow insights to cancel queries

News Knowledge Base
1

Allow insights to cancel queries and resolve the error "Test kill: Cannot cancel queries: Query Killed"

Issue Description

Allow insights to cancel queries and resolve the error "Test kill: Cannot cancel queries: Query Killed"

kb-0EMPa00000ZgkAL.png466×117 10.9 KB

Root Cause

By default this permission is not granted as part of the installation because this permission requires elevated permissions and allows the insights user to kill any connection on the server.

However, enabling this can improve performance and the application will only ever try to kill queries that it initiated.

Resolution

  1. Have DB admin running the following commands on the Insights SQL server database. IMPORTANT: Make sure to update the string USERNAME to the actual Insights SQL user. 
    1. USE master;
GRANT ALTER ANY CONNECTION TO USERNAME;
GRANT VIEW SERVER STATE TO USERNAME;
  2. The settings can be verified with the following query (Make sure to update the USERNAME with the actual user name). 
    1. SELECT
    prin.name AS [User],
    perm.permission_name,
    perm.state_desc
FROM sys.server_permissions perm
INNER JOIN sys.server_principals prin ON perm.grantee_principal_id = prin.principal_id
WHERE prin.name = 'USERNAME'
AND (perm.permission_name = 'ALTER ANY CONNECTION' OR perm.permission_name = 'VIEW SERVER STATE')
  3. After verify the settings, the connection settings in looker can be re-tested (The page has to be reloaded if a test was already conducted.)