How to read verify integrity results (4263293)

Return

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title

How to read verify integrity results
Description

When reading information from the integrity check, application does not show full information on potential tampering. How to see the full details?
On Audit Verify - I amended a record in the ApexSQL.Event table – but it doesn’t give much detail on who made the change – i.e. username, location, IPaddress, etc – Would this information shown somewhere else?
Cause

Information shown in the Verify integrity check results shows ID values and it is not possible to understand which logins, objects or events are affected by potential tampering.

Resolution

Integration checks are performed both on a regular basis, automatically by ApexSQL Audit built-in alerting mechanisms where the integrity of audited data is checked every 30 minutes (by default), as well as on-demand where users can check data integrity manually by initiating the check through Verify Central Repository | Start options:

Note that both active central repository as well as all previously created archives, can be checked for potential tampering at any given time by simply checking the box next to the repository name before clicking on the Start button.

Regardless of the integrity check method, if any tampering is detected, it will be shown after short processing in a table like this one:

From the screenshot above, it is clear that some information in the Details column is incomplete, and in order to see full details, users have to manually query appropriate tables to get the information on the mentioned IDs.

Here is a list of tables to query:

Table field shows the name of the table in the central repository database that was affected by the tampering
Changed column shows the exact date and time when the tampering occurred
Row id column shows SQL Server id for the specific row
Details column shows a variety of details on the tampering event including application and SQL Server login that were used to perform the tampering, Schema and object of the affected object as well as the operation which made the change

ApexSQLCRD Table	Integrity report value
ApexSQL.ApplicationName	ApplicationNameID
ApexSQL.LoginName	LoginNameID
ApexSQL.SchemaName	SchemaNameID
ApexSQL.ObjectName	ObjectNameID

Example:

If we are looking to identify our ‘LoginNameID = 4’, we should query the ‘ApexSQL.LoginName’ table and search for Login with ID column ‘4’, like this:

Additionally, here is a full list of operations referenced in the Operation ID in the report:

ID	Operation	ID	Operation
0	None	89	CreateRemoteServiceBinding
1	Unknown	90	CreateRole
2	TraceStarted	91	CreateRoute
3	TraceClosed	92	CreateService
4	AuditServerAlterTraceEvent	93	CreateSymmetricKey
5	AuditServerStartsAndStops	94	CreateSynonym
6	AssemblyLoad	95	CreateUser
7	Insert	96	CreateXmlSchemaCollection
8	Update	97	AlterApplicationRole
9	Delete	98	AlterAssembly
10	BulkInsert	99	AlterAsymmetricKey
11	SelectInto	100	AlterAuthorization
12	WriteText	101	AlterCertificate
13	Merge	102	AlterCredential
14	UpdateText	103	AlterDatabase
15	Select	104	AlterEndpoint
16	ReadText	105	AlterFullTextCatalog
17	Exec	106	AlterFullTextIndex
18	CreateTable	107	AlterIndex
19	DropTable	108	AlterLogin
20	AlterTable	109	AlterMessageType
21	CreateView	110	AlterPartitionFunction
22	DropView	111	AlterPartitionScheme
23	AlterView	112	AlterQueue
24	CreateProcedure	113	AlterRemoteServiceBinding
25	DropProcedure	114	AlterRole
26	AlterProcedure	115	AlterRoute
27	CreateIndex	116	AlterSchema
28	DropIndex	117	AlterService
29	CreateFunction	118	AlterServiceMasterKey
30	DropFunction	119	AlterSymmetricKey
31	AlterFunction	120	AlterUser
32	CreateTrigger	121	AlterXmlSchemaCollection
33	DropTrigger	122	DropAggregate
34	AlterTrigger	123	DropApplicationRole
35	EnableTrigger	124	DropAssembly
36	DisableTrigger	125	DropAsymmetricKey
37	CreateRule	126	DropCertificate
38	DropRule	127	DropContract
39	RenameObject	128	DropCredential
40	CreateDefault	129	DropDatabase
41	DropDefault	130	DropEndpoint
42	CreateType	131	DropEventNotification
43	DropType	132	DropFulltextCatalog
44	CreateStatistics	133	DropFulltextIndex
45	DropStatistics	134	DropLogin
46	CreateSchema	135	DropMessageType
47	DropSchema	136	DropPartitionFunction
48	CreateSequence	137	DropPartitionScheme
49	AlterSequence	138	DropQueue
50	DropSequence	139	DropRemoteServiceBinding
51	GrantDatabasePermissions	140	DropRoute
52	DenyDatabasePermissions	141	DropRole
53	RevokeDatabasePermissions	142	DropService
54	GrantServerPermissions	143	DropSignature
55	DenyServerPermissions	144	DropSymmetricKey
56	RevokeServerPermissions	145	DropSynonym
57	Backup	146	DropUser
58	Restore	147	DropXmlSchemaCollection
59	ErrorAttention	148	BitmapWarning
60	AddSignature	149	BackgroundJobError
61	CreateMasterKey	150	BlockedProcessReport
62	AlterMasterKey	151	ErrorLog
63	DropMasterKey	152	EventLog
64	CloseMasterKey	153	Exception
65	CloseSymmetricKey	154	UserErrorMessage
66	OpenMasterKey	155	DatabaseSuspectDataPage
67	OpenSymmetricKey	156	ExchangeSpillEvent
68	Revert	157	ExecutionWarnings
69	Dbcc	158	CpuThresholdExceeded
70	TruncateTable	159	HashWarning
71	UpdateStatistics	160	MissingColumnStatistics
72	CreateDatabase	161	MissingJoinPredicate
73	CreateAggregate	162	SortWarnings
74	CreateApplicationRole	163	AuditLoginFailed
75	CreateAssembly	164	AuditLogin
76	CreateAsymmetricKey	165	AuditLogout
77	CreateCertificate	166	CreateServerRole
78	CreateContract	167	AlterServerRole
79	CreateCredential	168	DropServerRole
80	CreateEndpoint	169	DropDatabaseTrigger
81	CreateEventNotification	170	AlterDatabaseTrigger
82	CreateFullTextCatalog	171	CreateDatabaseTrigger
83	CreateFullTextIndex	172	CreateServerTrigger
84	CreateLogin	173	AlterServerTrigger
85	CreateMessageType	174	DropServerTrigger
86	CreatePartitionFunction	175	ServerPrincipalImpersonation
87	CreatePartitionScheme	176	DatabasePrincipalImpersonation
88	CreateQueue

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Request a KB Article

How to read verify integrity results (4263293)

Title

Description

Cause

Resolution

Leave a Comment