CRM - SQL Authentication Issue

  • Article

 

CRM - SQL Authentication Issue

 

Event error logs are useful. However, it is huge to go through and find the relevant error message. I am sharing this handy script that will help to identify the logon failure details at SQL side. For instance if CRM App Pool account or any service account is failing to access database or failing to get authenticated by SQL then we can collect all the details using below scripts.

 

Let's say you are trying to access CRM url https://crm.habib.local:5555 and you are receiving below error message in browser. It's clear from the error message that "Habib\CRMAppPoolSvc" account is failing to login or authentication is failing.

 

 

In order to find more details about this error from SQL side, you can use below scripts

  • You can use below SP to find all error logs in SQL archived logs

    EXEC sp_readerrorlog

     

  • To search particular archived logs for particular string use below script. For example if you want to search current log and archive#1 log for error "Logon Failed", you can use below script

     

EXEC sp_readerrorlog0, 1, 'Login failed'

Please Note: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...

  • You can replace your search string accordingly with any service account which you are searching in error logs

     

EXEC sp_readerrorlog0, 1, 'HABIB\CRMAppPoolSvc'

 

 

  • Now the above script will give either all the error logs or filtered error logs. You can use below master script to pull all the failure details related to "Logon" process, which could fail due to various reasons and with various errors.

    This will create 2 temp tables and drop it after the execution.

 

DECLARE @TSQL NVARCHAR(2000)

DECLARE @lC INT

CREATE TABLE #TempLog (

      LogDate DATETIME,

      ProcessInfo NVARCHAR(50),

      [Text] NVARCHAR(MAX))

CREATE TABLE #logF (

      ArchiveNumber INT,

      LogDate DATETIME,

      LogSize INT

)

INSERT INTO #logF  

EXEC sp_enumerrorlogs

SELECT @lC = MIN(ArchiveNumber) FROM #logF

WHILE @lC IS NOT NULL

BEGIN

      INSERT INTO #TempLog

      EXEC sp_readerrorlog@lC

      SELECT @lC = MIN(ArchiveNumber) FROM #logF

      WHERE ArchiveNumber > @lC

END

--Failed login counts. Useful for security audits.

SELECT 'Failed - ' + CONVERT(nvarchar(5), COUNT(Text)) + ' attempts' AS [Login Attempt], Text AS Details

FROM #TempLog

where ProcessInfo = 'Logon'

and Text like '%failed%'

Group by Text

 

DROP TABLE #TempLog

DROP TABLE #logF