SQLServerWiki

“The Only Thing That Is Constant Is Change”

Gone in 29 minutes!

Posted by database-wiki on October 4, 2011

Problem Description
================

Customer has a requirement as per his auditing policies the he cannot keep N’BUILTIN\ADMINISTRATORS’ group
in sql server. Also another requirement for the customer would be that all the jobs would be owned
by N’NT AUTHORITY\SYSTEM’.

So customer has removed N’BUILTIN\ADMINISTRATORS’ Group from sql server and he has specifically assigned
sysadmin privileges for N’NT AUTHORITY\SYSTEM’

After the changes are done whenever customer tries to execute the jobs that are owned by N’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 N’BUILTIN\ADMINISTRATORS’, Once we cross 29min the job starts to fail.
Running a Profiler Trace in Background relveled 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 when ever we have deleted N’BUILTIN\ADMINISTRATORS’

When ever 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 N’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 N’BUILTIN\ADMINISTRATORS’ back to sql server,Our test has
also reveled because if we add N’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()

RESOLUTION
==========
Changes the owner of the job other than  ‘NT AUTHORITY\SYSTEM’ or do not remove ‘BUILTIN\Administrators’
couple of things on this.

There is Connect Bug : http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=387521
which was opened for the same problem and development team has closed this as By Design.

http://support.microsoft.com/kb/263712  KB Article talks about Removing the BUILTIN\Administrators login from SQL Server. Since we have already documented that we can remove BUILTIN\Administrators in KB http://support.microsoft.com/kb/263712  this should be considered as a Bug instead of BY DESIGN BEHAVIOR. We check with the Escalation services and they said this bug is not going to be fixed.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: