Scenario: Recently, I came across a situation on our production server. It was a SQL agent job failure with below error message:
Could not obtain information about Windows NT group/user 'kks', error code 0xffff0002.". Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly, or connection not established correctly. E... The package execution fa... The step failed.
The interesting thing was that the job runs every 10 mins and it fails occasionally only e.g every 1 or 2 day or so.
Approach: I started troubleshooting the error message by first looking into the permissions for the user account that the job is using for execution. The permissions were OK for the account.
I looked into the error logs and found that the failure was coinciding with the DB mail failure. So I looked into the code that the job was executing. The job was executing a SP that was calling internally another SP named sp_send_dbmail. And Voila … found the offending part !
I checked the DB mail configuration and noticed that the user ‘kks’ didn’t have any default mail profile associated with it.
So when it tries to execute the SP, it fails as the user is not associated with any default profile that it can use to send email. So I assigned the user ‘kks’ a profile with proper distribution list and made that profile default for it.
This way it was fixed and never failed again.
“sometimes little things become unnoticed and cause a lot of pain” :-)
HTH,
Posts
