Windows Authentication vs. SQL Server Authentication

Windows Authentication is for the environment that all of your users are part of a Windows domain (have a Windows NT/2000 user account or is a member of a group). Your access to SQL Server is controlled by your Windows NT/2000 account or group, which is authenticated when you log on to the Windows operating system on the client.

If you have a mixed environment (e.g. Windows and Novell), then you have to use SQL Server Authentication where you will have to create a Login ID and Password in SQL Server.

Don’t be confused with “Authentication” and “Authorization.” The “Authentication” is dealing with user’s connection to SQL Server and the “Authorization” is dealing with what data privilege a user has once he is connected to the SQL Server.

Secondly, don’t be confused with “Authentication,”(think of it as a “method”), and “Authentication Mode.” There are two kinds of authentication – Windows authentication and SQL Server authentication. And there are two kinds of authentication mode – Windows authentication mode and mixed authentication

The Windows authentication mode only allows you to login/connect to SQL Server with Windows authentication. The mixed authentication mode allows you to login/connect to SQL Server with either Windows authentication or SQL Server authentication.

Again, if your environment is pure Windows, then you should use Windows authentication mode, which uses Windows authentication to login/connect to SQL Server. If your environment is not pure Windows, like Novell and Windows, then you have to use mixed authentication mode, which will prompt you, during connection, to choose either Windows authentication or SQL Server authentication to connect to SQL Server.

If you are a member of Windows’ administrator group then, it doesn’t matter if you are using Windows authentication mode or mixed authentication mode, you should be able to use Windows Authentication to connect to SQL Server. However, what you can do after the connection depends on what you have been authorized.

Disadvantages of SQL Server Authentication

  • If a user is a Windows domain user who has a login and password for Windows, he must still provide another (SQL Server) login and password to connect. Keeping track of multiple names and passwords is difficult for many users. Having to provide SQL Server credentials every time that one connects to the database can be annoying.
  • SQL Server Authentication cannot use Kerberos security protocol.
  • Windows offers additional password policies that are not available for SQL Server logins.

Advantages of SQL Server Authentication

  • Allows SQL Server to support older applications and applications provided by third parties that require SQL Server Authentication.
  • Allows SQL Server to support environments with mixed operating systems, where all users are not authenticated by a Windows domain.
  • Allows users to connect from unknown or untrusted domains. For instance, an application where established customers connect with assigned SQL Server logins to receive the status of their orders.
  • Allows SQL Server to support Web-based applications where users create their own identities.
  • Allows software developers to distribute their applications by using a complex permission hierarchy based on known, preset SQL Server logins.

4 responses to “Windows Authentication vs. SQL Server Authentication

  1. Informative post, this post has created eagerness to go your through upcoming posts. Keep up good work.

  2. This Stuff Really Helps to me .good work bro keep it up 🙂

  3. I would add to the list of advantages of SQL Server Logins:
    Everything stays within SQL, and so if you are migrating to a new server, you can script the logins and migrate them easily, basically makes migration abit simpler, rather than having to migrate windows users from Active Directory as well as SQL Logins.

  4. Wow, this paragraph is pleasant, my sister is analyzing these things, so I am going to tell her.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s