May 29, 2015 at 9:01 am
I am trying to import this years worth of failed logins and last successful login for each user out of the logs using master.dbo.xp_readerrorlog. The script essentially loops through the linked servers I have on my DBA box and reaches out for the log data. It works, but here is the error I am getting on most of our production servers:
OLE DB provider "SQLNCLI11" for linked server "AWSCADENCEDB01" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 17
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "AWSCADENCEDB01" was unable to begin a distributed transaction.
I know how to enable distributed transactions on the servers that error out, but if it is not needed for anything other then my audit script, I doubt the business will approve me turning on distributed transactions at those locations (so I am not even going to ask).
I am attempting to setup a singular audit .rdl with the information I want to review quarterly. Does anyone else have a solution to import this data?
CREATE PROC [dbo].[Import_Login_Data]
AS
IF EXISTS (
SELECT 1
FROM master.sys.servers
WHERE is_linked = 1
)
BEGIN
DECLARE @LinkedServer NVARCHAR(256)
SELECT @LinkedServer = MIN(NAME)
FROM master.sys.servers
WHERE is_linked = 1
WHILE @LinkedServer IS NOT NULL--Loop for all linked servers
BEGIN
IF OBJECT_ID('tempdb..#TempLog') IS NOT NULL--Drop #TempLog if it exists
BEGIN
DROP TABLE #TempLog
END;
CREATE TABLE #TempLog (--Create temp table with xp_readerrorlog data
LogDate DATETIME,
ProcessInfo NVARCHAR(50),
TEXT NVARCHAR(2000)
);
DECLARE @LinkedMaster NVARCHAR(256)
SELECT @LinkedMaster = (@LinkedServer + '.master.dbo.xp_readerrorlog -1, 1, NULL, NULL, ''2015-01-01'', ''2015-06-02''')
INSERT INTO #TempLog (LogDate, ProcessInfo, TEXT)
EXEC sp_executesql @LinkedMaster;--Dates for log data you want to pull
--INSERT INTO AuditLogins.dbo.FailedLogins--Load static table w failed logins
SELECT MONTH(LogDate),
@LinkedServer AS [Server],
COUNT(TEXT) AS [FailedAttempts],--Failed login count
TEXT AS [Details]
FROM #TempLog
WHERE ProcessInfo = 'Logon'
AND TEXT LIKE '%failed%'
GROUP BY LogDate, TEXT
ORDER BY Logdate;
INSERT INTO AuditLogins.dbo.LastLogin--Load static table w last successful login
SELECT--Useful to know before deleting "obsolete" accounts.
MAX(LogDate),
@LinkedServer AS [Server],
'Successful - Last login at (' + CONVERT(NVARCHAR(64), MAX(LogDate)) + ')' AS [LastLogin],
TEXT AS [Details]
FROM #TempLog
WHERE ProcessInfo = 'Logon'
AND TEXT LIKE '%succeeded%'
AND TEXT NOT LIKE '%NT AUTHORITY%'
GROUP BY TEXT;
SELECT @LinkedServer = MIN(NAME)
FROM master.sys.servers
WHERE is_linked = 1
AND NAME > @LinkedServer;
END-- End of WHILE loop
END
May 29, 2015 at 9:45 am
One thing that just hit me is that I am attempting to pull the xp_readerrorlog data across the linked server. Would I encounter the distributed transaction error if I instead pulled the log data to tempdb on the remote server and then moved the data from the linked server tempdb over to a permanent table on my DBA box?
May 29, 2015 at 1:22 pm
Answer to my own question is "no DDL actions allowed using a linked server". I worked my way around it by adding a sproc in the master database in each of the prod servers:
USE master
GO
CREATE PROC Load_Audit_Log
AS
BEGIN
IF OBJECT_ID('master..TempLog') IS NOT NULL--Drop TempLog table if it exists
BEGIN
DROP TABLE TempLog
END;
CREATE TABLE TempLog (--Create temp table for xp_readerrorlog data
LogDate DATETIME,
ProcessInfo NVARCHAR(50),
TEXT NVARCHAR(2000)
);
INSERT INTO TempLog (LogDate, ProcessInfo, TEXT)
EXEC master.dbo.xp_readerrorlog -1, 1, NULL, NULL, '2015-01-01', '2015-06-01';
INSERT INTO SLCSQLSAFE01.AuditLogins.dbo.FailedLogins--Load static table w failed logins
SELECT LogDate,
@@SERVERNAME AS [Server],
COUNT(TEXT) AS [FailedAttempts],--Failed login count
TEXT AS [Details]
FROM master.dbo.TempLog
WHERE ProcessInfo = 'Logon'
AND TEXT LIKE '%failed%'
GROUP BY LogDate, TEXT
ORDER BY Logdate;
INSERT INTO SLCSQLSAFE01.AuditLogins.dbo.LastLogin--Load static table w last successful login
SELECT--Useful to know before deleting "obsolete" accounts.
MAX(LogDate),
@@SERVERNAME AS [Server],
'Successful - Last login at (' + CONVERT(NVARCHAR(64), MAX(LogDate)) + ')' AS [LastLogin],
TEXT AS [Details]
FROM master.dbo.TempLog
WHERE ProcessInfo = 'Logon'
AND TEXT LIKE '%succeeded%'
AND TEXT NOT LIKE '%NT AUTHORITY%'
GROUP BY TEXT;
DROP TABLE TempLog;
END
GO
From there I installed the sproc below on my DBA box and an agent job (for scheduling) to call the sproc.
CREATE PROC [dbo].[Import_Login_Data]
AS
IF EXISTS (
SELECT 1
FROM master.sys.servers
WHERE is_linked = 1
)
BEGIN
DECLARE @LinkedServer NVARCHAR(256)
SELECT @LinkedServer = MIN(NAME)
FROM master.sys.servers
WHERE is_linked = 1
WHILE @LinkedServer IS NOT NULL--Loop for all linked servers
BEGIN
EXEC (@LinkedServer + '.master.dbo.Load_Audit_Log');
SELECT @LinkedServer = MIN(NAME)
FROM master.sys.servers
WHERE is_linked = 1
AND NAME > @LinkedServer;
END-- End of WHILE loop
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply