Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Session Remains Open After ReportViewerControl Form is Closed.

Posted on 2017-04-14
8
Medium Priority
?
103 Views
Last Modified: 2017-04-18
Hi experts!

I'm working on a Windows Forms application in VB, using Visual Studio 2010. The application has an option to open a form that contains a ReportViewerControl which displays an SSRS report housed on our MS SQL Server 2008 R2 server. When the form is displayed the report shows up as expected and can be closed without any errors that I can see.

However, I've noticed that the session ID assigned when that form is launched remains open well after the form has been closed. In fact, it stays open even after the application itself is shut down. It does eventually go away - about 10 minutes later, which is (I believe) the default timeout value for the ServerReport class.

For reference, I'm seeing the session id hanging out there when I run the following from SSMS:
SELECT session_id FROM sys.dm_exec_sessions WHERE login_name = 'MyLoginName';

Open in new window


I'd like to avoid shorting the timeout value, in the hopes of preventing timeout errors for the users. However, I'd also like to close that session safely when the form is closed, as the session is no longer needed and seems like a waste of resources to have it left open.

My question is, what is the best way to safely close the SQL session when the report form is closed?
0
Comment
Question by:Jeff Edmunds
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 16

Expert Comment

by:Megan Brooks
ID: 42093769
Is there VB code in the RDL (or elsewhere) that makes a direct connection to the database from the client? ServerReports are rendered by the ReportServer process on the report server. That process makes all of the database connections, using its own identity and not that of the end user. The ReportViewer talks to the report server via the execution web service.

Which are you querying for connections, a data source database or the ReportServer database?
0
 
LVL 1

Author Comment

by:Jeff Edmunds
ID: 42093784
The report uses a datasource stored on the report server. The dataset executes a SQL stored procedure on the data server.  There is no VB code in the RDL - at least, not that I'm aware of... maybe something system generated, but as far as I know that's just xml.

However, to display the report in the ReportViewer control on the VB form, it uses an instance of the ServerReport class.
0
 
LVL 16

Expert Comment

by:Megan Brooks
ID: 42093798
OK, that is the normal case. So where are those database sessions coming from? ReportViewer (via the ServerReport class) connects to the execution web service endpoint of the report server service, not to a database server directly.

Is the report server service running under your identity? On my dev system it runs as a virtual service account, NT Service\ReportServer, and that is what I would expect to see as the login for any database connections.

The report server might hang on to its database connections for a while, perhaps to do connection pooling. I haven't paid a lot of attention to what goes on there.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Author Comment

by:Jeff Edmunds
ID: 42093803
The session id is tied to a login that I created just for this application. The 'MyLoginName' in the query I posted above is that, not the service account. (Names changed to protect the innocent and all that, but point being I'm specifying my application account in that WHERE clause.)

I actually stumbled across the session hanging out there by trying to drop and recreate that login and getting an error stating that user (the application, not a person) was still logged in. The program is not in production yet so the only 'user' is me while I'm working on it and I had completely closed the program and Visual Studio.

Now it is possible that I used that custom account when I setup the datasource, using SQL Server Authentication (very likely, although I don't recall for sure...will have to double check when I'm back in the office). However, I'm accessing that report and the datasource it uses via that ServerReport class in the VB front end.
0
 
LVL 16

Expert Comment

by:Megan Brooks
ID: 42093805
The datasets are going to be using the datasource credentials, so that's what you will see there for the login, even though the session is connected to the report server, not the client. (It's still hard for me to visualize after all these years -- I don't usually need think about it.)

The session is not with your client, though. For it to close when the client terminates, the client would have to notify the report server that it was shutting down, and the report server would have to close the connection. I don't think that happens.

Here is a blog post about report server connection pooling. It might be a little hard to read.
0
 
LVL 1

Author Comment

by:Jeff Edmunds
ID: 42097163
I've verified that I did, in fact, use the SQL user in the shared data source that I created on the reporting server to connect to that database. If I understand correctly, you don't believe there is a good way to signal the report server from the client to request that it close that connection?
0
 
LVL 16

Accepted Solution

by:
Megan Brooks earned 2000 total points
ID: 42097253
Unless it is causing a problem, I wouldn't think you would want to close the connection every time. There are situations where holding the connection open can lead to unwanted behavior, and you do have the option in the connection string to block connection pooling, to prevent that from happening. A single report should not create problems, but if you have a variety reports (or datasets within a report), presenting similar-but-different connection strings, there could be potential for trouble.

In general, though, connection pooling (caching the connection and holding it open for a period of time) saves resource use. Whether the savings are significant or not depends on the size of the workload. If you are querying against a database to load multiple datasets within a report, however, as is so often the case, connection pooling supports opening the connection just once during each report execution, as well as across different executions. If you do that a lot, pooling is going to help.

I can't say definitely that there is no way to make an explicit web service request that closes the connection. I'm not aware of anything like that, but it is not something I have studied.
0
 
LVL 1

Author Comment

by:Jeff Edmunds
ID: 42097277
Fair enough. Since it does eventually close itself, it isn't a major issue. It hasn't really caused any problems, other than while trying to drop and recreate that login during development. It just felt like something I should be cleaning up once the application has closed. However, I can now see the advantage to leaving it open. Thanks for your help!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question