Zurück
-
Titel
Permission error when auditing SQL Extended Events when using a local agent which is using Windows authentication. -
Beschreibung
There is a permissions error when reading the events for SQL Extended events when using a local agent and Windows authentication (Windows user account has Sysadmin role for example). Errors similar to the following are logged:
System.Data.SqlClient.SqlException (0x80131904): Cannot view the function 'fn_MSXe_read_event_stream', because it does not exist or you do not have permission.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
at System.Data.SqlClient.SqlDataReader.<>c__DisplayClass189_0.<ReadAsync>b__0(Task t)
at System.Data.SqlClient.SqlDataReader.InvokeRetryable[T](Func`2 moreFunc, TaskCompletionSource`1 source, IDisposable objectToDispose)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.SqlServer.XEvent.XELite.XELiveEventStreamer.<ReadEventStream>d__5.MoveNext()
ClientConnectionId:325861a8-6bfb-4dd7-85d8-bce92a99992d
Error Number:15151,State:1,Class:16
NOTE: The same account seems to work as expected when using remote agent configuration and will work as expected with SQL authentication on local agent with the same role.
-
Ursache
This is caused by a permission for the local system service account that is not set in the SQL server by default -
Lösung
WORKAROUND:
Setting When the `NT AUTHORITY\SYSTEM' user has the "View Server State" securable in SQL resolves the issue when using a local agent with Windows Authentication in SQL. When using SQL authentication this does not occur.
STATUS:Defect Logged: 376636 - Permission error reading events stream when local agent is using Windows authentication. This will be fixed in a future release of Change Auditor
-
Fehler-ID
376636