SQL Server security model

SQL Server security model

In order to access data from a database, a user must pass through two stages of authentication. first one at the SQL Server level and the other at the database level. These two stages are implemented using Logins names and User accounts respectively.

A valid login is required to connect to SQL Server and a valid user account is required to access a database.
How to Login: A valid login name is required to connect to an SQL Server. A login could be:

• A Windows NT/2000 login that has been granted access to SQL Server
• An SQL Server login, that is maintained within SQL Server
These login names are maintained within the master database. So, it is needed to backup the master database after adding new logins to SQL Server to the machine.

User: A valid user account within a database is required to access that database. User accounts are specific to a database. All permissions and ownership of objects in the database are controlled by the user account. SQL Server logins are associated with these user accounts. A login can have associated users in different databases, but only one user per database.
During a new connection request, SQL Server verifies the login name supplied, to make sure, that login is authorized to access SQL Server. This verification is called Authentication. SQL Server supports two authentication modes:

• Windows authentication mode: With Windows authentication, you do not have to specify a login name and password, to connect to SQL Server. Instead, your access to SQL Server is controlled by your Windows NT/2000 account (or the group to which your account belongs to), that you used to login to the Windows operating system on the client computer/workstation. A DBA must first specify to SQL Server, all the Microsoft Windows NT/2000 accounts or groups that can connect to SQL Server

• Mixed mode: Mixed mode allows users to connect using Windows authentication or SQL Server authentication. Your DBA must first create valid SQL Server login accounts and passwords. These are not related to your Microsoft Windows NT/2000 accounts. With this authentication mode, you must supply the SQL Server login and password when you connect to SQL Server. If you do not specify SQL Server login name and password, or request Windows Authentication, you will be authenticated using Windows Authentication.
Point to note is that, whatever mode you configure your SQL Server to use, you can always login using Windows authentication.

Windows authentication is the recommended security mode, as it is more secure and you don't have to send login names and passwords over the network. You should avoid mixed mode, unless you have a non-Windows NT/2000 environment or when your SQL Server is installed on Windows 95/98 or for backward compatibility with your existing applications.