Configuring the eG Agent with Query Execution Permissions

To pull metrics from the SSIS database, the eG agent requires SYSADMIN privileges to that database. However, if, owing to security constraints, administrators prefer not to expose the credentials of a SYSADMIN user, then you can create a special user on the Microsoft SQL Server (hosting the SSIS database) with SSIS_LOGREADER rights to that database.

To create such a user, do the following:

  1. Login to the Microsoft SQL Server Management Studio as a user with SYSADMIN privileges.

  2. Use the following script to create a special user with SSIS_LOGREADER privileges:

    USE [master]

    GO

    CREATE LOGIN [<Name_of_new_user>] WITH PASSWORD=N'<Password_of_new_user>', DEFAULT_DATABASE=[SSISDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    GO

    USE [SSISDB]

    GO

    CREATE USER [<Name_of_new_user>] FOR LOGIN [<Name_of_new_user>]

    GO

    USE [SSISDB]

    GO

    ALTER USER [<Name_of_new_user>] WITH DEFAULT_SCHEMA=[catalog]

    GO

    USE [SSISDB]

    GO

    ALTER ROLE [ssis_logreader] ADD MEMBER [<Name_of_new_user>]

    GO

  3. Edit the script to replace the following variables with relevant values:

    <Name_of_new_user> - Specify the name of the new user.

    <Password_of_new_user> - Specify the password of the new user.

    For instance, to create a user named ‘eglogreader’ with password ‘logreader’, your script should be changed as shown below:

    USE [master]

    GO

    CREATE LOGIN [eglogreader] WITH PASSWORD=N'logreader', DEFAULT_DATABASE=[SSISDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    GO

    USE [SSISDB]

    GO

    CREATE USER [eglogreader] FOR LOGIN [eglogreader]

    GO

    USE [SSISDB]

    GO

    ALTER USER [eglogreader] WITH DEFAULT_SCHEMA=[catalog]

    GO

    USE [SSISDB]

    GO

    ALTER ROLE [ssis_logreader] ADD MEMBER [eglogreader]

    GO

  4. After the edits, run the script to create the new user.

Finally, when configuring the tests for the managed Microsoft SQL Server Integration Server component using the eG admin interface, make sure you configure the USERNAME and PASSWORD parameters of the tests with the username and password of the new user you created using steps 1-4 above.