. NET data access architecture Guide 2

More information

When using the SQL Server. NET data provider connection pool, pay attention to the following aspects:

•

The connection is pooled by the exact match algorithm on the connection string. The pool mechanism is even sensitive to spaces between name-value pairs. For example, the following two connection strings will lead to two independent pools, because the second connection string contains additional space characters. SqlConnection conn = new SqlConnection( "Integrated Security=SSPI;Database=Northwind");conn.Open(); // Pool A is createdSqlConmection conn = new SqlConnection( "Integrated Security=SSPI ; Database=Northwind");conn.Open(); // Pool B is created (extra spaces in string)

•

The connection pool is divided into multiple transaction proprietary pools and a pool corresponding to the connections that are not currently registered in the transaction. For threads associated with a specific transaction context, the corresponding pool (the pool contains the connections registered in the transaction) is returned. This makes the use of registered connections a transparent process.

 

Ole db. NET data provider pool mechanism

The ole db. NET data provider pooled the connection by using the basic ole db resource pool. There are multiple options for configuring the resource pool:

•	You can use connection strings to configure, enable, or disable resource pools.
•	You can use the registry.
•	You can configure the resource pool programmatically.

To avoid deployment problems related to the Registry, do not use the registry to configure the ole db resource pool.

Monitoring connection pool

To monitor the application's use of the connection pool, you can use the event probe tool that comes with SQL Server, or use Microsoft Windows? 2000 Performance Monitor Tool attached to the operating system.

Use the SQL Server event probe to monitor the connection pool

1.	ClickStart, PointingPrograms, PointingMicrosoftSQLServerAnd then clickProfilerTo start the event probe.
2.	InFileMenu, pointingNewAnd then clickTrace.
3.	Provide connection details, and then clickOK.
4.	InTrace PropertiesIn the dialog box, clickEventsTab.
5.	InSelected event classesList, make sure thatAudit LoginAndAudit LogoutThe event is displayed inSecurity AuditBelow. To make the trail clearer, delete all other events from the list.
6.	ClickRunTo start the trail. When the connection is established, you will seeAudit LoginEvent. When the connection is closed, you will seeAudit LogoutEvent.

Use Performance Monitor to monitor connection pools

1.	ClickStart, PointingPrograms, PointingAdministrative ToolsAnd then clickPerformanceTo start the Performance Monitor.
2.	Right-click the image background, and then clickAddCounters.
3.	InPerformance objectIn the drop-down list, clickSQL Server: General Statistics.
4.	In the displayed list, clickUser Connections.
5.	ClickAddAnd then clickClose.

 

Management Security

Although the database connection pool improves the overall scalability of applications, it means that you no longer can manage security at the database level. This is because to support the connection pool, the connection strings must be identical. If you need to track the database operations of each user, consider adding a parameter to pass the user identity and manually record user operations in the database. You need to add this parameter to each operation.

Use Windows Authentication

When connecting to SQL Server, you should use Windows Authentication because it provides many advantages:

1.	Security is easier to manage because you use a single (Windows) security model instead of an independent SQL Server security model.
2.	Avoid embedding usernames and passwords in connection strings.
3.	The user name and password are not transmitted over the network in plaintext mode.
4.	By using the password expiration time, minimum length, and locking the account after multiple invalid login requests, the login security is improved.

 

Store connection strings

To store database connection strings, you can choose from multiple options, which have different levels of flexibility and security. Although hard encoding of the connection string in the source code provides optimal performance, the file system cache ensures that the performance reduction caused by storing the string in the file system is negligible. In almost all cases, the extra flexibility provided by external connection strings is preferred (it supports administrator configuration ).

When you choose to connect to the string storage method, the two most important things to note are security and configuration simplicity, followed by performance.

You can choose the following locations to store database connection strings:

•	In the application configuration file. For example, the Web. config of the ASP. NET Web application
•	In a Universal Data Link (UDL) file (only supported by the ole db. NET data provider)
•	In the Windows Registry
•	In a custom file
•	In the COM + directory, the method is to use the build string (applicable only to service components)

By using Windows authentication to access SQL Server, you can avoid storing the user name and password in the connection string. If you require stricter security measures, consider storing the connection string in encrypted format.

For ASP. NET Web applications, storing connection strings in the Web. config file in encrypted format represents a secure and configurable solution.

NoteYou can setPersist Security InfoSet the nameFalseTo prohibitSqlConnectionOrOleDbConnectionObjectConnectionStringAttribute returns security-sensitive details (such as passwords ).

The following sections describe how to use various options to store the connection string, and introduce the relative advantages and disadvantages of various methods. This content helps