About SQL Server Authentication

SQL Server Authentication

SQL Server authentication was the original authentication method supported by SQL Server when it was based on the Sybase code base. With SQL Server authentication, the application or the user specifies the username and password to be used to authenticate against the SQL Server instance. When specified, the username and password are put into the connection string, which the application then uses when it connects to the SQL Server instance. With SQL Server authentication, the actual username and password are stored in the master database within the database instance.

When you use SQL Server authentication, the account and password are passed to the database instance, which then hashes the password and compares the username and password hash against the list of SQL accounts stored within the master database. If the passed-in username and password hash match an account stored within the master database, authentication succeeds, you can connect, and the rights associated with the SQL account are granted to you. If no match is found, an authentication error is returned.

SQL Server logins can be configured to force them to follow the Windows Active Directory security policies. For the domain policies to be enforced the Windows domain must be a Windows 2003 or higher and the SQL Server must be installed on a Windows 2003 or higher version of the Windows operating system. Care should be taken when planning your SQL Server installations as SQL Server 2012 is the first version of Microsoft SQL Server which requires that it be installed on Windows Server 2008 or higher. There are two domain policy settings which can be enabled. The first is to follow the domain password policies that control password complexity. The second is to enforce password expiration.

Setting the password policies can be done via SQL Server Management Studio by editing the login and checking or unchecking the needed policies. The settings can also be enabled or disabled by using the ALTER LOGIN statement as shown in the following code snippet:

ALTER LOGIN chain_test

WITH CHECK_POLICY = ON, CHECK_EXPIRATION=ON

The domain policies are only verified when the account is created, or when the password is changed. This means that a SQL login could be created with a password that doesn’t meet the domain policy requirements. Then after it is created the check policy setting could be enabled and it would be assumed that the account met the domain policy requirements when in fact it doesn’t.

SQL Server 2012 introduces a new concept called the contained user, which is used within contained databases. A contained user exists only within a contained database and the password for the contained user exists within the database and not within the master database. As the contained user exists only within the database, no instance level rights can be granted to a contained user, and the contained user can only access objects within the database in which the contained user exists. Contained users are created by using the CREATE USER statement which is shown in the following code snippet by specifying the password parameter instead of the FROM LOGIN parameter.

CREATE USER MyContainedUser WITH PASSWORD=’MySecurePassword’

GO

Within the context of the database in which the contained user is created, rights to objects and permission chains all work in exactly the same process as a traditional or non-contained user.

Contained users can be created based on local or domain Windows accounts, or they can be created as SQL Server users. A contained Windows user is simply a Windows account that doesn’t have a corresponding login at the server level. Contained SQL users do not have the option of being configured to follow domain policies like traditional SQL logins.