SQL Authentication with no Windows authentication 1

[JBaileys]

I have noticed during server install, there is an option for windows authentication and mixed mode. Is there a way just to turn on sql authentication or would I have to remove windows administrators having rights as sa.

 

[SQLBill]

You can't turn on just SQL Server authentication. Why not - well Microsoft would rather we only used Windows Authentication as it's more secure.

You can delete the group Builtin/Administrators from the logins. Then the only way to use Windows Authentication would be to set that on the individual login (Security>Logins). However, be careful with deleting this login. Let's say you use Windows Authentication and you are also a sysadmin. When you remove that login, you will be denied access (even if you have a separate login).

-SQLBill

 

[mrdenny]

Also be carefull when using full text indexing when removing the BUILTIN\Administrators. The full text indexing will continue to run after you remove it, until the next time you restart the SQL Service at which point full text indexing will fail with some very strance error messages. At that point you have to add an account for "NT SYSTEM\Local Authority" (I think) into the SQL Server.

Denny

--Anything is possible. All it takes is a little research. (Me)