SQL Server: disable Windows authentication

Actually, this is not completely possible.

There are two authentication modes for SQL Server (2008R2):

  1. Windows Authentication: stronger security, Kerberos protocol.
  2. Mixed Mode (Windows and SQL Server): use of “sa” login. Less secure. Best option for web applications.

I’ve got a web server (IIS) andwhen I log into Windows (2008R2), if I open SQL Server, Windows Authentication is selected by default and it lets me access without any password.

This is because, by default, the Windows administrators group is assigned the “sysadmin” server role. We can remove them from this server role or remove completely the group from the SQL Server logins.

So, I did the following:

  1. Access SQL Server as “sa” user.
  2. Go to Security/Logins
  3. Right click on the BUILTIN\Administrators login or PC-NAME\USER-NAME login.
  4. Choose Properties
  5. Go to Server Roles
  6. Uncheck “sysadmin” role
  7. Make sure it has only the “public” role selected.
  8. Click OK

This way, Windows administrator can access SQL Server but without permissions to modify anything.

However, “sa” user can still access SQL Server with full privileges.

Of course, it’s not recommended to use “sa” login for any application (ASP.Net).

You better create a specific login for each application, with just the needed permissions to access its database.

References:

How to Disable Windows Authentication

Authentication in SQL Server (ADO.NET)

SQL SERVER – Disable Windows Authentication – Remove Windows Authentication Login Account

 

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: