Create DBADashService user on AWS RDS and GCP CloudSQL

[IndrajitMurgude]

Hi @DavidWiseman , Hope you are doing good.
I was trying to add few AWS RDS and GCP CloudSQL instances to DBA Dash monitoring, however, while creating the DBADashService user, we can not make it sysadmin, so I tried using minimal permissions, but it is still failing due to limitations on these cloud db services.
https://dbadash.com/docs/help/security/#running-with-minimal-permissions
Error -
Msg 4613, Level 16, State 1, Line 4 Grantor does not have GRANT permission. Msg 15151, Level 16, State 1, Line 14 Cannot alter the role 'db_datareader', because it does not exist or you do not have permission. Msg 15151, Level 16, State 1, Line 15 Cannot alter the role 'SQLAgentReaderRole', because it does not exist or you do not have permission.

I tried to use the existing admin server login with setupadmin permission, but DBA Dash collectors are failing with multiple errors -
The SELECT permission was denied on the object 'syssessions', database 'msdb', schema 'dbo'. Microsoft.Data.SqlClient.SqlException (0x80131904): User does not have permission to perform this action.

[DavidWiseman]

Hi, we don't have any footprint in GCP so I'm not easily able to test issues on this cloud platform.

The RDS issue with syssessions permission has been fixed #891 in 3.5.1 - just released yesterday. If you upgrade to the latest version it should be working.

[IndrajitMurgude]

Thank you David. For GCP Instances, we are getting these 2 errors all the time, can you please help me to understand for which data collector it is giving these errors -
Microsoft.Data.SqlClient.SqlException (0x80131904): User does not have permission to perform this action. User does not have permission to perform this action. Cannot alter the event session 'DBADash_1', because it does not exist or you do not have permission. Cannot alter the event session 'DBADash_2', because it does not exist or you do not have permission. at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method) at Microsoft.Data.SqlClient.SqlCommand.ExecuteScalar() at DBADash.DBCollector.GetSlowQueries() in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 1186 at DBADash.DBCollector.CollectSlowQueries() in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 1192 at DBADash.DBCollector.ExecuteCollection(CollectionType collectionType) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 953 at DBADash.DBCollector.<>c__DisplayClass66_0.<Collect>b__0(Context _) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 562 at Polly.Policy.<>c__DisplayClass138_0.<Implementation>b__0(Context ctx, CancellationToken token) at Polly.Retry.RetryEngine.Implementation[TResult](Func3 action, Context context, CancellationToken cancellationToken, ExceptionPredicates shouldRetryExceptionPredicates, ResultPredicates1 shouldRetryResultPredicates, Action4 onRetry, Int32 permittedRetryCount, IEnumerable1 sleepDurationsEnumerable, Func4 sleepDurationProvider)
ClientConnectionId:37106ff8-e500-4920-a248-24b39cc796c4
Error Number:15247,State:1,Class:16`

Microsoft.Data.SqlClient.SqlException (0x80131904): The SELECT permission was denied on the object 'syssessions', database 'msdb', schema 'dbo'. at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlDataReader.TrySetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method) at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader() at DBADash.DBCollector.CollectRunningJobs() in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 1018 at DBADash.DBCollector.ExecuteCollection(CollectionType collectionType) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 1004 at DBADash.DBCollector.<>c__DisplayClass66_0.<Collect>b__0(Context _) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 562 at Polly.Policy.<>c__DisplayClass138_0.<Implementation>b__0(Context ctx, CancellationToken token) at Polly.Retry.RetryEngine.Implementation[TResult](Func3 action, Context context, CancellationToken cancellationToken, ExceptionPredicates shouldRetryExceptionPredicates, ResultPredicates1 shouldRetryResultPredicates, Action4 onRetry, Int32 permittedRetryCount, IEnumerable1 sleepDurationsEnumerable, Func4 sleepDurationProvider)
at Polly.Retry.RetryPolicy.Implementation[TResult](Func3 action, Context context, CancellationToken cancellationToken) at Polly.Policy.Implementation(Action2 action, Context context, CancellationToken cancellationToken)
at Polly.Policy.Execute(Action2 action, Context context, CancellationToken cancellationToken) at DBADash.DBCollector.Collect(CollectionType collectionType) in D:\a\dba-dash\dba-dash\DBADash\DBCollector.cs:line 558 ClientConnectionId:fa1bfd88-3547-4095-bab7-10bf5a6156b9 Error Number:229,State:5,Class:14

[DavidWiseman]

Hi, did you grant permissions to create extended events:
GRANT ALTER ANY EVENT SESSION TO <your_service_user>

If this isn't supported in the managed offering on GCP, you can disable the slow query capture

Also, for msdb you should add the service user to db_datareader & SQLAgentReaderRole but it's possible the GCP managed offering might not support this. The RunningJobs collection could be disabled as a workaround. It might be able to use a similar workaround to RDS in future to allow some of the running job info to be captured.