To use SQL Server, you must be authenticated within SQL Server and then authorized to execute any T-SQL statement that you pass through the connection. Authentication is done using the connection string information when you open the connection. Authorization is done when you execute a command on the connection. This section provides a brief overview of authentication and authorization in SQL Server.
You can configure SQL Server to provide authentication by using its own security system (SQL Server authentication) and/or by using integrated Windows authentication. In SQL Server, authentication uses login accounts. If you match a login, you can get into SQL Server. However, you still have no permissions until you have been authorized to access a resource.
When SQL Server is configured for SQL Server authentication, you can create login accounts in SQL Server. A login consists of a user name and a password, which are maintained by the SQL Server administrator. You can turn on SQL Server authentication by editing the SQL Server security properties, as shown in Figure 13-13. Notice that this option, Mixed Mode, does not turn off integrated Windows authentication, because integrated Windows authentication cannot be turned off. The Mixed Mode option requires a restart of SQL Server before the change takes effect.
Figure 13-13: Setting SQL Server to use SQL Server authentication, which still uses Windows authentication
SQL Server Management Studio lets you add logins through the Security node. Figure 13-14 shows a login named Joe being added. Notice that adding the SQL Server login requires a name and a password.
Figure 13-14: The login screen, where Joe's name and password are entered to create a new login
If SQL Server authentication is turned on, your connection string must contain a valid user name and password. This can become a security issue because you must deal with storing and passing the user and password information without someone discovering a way to read it. A typical connection string might look like the following.
Data Source=.;Initial Catalog=Northwind;User ID=Joe;Password=hi2u2
In SQL Server 2000 and earlier, the SQL Server authentication lacks strong controls such as password complexity, expiration, lockout, and history when you use SQL Server logins. SQL Server 2005 has all these controls, and they inherit from the Windows operating system policy. You should be aware, however, that SQL Server authentication is provided for backward compatibility and for use with Windows 98/Me installations where Windows authentication isn't an option.
SQL Server 2005 can map Windows credentials to a SQL Server login. In some cases, you can use this instead of delegation. In Figure 13-14, shown earlier, notice that the bottom of the screen lets you specify credential mapping. If you map a Windows user account to this login, you can use this SQL login to connect to SQL Server and execute SQLCLR code that can access remote SQL Servers using this mapped account.
You should make every attempt to accomplish your tasks using integrated Windows authentication because no user credentials are exposed. If you must use SQL Server authentication with a Web application, be sure to read the "Storing Encrypted Connection Strings in Web Applications" section later in this chapter.
Integrated Windows authentication requires the user to be authenticated by the Windows operating system before attempting to connect to SQL Server. This is the default setting for SQL Server 2005. If you have changed to Mixed Mode, both SQL Server and Windows Authentication Mode logins are allowed access.
To access SQL Server using integrated Windows authentication, you must make sure your Windows credentials match one of the logins in SQL Server. In SQL Server, you can create a login that maps directly to a single user, but it's common to create a login that maps to a Windows role. You can grant access to all of your users by adding a couple of logins that map to the user roles.
If you want to use integrated Windows authentication, your connection string must contain the command to use a trusted connection or to use integrated security, as shown in the following sample connection strings.
Server=.;Database=Northwind;Trusted_Connection=Yes Server=.;Database=Northwind;Integrated Security=SSPI
These connection strings are synonymous; notice that there is no user name or password. You should always try to use integrated Windows authentication.
In SQL Server, the login is used for authentication, but each database contains its own table of users. The database user is granted permissions to access resources. For a stored procedure, the only permission is execute. For tables, the permissions include select, insert, update, and delete. The login is mapped to a user in the tables that the login needs access to, and the login has the permissions of the user, as shown in Figure 13-15.
Figure 13-15: SQL Server authentication uses logins; authorization looks up users in each database to check their permissions.
The complexity of mapping the correct permissions to a user in a large application can be overwhelming, especially if you are trying to figure out how someone should have SELECT permissions on a table in some scenarios but not in others. This is why it's generally a good idea to remove all permissions that are assigned to tables and to create stored procedures for each action, assigning execute permissions to the appropriate users. As long as the stored procedure owner is the same as the table owner, no check is made to see if the user has access to the table when the stored procedure is executing, and permission assignment is greatly simplified.