Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Session Remains Open After ReportViewerControl Form is Closed.

Posted on 2017-04-14
8
Medium Priority
?
115 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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

926 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