MS SQL: Using Profiler to Trace Failed Logins


There has been an issue with a service account triggering login errors at the frequency of once every 5 minutes. This has alerted Information Security as failed logins are aggregated and reviewed regularly. Thus, the task is to investigate and resolve this problem on the targeted node, a SQL server.


Repeated error message in text format:

Login failed for user 'KIMCONNECT\sql_readonly'. Reason: Failed to open the explicitly specified database. [CLIENT: <localmachine>]
Error: 18456, Severity: 14, State: 38

Initial Attempt at Addressing Issue:

First, grant SELECT permissions to all database objects

SELECT 'GRANT SELECT ON "' + TABLE_SCHEMA + '"."' + TABLE_NAME + '" TO "KIMCONNECT\sql_readonly"' FROM information_schema.tables


GRANT SELECT ON "dbo"."spt_fallback_db" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."spt_fallback_dev" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."AU_CommonTable_View" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."spt_fallback_usg" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."RPT_Common" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."spt_monitor" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."spt_values" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."MSreplication_options" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."_DBNames" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."_DBStatistics" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."database_size_free_space" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."disk_size" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."AU_XFR_EntityChangeSetProperty_View" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."AU_XFR_EntityChangeSet_View" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."AU_XFR_ScheduledHostTransfer_View" TO "KIMCONNECT\sql_readonly"
GRANT SELECT ON "dbo"."AU_XFR_HostTransferTransaction_View" TO "KIMCONNECT\sql_readonly"

Second, grant SELECT permissions toward a specified database




Output if the granter doesn’t have permissions

Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'TESTDB', because it does not exist or you do not have permission.

Expected outcome if grantor has the appropriate permissions

Command(s) completed successfully
Further Issues:

After those SQL statements have been issued, the problem still persists. Further investigation shows that the targeted databases have been in recovery pending statuses.

New Error messages:

[298] SQL Server Error: 40060. Cannot open database 'TESTDB" requested by the login. [SQLSTAGE 42000]
[298] SQL Server Error: 18456, Login failed for user 'KIMCONNECT\sql_readonly'.[SQLSTATE 28000]
Result of Attempts:

It appears that service accounts automatically log into databases to perform routine maintenance tasks, such as optimizing indexes and querying performance stats. If the databases are in pending recovery mode, these login attempts will fail. The fix is to kick the problematic databases out of recovery mode or set them as offline and to add “public” access to all databases of the service account. Afterward, we can verify that 5-minute interval errors have ceased… NOT.

Next, Add User Mapping:

Connect to SQL Server using SSMS > navigate to the Security > Logins > right-click on the account > Properties > select the “User Mapping” tab > put a check mark to each database that this service account should have access > ensure that there is a check market next to “public” access option > OK

Next, Run Profiler:

Start > All Programs > Microsoft SQL server > Performance Tools > SQL Server Profiler > New Trace > Connect to SQL Server Instance > Give the trace a name such as Failed Logins

Uncheck everything > Put check marks next to Show all events & Show all columns > Click on Security Audit category > put a check mark next to Audit Login Failed > Optional: select only Application Name & Text Data columns > click Run

Observe the first match(es)

Right-click an Event > Pause Trace > Expand Application Name, Database Name & Text Data

According the screen-shot above, one can interpret that the database name “Master” is having a a login issue by the readonly-sql service account. The application is “SQLAgent – TSQL JobStep (Job 0x262EBA2E6B57.. : Step 1)”. Once this is identified, it’s would be necessary to modify permissions of the database named “master” using one of these methods:

To grant read permissions, add the default db_datareader role (read permission on all tables) to that account.

USE master
EXEC sp_addrolemember N'db_datareader', N"KIMCONNECT\sql_readonly"

There’s also a db_datawriter role – which gives your user all WRITE permissions (INSERT, UPDATE, DELETE) on all tables:

USE master
EXEC sp_addrolemember N'db_datareader', N"KIMCONNECT\sql_readwrite"
EXEC sp_addrolemember N'db_datawriter', N"KIMCONNECT\sql_readwrite"

One Liner version


The attempts above have only assisted us at getting closer to the fix. It appears that issue has to do with a SQL job as indicated in the “SQLAgent – TSQL JobStep (Job 0x2628EBA2E6B57A409BB4D3689BE03287 : Step 1)” of the ApplicationName error being produced by the SQL Server Profiler.

Run this command to convert Job ID to Job Name

SELECT name FROM msdb.dbo.sysjobs WHERE CONVERT(binary(16), job_id)=0x2628EBA2E6B57A409BB4D3689BE03287

Once the culprit has been identified, pausing that job correlates to the ceasing of the failed login events.

Leave a Reply

Your email address will not be published. Required fields are marked *