Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

So we can do Windows Login or Mixed Mode, but can we configure SQL Server to only use internal logins and to block all Windows Logins?

Is the only solution to add all potential Windows logins and set them to restricted privileges as a preventative (or reactive) procedure?

share|improve this question

2 Answers 2

up vote 5 down vote accepted

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), and NT SERVICE\MSSQLSERVER (or a group containing the SQL database engine service account). These are sysadmin-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...

share|improve this answer

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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.