Sql-server – BUILTIN\Administrators allowing access via linked server but not locally

kerberoslinked-serverSecuritysql serversql-server-2008-r2

We have two servers:

LYNDB01 & LYNDB02

On LYNDB01 we have a database called db_ExampleDatabase

We have created a Linked Server on LYNDB02 to LYNDB01 using no mappings and the "Be made using the login's current security context" option for authentication:

EXEC master.dbo.sp_addlinkedserver @server = N'LYNDB01', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LYNDB01',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

We have a windows user on LYNDB02 for DOMAIN\USER1 – this windows user does not have a login on LYNDB01 at all but is a domain admin and thus is a member of BUILTIN\Administrators on both LYNDB01 & LYNDB02

I have created a view in the LYNDB01.db_ExampleDatabase database to confirm what user is running the transaction:

CREATE VIEW [dbo].[vw_CurrentUser]
AS
    SELECT  SYSTEM_USER AS CurrentUser

For testing we gave DOMAIN\USER1 RDP access to LYNDB01 & LYNDB02.

If we RDP to LYNDB01 as DOMAIN\USER1 and open up SSMS with windows authentication – we get a logon error as DOMAIN\USER1 does not have a login on LYNDB01, so that's fine.

If we RDP to LYNDB02 as DOMAIN\USER1 and open up SSMS with windows authentication it connects OK (Which is fine as DOMAIN\USER1 has a login on LYNDB02). Here is our problem, we can access LYNDB01.db_ExampleDatabase.dbo.vw_CurrentUser via the Linked Server!

SELECT * FROM LYNDB01.db_ExampleDatabase.dbo.vw_CurrentUser

How is this possible when LYNDB01 does not have a login on LYNDB01 (Which is confirmed by the error we get when connecting locally!)

And yes, the remote query confirms that DOMAIN\USER1 is the current user??

VERY confused?!

Edit: Theroy 1

DOMAIN\USER1 is a domain admin and is therefore a local admin on both LYNDB01 & LYNDB02 – could this be causing it? if so, why would i get access denied on LYNDB01 as USER1 has no logon but work via a linked server?

OK, if i delete BUILTIN\Administrators logon from LYNDB01 this problem goes away and the query from LYNDB02 fails – why is BUILTIN\Administrators letting USER1 authenticate remotely via a linked server but not locally & what can i do about it?

Best Answer

This is expected. LUA strips administrators membership from your token. From Getting Started with User Account Control on Windows Vista:

To help prevent malware silent installation and computer-wide infection, Microsoft developed the UAC feature for Windows Vista. Unlike previous versions of Windows, when an administrator logs on to a Windows Vista computer, the user’s full administrator access token is split into two access tokens: a full administrator access token and a standard user access token. During the logon process, authorization and access control components that identify an administrator are removed or disabled, resulting in a standard user access token. The standard user access token is then used to launch the desktop, the Explorer.exe process. Because all applications inherit their access control data from the initial launch of the desktop, they all run as a standard user as well. Contrasting with this process, when a standard user logs on, only a standard user access token is created. This standard user access token is then used to launch the desktop.

Since your local desktop token has the administrator membership removed, it cannot log in locally to SQL Server if you expect to log in through builtin\Administrators. On remote access your token preserves the Administrator membership and you gain access.

The solution is to grant SQL Server login rights through an explicit domain group account and add all users that require access to your SQL instance to this domain group. Do not rely on Administrators membership, is a bad practice.

Related Question