To achieve scalability using database connection pooling foregoes having the database manage security. This is
because database connection pooling requires the connection string be identical to pool connections. Therefore, you must manage security elsewhere. If you must track database operations on per user basis, consider adding a parameter for user identity to each operation and manually log user actions in the database.
Following the advice above, another issue is how to store the database connection string, which typically contains security credentials, so multiple users can access it without compromising security. Most sample applications demonstrate storing the connection string in the Web.config or global.asax files. However, because these files are plain text files that have limited security, it is not the best location for storing this information. Should an intruder compromise your Web server's security, these files would be easily accessible. Here are just a few alternatives:
- If using the Web.config file, store the connection string encrypted and then decrypt the connection string in your application code when needed.
- Build a COM+ application using the ServicedComponent Class and store the connection string in the construct string for that component.
When storing sensitive information in the constructor string, you should verify the following:
- Only the appropriate users/groups belong to the Reader role of the System Package. However, you must carefully manage COM+ to prevent it from being unable to read its own configuration.
- You have controlled and audited access to the %windows%\Registration folder, where the COM+ configuration database (RegDB) stores its files.
For more information, see ServicedComponent Class .
- Use integrated security to make a trusted connection with SQL Server. This makes it possible for you to use a connection string that eliminates the need for storing a password in the connection string, such as: "Data Source=mySqlServer;Integrated Security=SSPI;Initial Catalog=myDB"
There are some drawbacks to using integrated security, most of which you can overcome. Because integrated security requires a Windows account, it defeats connection pooling if you impersonate each
authenticated principal using an individual Windows account. However, if you instead impersonate a limited number of Windows accounts, with each account representing a particular role, you can overcome this drawback. Each Windows account must be a domain account with IIS and SQL Server in the same or trusted domains. Alternatively, you can create identical (including passwords) Windows accounts on each machine.
After a typical installation, the default security authentication mode is Windows Authentication for SQL Server 2000, which is different from SQL Server 7.0. In SQL Server 7.0, the default authentication mode is Mixed (Windows Authentication Mode and SQL Server Authentication). Windows Authentication is a better security method because of the additional security features it provides, such as secure validation and encryption of passwords, password expiration and auditing. For more information, see Authentication Modes .
If you configure SQL Server to use Windows Authentication, you could create one Windows account for read-only operations and another Windows account for read/write operations. You then map each Windows account to a SQL Server login and establish the desired permissions. Using application logic, you then determine which Windows account to impersonate when performing database operations. In SQL Server, you can add any Windows user account as a member of a fixed database role. Each member gains the permissions applied to the fixed database role. For more information, see Managing Permissions .
For SQL Server 7.0, integrated security does not work with SQL Server's TCP/IP network library, but uses the named pipes network library instead.
As an added security measure, the ConnectionString property of the SqlConnection object does not persist or return the full connection string by default. To do so, you must set Persist Security Info to true. For more information, see SqlConnection.ConnectionString Property and SqlConnection Class
See Also
