Home Dashboard Directory Help
Search

sp_sqlagent_has_server_access returns incorrect results when BUILTIN\Administrators are removed by CR8N


Status: 

Closed
 as By Design Help for as By Design


0
0
Sign in
to vote
Type: Bug
ID: 387521
Opened: 12/10/2008 4:54:24 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

Hopefully I am not going crazy here, but I have removed the BUILTIN\Administrators login and explicitly added NT AUTHORITY\SYSTEM as a Login with the sysadmin role.

My SQL Server Agent service is running as localsystem.

I noticed my jobs that were owned by NT AUTHORITY\SYSTEM were failed with an error of: The owner (NT AUTHORITY\SYSTEM) of job <job name> does not have server access.

I tracked it down to the stored procedure msdb.dbo.sp_sqlagent_has_server_access which has a special case for NT AUTHORITY\SYSTEM in the code:

IF (UPPER(@login_name collate SQL_Latin1_General_CP1_CS_AS) = N'NT AUTHORITY\SYSTEM')
    BEGIN
     IF (EXISTS (SELECT *
                 FROM master.dbo.syslogins
                 WHERE (UPPER(loginname collate SQL_Latin1_General_CP1_CS_AS) = N'BUILTIN\ADMINISTRATORS')))
     BEGIN
        SELECT @has_server_access = hasaccess,
             @is_sysadmin = sysadmin,
             @actual_login_name = loginname
        FROM master.dbo.syslogins
        WHERE (UPPER(loginname collate SQL_Latin1_General_CP1_CS_AS) = N'BUILTIN\ADMINISTRATORS')
     END
    END


This
Details
Sign in to post a comment.
Posted by Robert Spinelli on 3/31/2009 at 12:07 PM
Here is the info I got from MS in regards to this:

We further investigated why sysadmin privileged 'NT AUTHORITY\SYSTEM' was not getting server access.

Problem Description
===================
requirement as per our auditing policies that we cannot keep 'BUILTIN\ADMINISTRATORS' group in SQL server. Also another requirement for the ours would be that all the jobs would be owned
by N'NT AUTHORITY\SYSTEM'.

We have removed 'BUILTIN\ADMINISTRATORS' Group from sql server and we also specifically assigned sysadmin privileges for N'NT AUTHORITY\SYSTEM'

After the changes are done whenever we try to execute the jobs that are owned by 'NT AUTHORITY\SYSTEM' or when it runs as per the schedule the Jobs fail with the Below Error Message.

Error Message
=============
The owner (NT AUTHORITY\SYSTEM) of job after remove does not have server access.

Research At our End
===================
We were able to reproduce the problem in-house and in our research we have found that the jobs run fine for 29min after we remove 'BUILTIN\ADMINISTRATORS', Once we cross 29min the job starts to fail.

Running a Profiler Trace in Background reveled the below findings.

We internally execute sp_sqlagent_has_server_access, This is the Exact statement

EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name = N'NT AUTHORITY\SYSTEM'

Looking at the stored procedure found the reason why we hit into this problem whenever we have deleted 'BUILTIN\ADMINISTRATORS'

Whenever this stored procedure is executed we delete entry from syscachedcredentials table where DATEDIFF(MINUTE, cachedate, GETDATE()) >= 29 which means we delete data older than 29 minutes.

Later we set specific values to some variables intentionally

-- Set defaults
SELECT @has_server_access = 0
SELECT @is_sysadmin = 0
SELECT @actual_login_name = FORMATMESSAGE(14205)

FORMATMESSAGE(14205) will make the @actual_login_name varaiable hold the value " (unknown) "

Later there is a logical check in the stored procedure

-- update the cache only if something is found

IF (UPPER(@actual_login_name collate SQL_Latin1_General_CP1_CS_AS) <> '(UNKNOWN)')

Inside this IF condition we update syscachedcredentials if data exists else we insert data into the syscachedcredentials.We dont enter this logical code since we have another check condition above this code for 'NT AUTHORITY\SYSTEM' and due to that we follow a different code path and never insert or update data into the table resulting in the above error message since the stored procedure returns the below data eventhough NT AUTHORITY\SYSTEM is a sysadmin on the sql server instance.

has_server_access is_sysadmin actual_login_name
----------------- ----------- ------------------
0                 0         (unknown)

The only way to resolve the issue would be to add 'BUILTIN\ADMINISTRATORS' back to sql server,Our test has also reveled because if we add 'BUILTIN\ADMINISTRATORS' and then later remove it ,The job would only execute
fine for next 29min. The moment we pass on this time line job starts to fail.

Also if we manually add data into the table it would start working fine for next 29min

insert into syscachedcredentials SELECT N'NT AUTHORITY\SYSTEM',1,1,GETDATE()

Next Action Plans
=================
1) add back builtin\administrators. but according to our corporate policy I don’t think that’s possible.
2) add an entry every a few minutes in msdb.dbo.syscachedcredentials. since this is our system table, we shouldn't do this without further investigation.
3) use a different user account for owner.

So its a bug in my opinion. MS did say this is fixed in SQL2008.

I ended up just using SA for the job owner for all the maint tasks instead of nt authority\system.
Posted by Microsoft on 1/6/2009 at 10:32 AM
Thanks for the bug. We will look into it.

Gil
Sign in to post a workaround.