SQL Session Remains Open After ReportViewerControl Form is Closed.

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?
Jeff EdmundsApplication Developer/SQL DBAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Megan BrooksSQL Server ConsultantCommented:
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?
Jeff EdmundsApplication Developer/SQL DBAAuthor Commented:
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.
Megan BrooksSQL Server ConsultantCommented:
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeff EdmundsApplication Developer/SQL DBAAuthor Commented:
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.
Megan BrooksSQL Server ConsultantCommented:
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.
Jeff EdmundsApplication Developer/SQL DBAAuthor Commented:
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?
Megan BrooksSQL Server ConsultantCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeff EdmundsApplication Developer/SQL DBAAuthor Commented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.