One
of the most common scenarios for a distributed application
involves reading and writing data on a remote database.
The dilemma that arises is how to do so securely while
maintaining application scalability. Where you choose
to manage security in your application will greatly
impact, either negatively or positively, the scalability
of your application.
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
Security
Model |
