Can Windows Authentication logins be disabled on SQL Server 2008 (or R2)?
As far as I'm aware it is not possible to disable Windows authentication on SQL Server 2008 + R2:
http://msdn.microsoft.com/en-us/library/ms144284%28v=sql.105%29.aspx
The only Windows accounts that will have access are those that are explicitly added as logins (or are members of a group which has a login).
It cannot be disabled completely, for two reasons:
On install, logins are provisioned for
NT AUTHORITY\SYSTEM
,NT SERVICE\SQLSERVERAGENT
(or a group containing the SQL Agent service account), andNT SERVICE\MSSQLSERVER
(or a group containing the SQL database engine service account). These aresysadmin
-level logins that need to be available for SQL Server to function correctly.While a quick test revealed that deleting all three of those logins only prevented SQL Agent from restarting (the database engine came up fine), I'm sure there are other functions that rely on the other two logins... they were created by default for a reason, so I wouldn't mess around with them. (FYI if you test this yourself: the Drop & Create scripting option for a login in SSMS doesn't script server role membership.)
In single-user mode, local administrators are automatically granted
sysadmin
-level privileges regardless of whether or not there is a login created that "contains" those users. This is a coat hanger for when you've locked your keys in the car.
As mentioned in the other answer, only explicitly-created Windows logins will have access to connect (my original comment was incorrect) -- removing all user-created Windows logins is sufficient to prevent access.
If you need to go a step farther and prevent Windows logins from being created, here's a starting point (Policy-Based Management, at least on 2008, doesn't support preventing this as it happens):
CREATE TRIGGER trg_PreventWindowsLogins
ON ALL SERVER
AFTER CREATE_LOGIN
AS
BEGIN
SET NOCOUNT ON;
IF (EVENTDATA().exist('/EVENT_INSTANCE[1]/LoginType[1]/text()[1] eq "Windows (NT) Login"') = 1)
BEGIN
RAISERROR(N'Not allowed to create Windows logins!', 16, 1);
ROLLBACK;
END
END
Of course, anyone with enough permissions could defeat this, but that's a separate issue...