SQL Server Security
SQL Server Security
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.
SQL Server Authentication
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.
Using SQL Server Authentication
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.
Using SQL Server Authentication as a Form of Delegation
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.
