LearnNowOnline

Blog

Understanding Logins with SQL Server

Excerpt by Don Kiely | May 28, 2013

SQL Server logins are not part of Windows-they are saved in and managed by SQL Server. A user who connects to SQL Server via a SQL Server login is prompted for a login name and password. If you select the Windows Only mode of authentication, all SQL Server logins will be disabled and users will be able to connect only by using their Windows logins.

Mixed mode with SQL Server logins is much more flexible-for example, it supports users on Windows 9x computers-but it is also less secure. SQL Server logins and passwords are saved in the system tables in SQL Server, which are file-based. Users who gain access to these files could conceivably hack administrative logins and passwords.

If you configure your SQL Server to support SQL Server logins, there is one built-in SQL Server login that you need to watch out for-the sa login.

Beware of the sa Login

You may have noticed a login named "sa" hanging around with the other logins in the Logins node in Object Explorer. The sa, or system administrator, login is included mainly for backward compatibility with older versions of SQL Server. The sa account is mapped to the sysadmin fixed server role, and anyone who uses sa is a full system administrator, with irrevocable rights over the entire SQL Server instance and all the databases in it.

You can't modify or delete the sa login. If you select Mixed Mode authentication when you install SQL Server, you're prompted for a password for the sa user. Unless you set a password, anyone can log in as sa with no password, and play "let's administer the server." Needless to say, this is the last thing you want your users doing. Use the sa login only as a backdoor if other system administrators are unavailable or have forgotten their Windows passwords. If that happens, you probably need new admins!

WARNING! Never, EVER, use the sa login for access to a database in an application. Doing so could give a hacker administration-level control over your database server if the hacker is able to get control of the application. This has been an easy way to attack servers and is a horrible practice. Instead, either set up a custom Windows or SQL Server login for the application to use, and give that login the absolute minimum permissions necessary to run the application (which is another application of the principle of least privilege).

Password Policy and Enforcement

In versions of SQL Server before 2005, there was no easy way for a system administrator to enforce password policies that could help make a system more secure. For example, SQL Server had no way to force users to create strong passwords of a minimum length and a mix of alphanumeric and other characters. If someone wanted to create a login with a single letter for a password, you couldn't configure SQL Server to prevent it. Likewise, there was no way to cause passwords to expire on a regular basis, such as every three months. Some people rightly saw this as a major reason not to use SQL Server logins.

SQL Server now can hook into the password policies of Windows Server 2003, Windows Vista, or later versions. The passwords are still stored in SQL Server, but SQL Server makes a call into the NetValidatePasswordPolicy() Windows API method, which was first introduced in Windows Server 2003. This API function applies the Windows password policy to SQL Server logins and returns a value that indicates whether the password is valid. SQL Server calls this function when a user creates, sets, or resets a password.

This post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals course written by expert Don Kiely.