Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com May 13 '10 at 21:51

This question came from our site for professional and enthusiast programmers.

    
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

5 Answers 5

up vote 7 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
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 at 12:25

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

share|improve this answer

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.

share|improve this answer

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

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.