This question is locked. New answers and comments are not allowed.
Hi,
We are using OpenAccess to connect to a SQL database uwing Windows Authentication instead of SQL Authentication. We therefore have Windows Integrated Authentication enabled on the IIS webapplication and are using a connectionstring similar to the following:-
<add name="MyConnection" connectionString="Data Source=XXX.myDomain.com;Initial Catalog=MyDatabaseName;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False" />
We are also using the default Connection Pool of "Use OpenAccess Connection Pool" as part of the OpenAccess Backend Configuration. Whilst this seemed fine during initial development and testing, we began to have issues when having several users test the system at the same time. Essentially user contexts appear to be getting re-used resulting in people storing records using other peoples credentials (the wrong SUSER_SNAME at the SQL server). We have tested this on several of our webapplications/databases and see the same results. We have also ensured that all of the OpenAccess calls are embedded within "using" blocks to ensure that resources are freed correctly.
We then tried the "Use OpenAccess Connection Pool with OpenAccess Connection Management" settings. That seems to have resolved the issue with re-using user contexts.
So, my question is: What type of connection pooling should we use with Integrated Authentication and is it expected that the default pooling will re-use user contexts? We could not find documentation to explain the difference between the connnection types.
On a different, but possibly related, issue; whilst attempting to debug this situation we enabled logging on OpenAccess and noticed that our IIS servers are logging hundreds of errors like the following. This repeats even when there is no-one access the system. We have searched extensively for solutions to this but cannot find any useful informatation. This does not happen on our development PCs. We have tested on 2 different sets of IIS and SQL servers and see the same errors.
17:59:28.975 driver.con.connect 390 MultipleActiveResultSets=true;Data Source=XXX.myDomain.com;Initial Catalog=MyDatabaseName;Integrated Security=True; Trusted_Connection=Yes
17:59:28.975 driver.pool.error Exception in connection pool cleanup thread.
FYI : We are using OpenAccess 2013.3.1014
Regards,
Steve.
We are using OpenAccess to connect to a SQL database uwing Windows Authentication instead of SQL Authentication. We therefore have Windows Integrated Authentication enabled on the IIS webapplication and are using a connectionstring similar to the following:-
<add name="MyConnection" connectionString="Data Source=XXX.myDomain.com;Initial Catalog=MyDatabaseName;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False" />
We are also using the default Connection Pool of "Use OpenAccess Connection Pool" as part of the OpenAccess Backend Configuration. Whilst this seemed fine during initial development and testing, we began to have issues when having several users test the system at the same time. Essentially user contexts appear to be getting re-used resulting in people storing records using other peoples credentials (the wrong SUSER_SNAME at the SQL server). We have tested this on several of our webapplications/databases and see the same results. We have also ensured that all of the OpenAccess calls are embedded within "using" blocks to ensure that resources are freed correctly.
We then tried the "Use OpenAccess Connection Pool with OpenAccess Connection Management" settings. That seems to have resolved the issue with re-using user contexts.
So, my question is: What type of connection pooling should we use with Integrated Authentication and is it expected that the default pooling will re-use user contexts? We could not find documentation to explain the difference between the connnection types.
On a different, but possibly related, issue; whilst attempting to debug this situation we enabled logging on OpenAccess and noticed that our IIS servers are logging hundreds of errors like the following. This repeats even when there is no-one access the system. We have searched extensively for solutions to this but cannot find any useful informatation. This does not happen on our development PCs. We have tested on 2 different sets of IIS and SQL servers and see the same errors.
17:59:28.975 driver.con.connect 390 MultipleActiveResultSets=true;Data Source=XXX.myDomain.com;Initial Catalog=MyDatabaseName;Integrated Security=True; Trusted_Connection=Yes
17:59:28.975 driver.pool.error Exception in connection pool cleanup thread.
FYI : We are using OpenAccess 2013.3.1014
Regards,
Steve.
2 Answers, 1 is accepted
0
Hi Steve,
Thank you for the detailed explanation of your issues.
The integrated security approach is only supported when you rely on the ADO driver itself. This can be done by either choosing the 'Use Telerik Data Access Connection Pool with Telerik Data Access Connection Management' or 'Use ADO Connection Pool' setting as you already found out.
The reason for that is, that when using the integrated connection pool, we are managing the open connections. Though we can not take care about the actual credentials used for the actual connection since our the connection pool just manages all connections with the same string. In case of integrated security, all users are using the same connection string and can therefore not be separated.
The ADO driver instead is able to handle this situation and is therefore the way to go. With the previously mentioned settings you are using the ADO connection pool instead of the integrated one though with the 'Use Telerik Data Access Connection Pool with Telerik Data Access Connection Management' setting we allow some maintenance tasks on the connections to be done.
Regarding the mentioned exception 'Exception in connection pool cleanup thread'. This could be related to the problem above though we are not tracking enough information at the moment to really know what the cause here is.
That said, with the next version of the Product, to be expected in mid of April, we have enhanced the exception reporting which allows us to dig deeper here.
I hope this information is helpful for you.
Feel free to ask in case you have any other question.
Regards,
Ralph Waldenmaier
Telerik
Thank you for the detailed explanation of your issues.
The integrated security approach is only supported when you rely on the ADO driver itself. This can be done by either choosing the 'Use Telerik Data Access Connection Pool with Telerik Data Access Connection Management' or 'Use ADO Connection Pool' setting as you already found out.
The reason for that is, that when using the integrated connection pool, we are managing the open connections. Though we can not take care about the actual credentials used for the actual connection since our the connection pool just manages all connections with the same string. In case of integrated security, all users are using the same connection string and can therefore not be separated.
The ADO driver instead is able to handle this situation and is therefore the way to go. With the previously mentioned settings you are using the ADO connection pool instead of the integrated one though with the 'Use Telerik Data Access Connection Pool with Telerik Data Access Connection Management' setting we allow some maintenance tasks on the connections to be done.
Regarding the mentioned exception 'Exception in connection pool cleanup thread'. This could be related to the problem above though we are not tracking enough information at the moment to really know what the cause here is.
That said, with the next version of the Product, to be expected in mid of April, we have enhanced the exception reporting which allows us to dig deeper here.
I hope this information is helpful for you.
Feel free to ask in case you have any other question.
Regards,
Ralph Waldenmaier
Telerik
OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
0
SteveT
Top achievements
Rank 2
Rank 2
answered on 01 Apr 2014, 09:11 AM
Thanks Ralph.
That does clarify it and give us confidence in our choice of pooling technique. I suspect that many people use integrated authentication for internal/intranet applications and I think it would be good to mention the integrated authentication implications in the Open Access (or Data Access) documentation.
We did try the "Use ADO Connection Pool" and although it solved the main issue (re-using user contexts) it seemed to lock the database table after an update even though the code was inside a "using" block. The only way that we could resolve that was to add a dispose statement after the SaveChanges() call. I found this confusing as I assumed that the using statement took care of disposal at the end of its inner code block. That is why we settled on the "Use OpenAccess Connection Pool with OpenAccess Connection Management" option. I do not believe that the "Use ADO Connection Pool" option generated those exceptions so would have been our chose were it not for the apparent need to explicitly dispose after every update.
I look forward to the next version so that we can try and resolve those errors that we are seeing. They do not seem to adversely affect anything so we will turn off logging and see how it goes until then. By the way it would be good to be able to enable/disable logging in a config file rather than having to rebuild the application.
Regards,
Steve
That does clarify it and give us confidence in our choice of pooling technique. I suspect that many people use integrated authentication for internal/intranet applications and I think it would be good to mention the integrated authentication implications in the Open Access (or Data Access) documentation.
We did try the "Use ADO Connection Pool" and although it solved the main issue (re-using user contexts) it seemed to lock the database table after an update even though the code was inside a "using" block. The only way that we could resolve that was to add a dispose statement after the SaveChanges() call. I found this confusing as I assumed that the using statement took care of disposal at the end of its inner code block. That is why we settled on the "Use OpenAccess Connection Pool with OpenAccess Connection Management" option. I do not believe that the "Use ADO Connection Pool" option generated those exceptions so would have been our chose were it not for the apparent need to explicitly dispose after every update.
I look forward to the next version so that we can try and resolve those errors that we are seeing. They do not seem to adversely affect anything so we will turn off logging and see how it goes until then. By the way it would be good to be able to enable/disable logging in a config file rather than having to rebuild the application.
Regards,
Steve