Story:
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.
Symptoms:
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
Output
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
GRANT SELECT ON "TESTDB" TO "KIMCONNECT\sql_readonly"
Alternative
GRANT SELECT ON "dbo"."TESTDB" TO "KIMCONNECT\sql_readonly"
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
GO
EXEC sp_addrolemember N'db_datareader', N"KIMCONNECT\sql_readonly"
GO
There’s also a db_datawriter role – which gives your user all WRITE permissions (INSERT, UPDATE, DELETE) on all tables:
USE master
GO
EXEC sp_addrolemember N'db_datareader', N"KIMCONNECT\sql_readwrite"
EXEC sp_addrolemember N'db_datawriter', N"KIMCONNECT\sql_readwrite"
GO
One Liner version
GRANT CONNECT, SELECT, INSERT, UPDATE, CONTROL, EXECUTE, ALTER, ALL ON DATABASE::master TO "KIMCONNECT\sql_readwrite"
GO
Solution:
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.