NOTE in Visual Basic .NET

Draw Data Matrix ECC200 in Visual Basic .NET NOTE

When you experiment with different authentication options, you might be surprised to find that no connection to the database is created with the new credentials. In the case of SQL Server, you may not see the connection when using the Process Info screen in Enterprise Manager or executing the sp_who system procedure. The reason for this is most likely the report session caching that the Report Server does behind the scenes. The default

Figure 3.8 Use the SqlClient: Current # Pooled Connections counter found under the .NET CLR DATA category to monitor database connection pooling.

session timeout specified in the SessionTimeout field in the ConfigurationInfo table (ReportServer database) is 600 seconds. This means that if the Report Server decides to reuse the report intermediate format when the report is requested again, it won t query the data source within that period. Instead, it will use the report IF serialized in the Report Server Temporary Database. We discuss report caching in detail in chapter 8. For the time being, when you experiment with different authentication connection options, you may want to decrease the SessionTimeout value so that the report session expires sooner. Our experiments show that you cannot completely disable the ReportServer session caching. The minimum value you can set the SessionTimeout field to is 60 seconds. If you decide to change SessionTimeout, don t forget to restart IIS. Alternatively, you can manually delete the record in the table SessionData (ReportServerTempDb database) or set its Expiration column to a date in the past.

Step 1 Assuming that RS is installed locally on your computer, open the Report Manager by navigating to http://localhost/reports in the browser. Navigate to the AWReporter folder and click on the AW Shared DS data source. Step 2 Select the Credentials Stored Securely option and specify the credentials of a database login that has rights to query the AdventureWorks database. Step 3 Open the Performance Console and add the SqlClient:Current # Pooled Connections counter found under the .NET CLR Data performance category for the _global_ domain. Step 4 Open another instance of the browser and request the Sales by Territory report (the encoded report URL should be http://localhost/Reports/

Pages/Report.aspx ItemPath=%2fAWReporter%2fSales+By+ Territory). Assuming that there is no other connection with the same

credentials, you should see the pooled connection counter going up. Wait for one minute or remove the session record from the SessionData table in ReportServerTempDB. Step 5 Repeat the process by opening up another instance of the browser and requesting the report again. The pooled connection counter should remain unchanged. This means that the .NET SqlClient provider uses connection pooling behind the scenes and reuses the already existing connection. Let s now change the authentication options of the data source to Windows Integrated Security. For the new test, you need two Windows user accounts, which are members of the Administrator group. You can use regular user accounts, but you have to specifically give them rights to the database, while members of the Administrator group automatically get admin privileges. Fire up the browser again and request the Sales by Territory report. Observe the pooled connection performance counter. Now, right-click on the Internet Explorer shortcut and choose Run As. Specify the username and password for the second user account. Run the report again, and you will see the counter going up instead of remaining unchanged. This proves that the Report Server doesn t pool connections. Let s wrap up our overview of authentication options with some recommendations. Authentication best practices To summarize, we recommend that you follow these guidelines for data source authentication: Use shared data sources. For example, almost all reports from the AWReporter project use the AW Shared DS.rds shared data source. Use the Credentials Stored Securely in the Report Server option with standard or Windows-based authentication.