SQL Server Authentication Modes


One of the things we usually configure and don’t think too much about is the authentication modes available in SQL Server. Authentication is the process of verifying a user is authorized based on a username and password provided during the login process. SQL Server uses two modes of authentication: Windows Authentication and SQL Server Authentication.

Windows Authentication

When you configure the SQL Server instance to use Windows Authentication mode, you are using Windows usernames and passwords. When you attempt to log into the instance, you are required to provide a valid Windows (usually Active Directory) username and the correct password, then SQL Server will validate the credentials passed to allow access, but also use SQL Server security settings to determine if you are allowed access to the database and objects selected.

Many people prefer to use Windows Authentication because they don’t have to remember separate username and password combinations to access the database. Making it easy for users to create and maintain complex passwords will enhance your overall security.

SQL Server Authentication

When you use SQL Server Authentication, you must provide a username and password already configured in the database instance. Many commercial database applications use this authentication mode to provide access to their third-party database. You can have multiple user accounts, with various levels of permissions.

Using SQL Server Authentication is best for older applications that require backwards compatibility, and for non-windows systems that must access your database.

Mixed-Mode Authentication

This mode simply allows the server to use both SQL Server and Windows Authentication modes.

You can learn more about database authentication and security here.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s