0

How can I disable the windows authentication mode of SQL Server and restrict it to ask for username and password every time, with no windows authentication?

2
  • 1
    Probably not the best idea. – Dave Markle May 13 '10 at 10:20
  • This should be on Serverfault.com – David Stratton May 13 '10 at 10:20
7

It's not possible to disable windows authentication mode. SQL Server suports only two modes:

  1. Windows authentication only
  2. Mixed mode (windows authentication and SQL Server authentication)

You can limit access via windows authentication by restricting (or removing) the permissions of the windows users/groups with logins in SQL Server.

1

As the other posters say you cannot remove windows authentication mode. You can however remove all windows account logins from the server instance and then you acheive the same end, nobody can log in using integrated windows authentication.

1
  • I wouldn't do that windows accounts are sysadmin-level logins that need to be available for SQL Server to function correctly. For example deleting NT SERVICE\SQLSERVERAGENT will prevent SQL Agent from starting. Instead its better to revoke access from windows logins. – tangobee Sep 14 '14 at 12:25
1

Windows authentication is the recommended way to access SQL Server. Hence, what you're trying to do is go with the insecure way to login. SQL authentication allows for passing SQL passwords in plain view. Your passwords are not encrypted.

0

I don't believe this is possible. The best that you can do is to remove any unwanted Windows accounts from the permissioning.

0

To do what others have explained above, run the following steps

  1. Login Using SQL Server Authentication mode,
  2. Run the following script

    USE [master] GO ALTER LOGIN [DomainName\Login] DISABLE GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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