SQL Service Instance - Timeout Expired: How to Limit SQL Permissions
KBS100261
PRODUCTIssue
When trying to register a SQL Service Instance against a large custom database, you may experience the following timeout error:
SmartObject Server Exception: Execution Timeout Expired: The timeout period elapsed prior to completion of the operation or the server is not responding.
Symptoms
You are unable to register a SQL Service Instance of your desired custom database. If you are using K2 Cloud and you are trying to connect to an OnPrem data source, and your relevant settings are correct, you may experience this error:
A network error has occured. Unable to execute a server-side operation.
Resolution
The current functionality within K2 is that all objects that can be seen, will be described. There is currently no Out of the Box tooling in the UI (K2 Management) to allow a user to only describe specific objects.
However, there is a feature to enable this in the product, please vote for it here.
The best way forward would be to reduce the list of tables that the K2 service account (or any user that is doing this using impersonate) can see. If you set a DENY action on the VIEW DEFINITION action on a table for a user, this will cause SQL to not return that object in the list that the user can see (if set to impersonate), thus it will not be described.
If you are using the service account for authentication, then you can just deny the VIEW DEFINITION action for the service account.
Note: If the user in SQL is a DBOwner, it will not work, the Owner role trumps explicit deny rules. We recommend adding 'User1' and assigning him the DataReader,DataWriter and AccessAdmin database roles (not owner) and then add deny rights for the View Definition action on some tables. However, this is up to the user how they want to setup their SQL permissions. This way of setting permissions will also allow them in future, if they want to describe new objects, to just remove the deny rule on that object and refresh the service instance, and it should become available.
Example
Execute the following script against the Custom Database:
SELECT 'DENY VIEW DEFINITION ON OBJECT:: "' + TABLE_SCHEMA + '"."' + TABLE_NAME + '" TO ""Relevant Account]"' FROM information_schema.tables
This script will list the query you need to execute, to deny set permissions against a large list of tables within the relevant custom database.
Copy the script and execute it in a new query window:
You will notice that all tables have been denied access to the relevant user:
After refreshing the SQL Service Instance, you will notice the user does not see the relevant tables, when trying to generate SmartObjects:
You can explicitly give rights to certain tables doing the following, un-tick the ‘Deny’ box for ‘View Definition’:
Refresh the SQL Service instance, and you will now see Table 2 only:
For additional references, please refer to the following articles:
Register SQL Service Instance: Timeout Expired
Considerations: SQL Server Service
- You may want to use SQL permissions to limit the access of the K2 Service Account which is used to discover the SQL entities, so that K2 only discovers the entities that the Service Account has access to. This approach can also be used to restrict the number of entities K2 discovers and generates Service Objects for when registering or refreshing the service.
- To discover the Stored Procedures, the K2 Service Account needs View Definition permissions on stored procedures in the targeted SQL database.
Considerations: Troubleshooting the SQL Server Service Type
- For a simple database, the permissions to create the instance are: data reader; data writer; and execute. However, depending on the nature of the stored procedures, DBO permissions may also be necessary. Granting DBO permissions can be a problem for very large databases, due to the broker and how it scans objects to create the instance. For very large databases you need to limit the permissions the account has to only the objects you want to create.