SQL Server 2005 authentication methods

SQL Server 2005 must authenticate a user before the client is allowed to perform any activities within the database. In this article we will see the various options for user authentication.
how sql server authenticates its users

In the book titled Develop An Accounting Package using VB, we have discussed how exactly you should use authentication methods and when to use the various modes. The discussion also includes how to configure the options.

SQL Server security is integrated with Windows domain security. This enables authentication based on user and group memberships. This authentication technique make it much easier because of the familiarity we have with windows authentication.

There are two types of authentication:

1. Windows authentication

Use authentication based on Windows domain accounts only. In this case only users with a domain account of Windows can access the server.

2. SQL Server and Windows authentication mode

When we combine Windows and SQL Server authentication, users in Windows domains can access the server using a single account and users with SQL Server logins can be logged.

Authentication process of a Windows login

After the user has logged in to the domain using either Kerberos (this is the preferred authentication protocol for a windows domain) and NTLM (Windows NT lan manager authentication) the client can request access to a specific SQL Server. The client sends identification to the server and based on the server's list of users (stored in the sysxlogins table on the master database), the user is granted or denied access to SQL Server.

Configuring Authentication

We can configure authentication and auditing options with the Security page of the Server Properties dialog box. The security page can be accessed in the left pane of the Server Properties dialog box.

Setting Authentication Mode

As mentioned earlier SQL Server security is integrated with Windows domain security. This allows for authentication based on user and group memberships. To use combined authentication, select the SQL Sever and Windows Authentication mode option button. In this option users in Windows domains can access the server using a domain account, and other users can be logged on using a SQL Sever logon ID.

In the Express Edition Windows authentication is not available.

To use domain authentication only, select the Windows Authentication Mode option button. In this option only users with a domain account can access the server.

With combined authentication, SQL Sever first checks to see if a new login is a SQL Server login, if the login exists, SQL Server then uses the password provided to authenticate the user. If the login does not exist, it uses Windows authentication.

Setting Auditing Level

Auditing allows tracking user access to SQL Server. You can use auditing with both authentication modes as well as with trusted and untrusted connections.

When auditing is enabled, user logins are recorded in the Windows application log, the SQL Server error log, or both, depending on how you configure logging for SQL Server. The available auditing options include:
  1. None
  2. Disables auditing
  3. Failed Logins Only Audits only failed login attempts (the default setting)
  4. Successful Logins Only Audits only successful login attempts
  5. Both Failed And Successful Logins Audits both successful and failed login attempts

Windows vs SQLServer Authentication

Windows authentication is generally preferred because it provides an optimal level of integration with Windows 2000. User and group accounts from Windows are granted or denied access to SQL Server. Windows 2000 authenticates the user when the user logs on to the network. Because the password is authenticated at network login, SQL Server does not need to know or verify the password of a user. Windows Authentication provides the following advantages over SQL Server Authentication.

  1. Windows Authentication can grant group accounts access SQL Server, thus minimizing the over head of login administration
  2. Users are authenticated by Windows 2000, resulting in a secure authentication over a network
  3. Users could be able to use the same user credentials for network and database access
  4. Users could be able to use the same user credentials for network and database access
  5. Audited events can be tracked to a network user.

Understanding the SQL Authentication Mode

SQL Server Authentication is preferred in the following scenarios.

  1. The user is not logging in to a Windows domain
  2. Your network does not have a Windows 2000 domain
  3. It is not feasible to manage all Internet users on the windows domain
  4. You prefer to manage them separately from your normal Windows domain administration
  5. An application is acquired from a vendor that requires SQL Server

cover image of azure cloud book

Develop Azure Applications

Azure Cloud is gaining popularity and is competing with Amazon Elastic Cloud and Google Cloud for a spot in the billion dollar Cloud computing space.

With this book learn how to use - Table (an Azure Table is different from a SQL Server Table), Queue, Blob storage services, use your local machine to create an Application and a Service, how Azure splits a Table to achieve Load Balancing, Scalability based on the Partition Key, AppFabric Service Bus, Access Control Service, role of Fabric Controller, features of Azure SQL Database and Content Delivery Network.

Many have mastered Cloud Programming and are enjoying the benefits. Why not you?

View Highlights » View Contents »
cover image of foundation database programming book

Create your Own Accounting Software

A foundation book to Master Accounting Software Development, VB.Net, C# and OOPs programming. Detailed explanation of the Accounting domain for programmers is included. SQL and ADO.Net is also covered in detail.

This book gives you a strong foundation in Application Development.

A thorough understanding of the contents will enable you to grasp the topics covered in the accompanying books quite easily. This is because, the accounting domain is dealt with great detail in this book.

The ideal book to get started with application development in the .Net world.

View Highlights » View Contents »

cover image of asp.net book

Create your Own WEB ERP Software using ASP.Net

This book opens you to the Web. Using ASP.Net, C# and SQL Server as the tools, you will learn to build a Multi Tier Web Application as recommended by Microsoft. You will learn implementing inline GridView control, Transaction Management for a layered Web Application, build Data Access component and a Business Layer component.

Opens you to a world of opportunities. You will be able to develop a Web ERP application including all the five modules and learn how to use the TreeView control and GridView control to implement transactions such as Purchase Invoice, Sales Order and Sales Invoice.

View Highlights » View Contents »
cover image of entity framework book

ASP.Net MVC 5 Application Development Using Entity Framework 6 and VS 2015

Entity frameworks is the latest buzz in .Net programming and is the data access technology for Microsoft OS based hand held devices and web applications. Learn to integrate EF into an ASP.Net Application and MVC application. Create, explore and query the Entity Data Model. Learn how to use ObjectDataSource control and GridView control in an ASP.Net Application.

Also, learn to build Three-Tier ASP.Net application, create a business-class, implement methods in the class to perform CRUD operations and bind a GridView to the ObjectDataSource control. Learn how to handle concurrency and managing Transactions.

View Contents »