Reading through the error you may have the unfortunate issue of seeing the error 18456 followed by the following log

DESCRIPTION: Login failed for user ‘xxx’. Reason: Failed to open the explicitly specified database. [CLIENT: xx.xx.xx.xx]

The problem with the issue is, that you don’t know what database it is trying to connect to, to help resolve the issue. Run the below server side trace which records “Audit login Failed”, “ErrorLog” and “User Error Message” filtering on the login name. simple update the starting variables for file location, maxfile size and loginname, and run. at the end of the scripts are comments to stop the trace, write the trc file to a database and do a quick query to pull out the offending database.

/*

Trace - Cannot Open Database 

Run this trace to find the name of the database when you receive a sev14 / error in the SQL Server errorlog

DESCRIPTION:	Login failed for user 'xxx'. Reason: Failed to open the explicitly specified database. [CLIENT: xx.xx.xx.xx]

Update the following Fields:

@filename = name of the file 
@maxFilesize = max file size 
@loginName = Name of the offending login user

Note sp_trace_create is set to 2 to 'TRACE_FILE_ROLLOVER'
https://msdn.microsoft.com/en-us/library/ms190362.aspx?f=255&MSPPError=-2147217396

*/

-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
DECLARE @fileName NVARCHAR(256)
DECLARE @loginName NVARCHAR(100)

-- Update the below settings! 
SET @maxfilesize = 500 
SET @fileName = N'C:\Temp\Login'
SET @loginName = N'%EPiServer_User%'

EXEC @rc = sp_trace_create @traceid = @TraceID OUTPUT, @options = 2,
    @tracefile = @fileName, @maxfilesize = @maxfilesize, @filecount = NULL 
IF ( @rc != 0 )
    GOTO error

-- Client side File and Table cannot be scripted

-- Set the events
DECLARE @on BIT
SET @on = 1
EXEC sp_trace_setevent @TraceID, 22, 7, @on
EXEC sp_trace_setevent @TraceID, 22, 31, @on
EXEC sp_trace_setevent @TraceID, 22, 8, @on
EXEC sp_trace_setevent @TraceID, 22, 64, @on
EXEC sp_trace_setevent @TraceID, 22, 1, @on
EXEC sp_trace_setevent @TraceID, 22, 9, @on
EXEC sp_trace_setevent @TraceID, 22, 41, @on
EXEC sp_trace_setevent @TraceID, 22, 49, @on
EXEC sp_trace_setevent @TraceID, 22, 6, @on
EXEC sp_trace_setevent @TraceID, 22, 10, @on
EXEC sp_trace_setevent @TraceID, 22, 14, @on
EXEC sp_trace_setevent @TraceID, 22, 26, @on
EXEC sp_trace_setevent @TraceID, 22, 3, @on
EXEC sp_trace_setevent @TraceID, 22, 11, @on
EXEC sp_trace_setevent @TraceID, 22, 35, @on
EXEC sp_trace_setevent @TraceID, 22, 51, @on
EXEC sp_trace_setevent @TraceID, 22, 4, @on
EXEC sp_trace_setevent @TraceID, 22, 12, @on
EXEC sp_trace_setevent @TraceID, 22, 20, @on
EXEC sp_trace_setevent @TraceID, 22, 60, @on
EXEC sp_trace_setevent @TraceID, 162, 7, @on
EXEC sp_trace_setevent @TraceID, 162, 31, @on
EXEC sp_trace_setevent @TraceID, 162, 8, @on
EXEC sp_trace_setevent @TraceID, 162, 64, @on
EXEC sp_trace_setevent @TraceID, 162, 1, @on
EXEC sp_trace_setevent @TraceID, 162, 9, @on
EXEC sp_trace_setevent @TraceID, 162, 41, @on
EXEC sp_trace_setevent @TraceID, 162, 49, @on
EXEC sp_trace_setevent @TraceID, 162, 6, @on
EXEC sp_trace_setevent @TraceID, 162, 10, @on
EXEC sp_trace_setevent @TraceID, 162, 14, @on
EXEC sp_trace_setevent @TraceID, 162, 26, @on
EXEC sp_trace_setevent @TraceID, 162, 30, @on
EXEC sp_trace_setevent @TraceID, 162, 50, @on
EXEC sp_trace_setevent @TraceID, 162, 66, @on
EXEC sp_trace_setevent @TraceID, 162, 3, @on
EXEC sp_trace_setevent @TraceID, 162, 11, @on
EXEC sp_trace_setevent @TraceID, 162, 35, @on
EXEC sp_trace_setevent @TraceID, 162, 51, @on
EXEC sp_trace_setevent @TraceID, 162, 4, @on
EXEC sp_trace_setevent @TraceID, 162, 12, @on
EXEC sp_trace_setevent @TraceID, 162, 20, @on
EXEC sp_trace_setevent @TraceID, 162, 60, @on
EXEC sp_trace_setevent @TraceID, 20, 7, @on
EXEC sp_trace_setevent @TraceID, 20, 23, @on
EXEC sp_trace_setevent @TraceID, 20, 31, @on
EXEC sp_trace_setevent @TraceID, 20, 8, @on
EXEC sp_trace_setevent @TraceID, 20, 64, @on
EXEC sp_trace_setevent @TraceID, 20, 1, @on
EXEC sp_trace_setevent @TraceID, 20, 9, @on
EXEC sp_trace_setevent @TraceID, 20, 21, @on
EXEC sp_trace_setevent @TraceID, 20, 49, @on
EXEC sp_trace_setevent @TraceID, 20, 57, @on
EXEC sp_trace_setevent @TraceID, 20, 6, @on
EXEC sp_trace_setevent @TraceID, 20, 10, @on
EXEC sp_trace_setevent @TraceID, 20, 14, @on
EXEC sp_trace_setevent @TraceID, 20, 26, @on
EXEC sp_trace_setevent @TraceID, 20, 30, @on
EXEC sp_trace_setevent @TraceID, 20, 3, @on
EXEC sp_trace_setevent @TraceID, 20, 11, @on
EXEC sp_trace_setevent @TraceID, 20, 35, @on
EXEC sp_trace_setevent @TraceID, 20, 51, @on
EXEC sp_trace_setevent @TraceID, 20, 12, @on
EXEC sp_trace_setevent @TraceID, 20, 60, @on


-- Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT

EXEC sp_trace_setfilter @TraceID, 11, 0, 6, @loginName
-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1

-- display trace id for future references
SELECT  TraceID = @TraceID
GOTO finish

error: 
SELECT  ErrorCode = @rc

finish: 
GO


/*

-- confirm trace is traceid2 and stop it
 SELECT * FROM sys.fn_trace_getinfo(0) ; -- Get info on all server-side traces
 sp_trace_setstatus  @traceid =  2,  @status =  0	-- Trace stop
 sp_trace_setstatus  @traceid =  2,  @status =  2   -- Trace delete
 SELECT * FROM sys.fn_trace_getinfo(0) ; -- Get info on all server-side traces

*/

/*

-- Import Results into Table

USE DBA;
GO
SELECT * INTO temp_trc
FROM fn_trace_gettable('C:\Temp\Login.trc', default);
GO

*/

/*
-- Query Results
SELECT [HostName]
      ,[StartTime]
      ,[TextData]
  FROM [DBA].[dbo].[temp_trc]
  where TextData like 'cannot open database%'

*/



Advertisements