SQLServer 2016 Logs are full of Login Failed to a database that no longer exist
Deleted a database in sql 2016 a week ago, and now logs are showing login failed for user xxxx\xxxx (domain account replaced with x's), cannot open database. I have scrubbed everything i can think of for hours and can't find the culprit.
SqlServer Logs:
04/02/2020 09:46:15,Logon,Unknown,Log
04/02/2020 09:46:00,Logon,Unknown,Log
04/02/2020 09:41:00,Logon,Unknown,Log
04/02/2020 09:40:45,Logon,Unknown,Log
04/02/2020 09:35:45,Logon,Unknown,Log
SqlServer Agent Logs:
04/02/2020 09:46:15,,Error,[298] SQLServer Error: 4060<c/> Cannot open database "SWPDM_CortecPDM" requested by the login. The login failed. [SQLSTATE 42000]
04/02/2020 09:46:15,,Error,[298] SQLServer Error: 18456<c/> Login failed for user 'xxxx\xxxxxxxx'. [SQLSTATE 28000]
04/02/2020 09:46:00,,Error,[298] SQLServer Error: 4060<c/> Cannot open database "SWPDM_CortecPDM" requested by the login. The login failed. [SQLSTATE 42000]
04/02/2020 09:46:00,,Error,[298] SQLServer Error: 18456<c/> Login failed for user 'xxxx\xxxxxxxx'. [SQLSTATE 28000]
04/02/2020 09:41:00,,Error,[298] SQLServer Error: 4060<c/> Cannot open database "SWPDM_CortecPDM" requested by the login. The login failed. [SQLSTATE 42000]
04/02/2020 09:41:00,,Error,[298] SQLServer Error: 18456<c/> Login failed for user 'xxxx\xxxxxxxx'. [SQLSTATE 28000]
04/02/2020 09:40:45,,Error,[298] SQLServer Error: 4060<c/> Cannot open database "SWPDM_CortecPDM" requested by the login. The login failed. [SQLSTATE 42000]
04/02/2020 09:40:45,,Error,[298] SQLServer Error: 18456<c/> Login failed for user 'xxxx\xxxxxxxx'. [SQLSTATE 28000]
04/02/2020 09:35:45,,Error,[298] SQLServer Error: 4060<c/> Cannot open database "SWPDM_CortecPDM" requested by the login. The login failed. [SQLSTATE 42000]
04/02/2020 09:35:45,,Error,[298] SQLServer Error: 18456<c/> Login failed for user 'xxxx\xxxxxxxx'. [SQLSTATE 28000]
SqlServer Logs:
04/02/2020 09:46:15,Logon,Unknown,Log
04/02/2020 09:46:00,Logon,Unknown,Log
04/02/2020 09:41:00,Logon,Unknown,Log
04/02/2020 09:40:45,Logon,Unknown,Log
04/02/2020 09:35:45,Logon,Unknown,Log
SqlServer Agent Logs:
04/02/2020 09:46:15,,Error,[298] SQLServer Error: 4060<c/> Cannot open database "SWPDM_CortecPDM" requested by the login. The login failed. [SQLSTATE 42000]
04/02/2020 09:46:15,,Error,[298] SQLServer Error: 18456<c/> Login failed for user 'xxxx\xxxxxxxx'. [SQLSTATE 28000]
04/02/2020 09:46:00,,Error,[298] SQLServer Error: 4060<c/> Cannot open database "SWPDM_CortecPDM" requested by the login. The login failed. [SQLSTATE 42000]
04/02/2020 09:46:00,,Error,[298] SQLServer Error: 18456<c/> Login failed for user 'xxxx\xxxxxxxx'. [SQLSTATE 28000]
04/02/2020 09:41:00,,Error,[298] SQLServer Error: 4060<c/> Cannot open database "SWPDM_CortecPDM" requested by the login. The login failed. [SQLSTATE 42000]
04/02/2020 09:41:00,,Error,[298] SQLServer Error: 18456<c/> Login failed for user 'xxxx\xxxxxxxx'. [SQLSTATE 28000]
04/02/2020 09:40:45,,Error,[298] SQLServer Error: 4060<c/> Cannot open database "SWPDM_CortecPDM" requested by the login. The login failed. [SQLSTATE 42000]
04/02/2020 09:40:45,,Error,[298] SQLServer Error: 18456<c/> Login failed for user 'xxxx\xxxxxxxx'. [SQLSTATE 28000]
04/02/2020 09:35:45,,Error,[298] SQLServer Error: 4060<c/> Cannot open database "SWPDM_CortecPDM" requested by the login. The login failed. [SQLSTATE 42000]
04/02/2020 09:35:45,,Error,[298] SQLServer Error: 18456<c/> Login failed for user 'xxxx\xxxxxxxx'. [SQLSTATE 28000]
ASKER
i am guessing you mean the windows task scheduler? i checked, nothing in there.
I also check Sql server agent Jobs, maintenance plans, Linked servers (checked on our other sql server as well).
If i add back the database (blank) the errors go away.
I even tried adding back the database and assigning it to the user, save and then unassigned it and removed the database and the errors come back.
I also check Sql server agent Jobs, maintenance plans, Linked servers (checked on our other sql server as well).
If i add back the database (blank) the errors go away.
I even tried adding back the database and assigning it to the user, save and then unassigned it and removed the database and the errors come back.
if domain/username is know and it's interactive user check his PC for installed software
In my opinion this may be related to logins in SQL Server - AD or SQL type that have this database set as "Default" and as it's gone now...therefore those errors - I'll try find my script that can show all logins bound to a specific "Default" database name.
Here try run this and see if there are any logins set to that default database name:
Here try run this and see if there are any logins set to that default database name:
select * from [master].sys.server_principals where default_database_name like '%SWPDM_CortecPDM%';ASKER
Thanks guy,
Lukasz, the logs show it to be <local machine>, so i am guessing it is coming from the local sql server.
lochan, no results from the select statement.
Lukasz, the logs show it to be <local machine>, so i am guessing it is coming from the local sql server.
lochan, no results from the select statement.
"Failed to open the explicitly specified database 'SWPDM_CortecPDM'"
Does that database even exists and is it online?
Does that database even exists and is it online?
ASKER
no sir, it was deleted over a week ago and it was only a test database that was never used.
there are actually 3 items that keep repeating every 5 min:
[298] SQLServer Error: 245, Conversion failed when converting the varchar value 'Step ' to data type int. [SQLSTATE 22018] (ConnExecuteCachableOp)
04/02/2020 09:46:15,,Error,[298] SQLServer Error: 4060<c/> Cannot open database "SWPDM_CortecPDM" requested by the login. The login failed. [SQLSTATE 42000]
04/02/2020 09:46:15,,Error,[298] SQLServer Error: 18456<c/> Login failed for user 'xxxx\xxxxxxxx'. [SQLSTATE 28000]
if i add back the database (blank) 2 of the continuous messages go away, this one keeps repeating every 5 min:
[298] SQLServer Error: 245, Conversion failed when converting the varchar value 'Step ' to data type int. [SQLSTATE 22018] (ConnExecuteCachableOp)
there are actually 3 items that keep repeating every 5 min:
[298] SQLServer Error: 245, Conversion failed when converting the varchar value 'Step ' to data type int. [SQLSTATE 22018] (ConnExecuteCachableOp)
04/02/2020 09:46:15,,Error,[298] SQLServer Error: 4060<c/> Cannot open database "SWPDM_CortecPDM" requested by the login. The login failed. [SQLSTATE 42000]
04/02/2020 09:46:15,,Error,[298] SQLServer Error: 18456<c/> Login failed for user 'xxxx\xxxxxxxx'. [SQLSTATE 28000]
if i add back the database (blank) 2 of the continuous messages go away, this one keeps repeating every 5 min:
[298] SQLServer Error: 245, Conversion failed when converting the varchar value 'Step ' to data type int. [SQLSTATE 22018] (ConnExecuteCachableOp)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ah ha, there was a replication monitor job scheduled to run every 15 seconds!
ASKER
thanks guys!
Check task scheduler on the client machine and see if there are any jobs defined that might run against that DB. Username should probably give you a clue as well.
Jim.