Connection Pooling with the .NET Provider on IIS
An ASP.NET/IIS intranet app using Windows authentication and impersonation found that, with the Informix .NET provider, pooled connections were reused across different users — so a second user's request ran under the first user's database credentials. Setting Pooling=False fixed it but raised performance concerns. Replies explained that Informix cannot re-authenticate an existing connection, and that the n-tier approach is to pool connections under a single application account and handle user identity/permissions in the application layer. No provider-level fix was offered: the choices given were disabling pooling or redesigning authentication.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have developed an Intranet IIS web application that is configured to use Windows Authentication against our Active Directory. The sites run with identity impersonation so all connections against the database SHOULD use the the credentials of the original user who requested the web page. Initially this appeared to be working correctly. If you have infrequent visitors or many hits from the same user everything appears fine however if you get visits from multiple identities in quick succession the second and third connections re-use the credentials of the first connection. Adding "Pooling=False" to the Connection string resolves this issue, however I fear we will run into performance issues when we deploy the system to our live servers. Has anyone seen this issue before? Is this a bug in the Provider? I have never seen this with other (SqlClient) .NET Data Providers. TIA MC
mc wrote: > I have developed an Intranet IIS web application that is configured to > use Windows Authentication against our Active Directory. The sites run > with identity impersonation so all connections against the database > SHOULD use the the credentials of the original user who requested the > web page. > > Initially this appeared to be working correctly. If you have infrequent > visitors or many hits from the same user everything appears fine however > if you get visits from multiple identities in quick succession the > second and third connections re-use the credentials of the first > connection. > > Adding "Pooling=False" to the Connection string resolves this issue, > however I fear we will run into performance issues when we deploy the > system to our live servers. > > Has anyone seen this issue before? Is this a bug in the Provider? I have > never seen this with other (SqlClient) .NET Data Providers. > > TIA > > > MC Currently you cannot change the user credentials on an already established database connection. This could change in the future, but currently it's not supported. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
I could understand that for "Open Connections" however it seems a little strange that after I have Closed the connection the "Security Sub System" of the Informix Drivers allows that connection to be reused. Isn't it a massive security hole? Am I right in assuming that the only solution to this issue is to turn off Connection Pooling? Regards MC Fernando Nunes wrote: > mc wrote: >> I have developed an Intranet IIS web application that is configured to >> use Windows Authentication against our Active Directory. The sites run >> with identity impersonation so all connections against the database >> SHOULD use the the credentials of the original user who requested the >> web page. >> >> Initially this appeared to be working correctly. If you have >> infrequent visitors or many hits from the same user everything appears >> fine however if you get visits from multiple identities in quick >> succession the second and third connections re-use the credentials of >> the first connection. >> >> Adding "Pooling=False" to the Connection string resolves this issue, >> however I fear we will run into performance issues when we deploy the >> system to our live servers. >> >> Has anyone seen this issue before? Is this a bug in the Provider? I >> have never seen this with other (SqlClient) .NET Data Providers. >> >> TIA >> >> >> MC > > Currently you cannot change the user credentials on an already > established database connection. > This could change in the future, but currently it's not supported. > Regards. >
Isn't that the purpose of a connection pool? I mean the point is that you're reusing the connection so you don't have to constantly authenticate the user. ;-) And to answer Fernando, re-setting authenticated data on the fly is not a good thing. Nor would you want it. -G > Date: Sat, 13 Jun 2009 11:55:36 +0100 > From: mc@community.nospam > Subject: Re: Connection Pooling with the .NET Provider on IIS > To: informix-list@iiug.org > > I could understand that for "Open Connections" however it seems a little strange that after I have > Closed the connection the "Security Sub System" of the Informix Drivers allows that connection to be > reused. Isn't it a massive security hole? Am I right in assuming that the only solution to this > issue is to turn off Connection Pooling? > > Regards > > > MC > > Fernando Nunes wrote: > > mc wrote: > >> I have developed an Intranet IIS web application that is configured to > >> use Windows Authentication against our Active Directory. The sites run > >> with identity impersonation so all connections against the database > >> SHOULD use the the credentials of the original user who requested the > >> web page. > >> > >> Initially this appeared to be working correctly. If you have > >> infrequent visitors or many hits from the same user everything appears > >> fine however if you get visits from multiple identities in quick > >> succession the second and third connections re-use the credentials of > >> the first connection. > >> > >> Adding "Pooling=False" to the Connection string resolves this issue, > >> however I fear we will run into performance issues when we deploy the > >> system to our live servers. > >> > >> Has anyone seen this issue before? Is this a bug in the Provider? I > >> have never seen this with other (SqlClient) .NET Data Providers. > >> > >> TIA > >> > >> > >> MC > > > > Currently you cannot change the user credentials on an already > > established database connection. > > This could change in the future, but currently it's not supported. > > Regards. > > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Lauren found her dream laptop. Find the PC that’s right for you. http://www.microsoft.com/windows/choosepc/?ocid=ftp_val_wl_290
Ian Michael Gumby wrote: > Isn't that the purpose of a connection pool? > > I mean the point is that you're reusing the connection so you don't have > to constantly authenticate the user. ;-) > > And to answer Fernando, re-setting authenticated data on the fly is not > a good thing. Nor would you want it. > > -G > > > > Date: Sat, 13 Jun 2009 11:55:36 +0100 > > From: mc@community.nospam > > Subject: Re: Connection Pooling with the .NET Provider on IIS > > To: informix-list@iiug.org > > > > I could understand that for "Open Connections" however it seems a > little strange that after I have > > Closed the connection the "Security Sub System" of the Informix > Drivers allows that connection to be > > reused. Isn't it a massive security hole? Am I right in assuming that > the only solution to this > > issue is to turn off Connection Pooling? > > > > Regards > > > > > > MC > > > > Fernando Nunes wrote: > > > mc wrote: > > >> I have developed an Intranet IIS web application that is > configured to > > >> use Windows Authentication against our Active Directory. The sites > run > > >> with identity impersonation so all connections against the database > > >> SHOULD use the the credentials of the original user who requested the > > >> web page. > > >> > > >> Initially this appeared to be working correctly. If you have > > >> infrequent visitors or many hits from the same user everything > appears > > >> fine however if you get visits from multiple identities in quick > > >> succession the second and third connections re-use the credentials of > > >> the first connection. > > >> > > >> Adding "Pooling=False" to the Connection string resolves this issue, > > >> however I fear we will run into performance issues when we deploy the > > >> system to our live servers. > > >> > > >> Has anyone seen this issue before? Is this a bug in the Provider? I > > >> have never seen this with other (SqlClient) .NET Data Providers. > > >> > > >> TIA > > >> > > >> > > >> MC > > > > > > Currently you cannot change the user credentials on an already > > > established database connection. > > > This could change in the future, but currently it's not supported. > > > Regards. > > > > > > > _______________________________________________ > > Informix-list mailing list > > Informix-list@iiug.org > > http://www.iiug.org/mailman/listinfo/informix-list > > ------------------------------------------------------------------------ > Lauren found her dream laptop. Find the PC that's right for you. > <http://www.microsoft.com/windows/choosepc/?ocid=ftp_val_wl_290>; Why is it not a good idea? Most other RDBMS allow it... I can imagine one or two objections, but I think it's nice to have the option... Your objections are welcome. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
But the problem is the Connection pool is letting someone else use your already authenticated user session. Scenario: User A Hits Website, his credentials are passed through to asp.net and on to the Informix .NET Provider which access database and returns results based on the Current "USER". User B Hits Website (shortly after User A) his credentials are passed through to asp.net and on to the Informix .NET Provider this grabs a connection from the pool (previously used by User A) the provider access the data as User A and returns the results to User B. Thus a breach of confidentiality occurs. Do you see the problem? For me there are only two acceptable modes of operation. Re-use Connection A but with the credentials of User B. Block the re-use (by anyone other than User A) of the Connection created for User A I'm still assuming the only safe way to operate is to disable connection Pooling? TIA MC Ian Michael Gumby wrote: > Isn't that the purpose of a connection pool? > > I mean the point is that you're reusing the connection so you don't > have to constantly authenticate the user. ;-) > > And to answer Fernando, re-setting authenticated data on the fly is > not a good thing. Nor would you want it. > > -G > > > > Date: Sat, 13 Jun 2009 11:55:36 +0100 > > From: mc@community.nospam > > Subject: Re: Connection Pooling with the .NET Provider on IIS > > To: informix-list@iiug.org > > > > I could understand that for "Open Connections" however it seems a > little strange that after I have > > Closed the connection the "Security Sub System" of the Informix > Drivers allows that connection to be > > reused. Isn't it a massive security hole? Am I right in assuming > that the only solution to this > > issue is to turn off Connection Pooling? > > > > Regards > > > > > > MC > > > > Fernando Nunes wrote: > > > mc wrote: > > >> I have developed an Intranet IIS web application that is > configured to > > >> use Windows Authentication against our Active Directory. The > sites run > > >> with identity impersonation so all connections against the database > > >> SHOULD use the the credentials of the original user who > requested the > > >> web page. > > >> > > >> Initially this appeared to be working correctly. If you have > > >> infrequent visitors or many hits from the same user everything > appears > > >> fine however if you get visits from multiple identities in quick > > >> succession the second and third connections re-use the > credentials of > > >> the first connection. > > >> > > >> Adding "Pooling=False" to the Connection string resolves this > issue, > > >> however I fear we will run into performance issues when we > deploy the > > >> system to our live servers. > > >> > > >> Has anyone seen this issue before? Is this a bug in the Provider? I > > >> have never seen this with other (SqlClient) .NET Data Providers. > > >> > > >> TIA > > >> > > >> > > >> MC > > > > > > Currently you cannot change the user credentials on an already > > > established database connection. > > > This could change in the future, but currently it's not supported. > > > Regards. > > > > > > > _______________________________________________ > > Informix-list mailing list > > Informix-list@iiug.org > > http://www.iiug.org/mailman/listinfo/informix-list > > ------------------------------------------------------------------------ > Lauren found her dream laptop. Find the PC that's right for you. > <http://www.microsoft.com/windows/choosepc/?ocid=ftp_val_wl_290>;
MC, Your complaint is that a connection pool does what a connection pool is supposed to do. Essentially you're telling the waitress that you want a stack of pancakes and a coffee, and when she brings your order, you complain that its a stack of pancakes and a coffee. ;-) In straight forward client/server 2 tier app development, you dont' share the connections and you can run in to a couple of problems in terms of scalability and stale connections. Not very efficient, however you do have no 'breech of confidentiality' to concern yourself with. (At least not in terms of connections.) The n-tier way of developing an application is to separtate the user authentication from the 'transport' layer. If we look at developing a web application, User A will have a different session ID than User B. At the start of the session, you authenticate the different users and based on their pre-defined permissions, you control what they can see and do. Each session tracks the user id and their role/privilege information. The app server has a preset number of connections and the connection pool connects to the database using a known userid and password. This can be configurable and changed as necessary. When accessing the database, the session record is passed back along with the data request and then depending on the permissions of the database connection, the user authentication, and the type of transaction, you can either do the task or have an exception thrown. Note: I said a couple of things that *are* important to remember.... 1) The connection's user ID is authenticated by the OS or via PAM or LDAP. (In most cases this means that you have to create a nologin userid for the application.) This limits access to the system/network resources and reduces the chances that a user is granted more network/system authority than they should have. Also note, that depending on the userid, you can control what access the app has within the database. A good example is that if you have a retail application, then you only allow SELECT/UPDATE/DELETE on certain tables, SELECT/UPDATE on others and no access to DDL statements like CREATE/GRANT/DROP and other tables. 2) The end user's ids could be controlled now via an LDAP server (single sign on), or via the database. Depending on the framework/code/appserver you have a couple of authentication methods built in. This is more secure and you have a couple of block points in terms of security. The web server has some security where you can limit connections to the login page of your app. The app server has some security in that when the app server is started, only those identified connection pools are started and you can create multiple connection pools for different user types and applications running simultaneously on the server. An example... one connection pool to authenticate user login attemps that connects to a security database. A second connection pool to the application that has specific permissions granted to the connection. Depending on the application, following MVC, you can put checks in the view(V) that limit the types of data and you can put more checks in the model (M) that limit what types of data. (Hint: Think SQL Injection attacks). Specific to your example, you either have to modify your application where you pass in a portion of the user's credentials, or you don't use connection pooling. With respect to Fernando's earlier post, you have to think about the security risks if you can change your connection details within the pool. Not a good idea. K? > Date: Mon, 15 Jun 2009 12:13:30 +0100 > From: mc@community.nospam > Subject: Re: Connection Pooling with the .NET Provider on IIS > To: informix-list@iiug.org > > But the problem is the Connection pool is letting someone else use your already authenticated user > session. > > Scenario: > User A Hits Website, his credentials are passed through to asp.net and on to the Informix .NET > Provider which access database and returns results based on the Current "USER". User B Hits Website > (shortly after User A) his credentials are passed through to asp.net and on to the Informix .NET > Provider this grabs a connection from the pool (previously used by User A) the provider access the > data as User A and returns the results to User B. Thus a breach of confidentiality occurs. > > Do you see the problem? > > For me there are only two acceptable modes of operation. > > Re-use Connection A but with the credentials of User B. > Block the re-use (by anyone other than User A) of the Connection created for User A > > I'm still assuming the only safe way to operate is to disable connection Pooling? > > TIA > > > MC > > Ian Michael Gumby wrote: > > Isn't that the purpose of a connection pool? > > > > I mean the point is that you're reusing the connection so you don't > > have to constantly authenticate the user. ;-) > > > > And to answer Fernando, re-setting authenticated data on the fly is > > not a good thing. Nor would you want it. > > > > -G > > > > > > > Date: Sat, 13 Jun 2009 11:55:36 +0100 > > > From: mc@community.nospam > > > Subject: Re: Connection Pooling with the .NET Provider on IIS > > > To: informix-list@iiug.org > > > > > > I could understand that for "Open Connections" however it seems a > > little strange that after I have > > > Closed the connection the "Security Sub System" of the Informix > > Drivers allows that connection to be > > > reused. Isn't it a massive security hole? Am I right in assuming > > that the only solution to this > > > issue is to turn off Connection Pooling? > > > > > > Regards > > > > > > > > > MC > > > > > > Fernando Nunes wrote: > > > > mc wrote: > > > >> I have developed an Intranet IIS web application that is > > configured to > > > >> use Windows Authentication against our Active Directory. The > > sites run > > > >> with identity impersonation so all connections against the database > > > >> SHOULD use the the credentials of the original user who > > requested the > > > >> web page. > > > >> > > > >> Initially this appeared to be working correctly. If you have > > > >> infrequent visitors or many hits from the same user everything > > appears > > > >> fine however if you get visits from multiple identities in quick > > > >> succession the second and third connections re-use the > > credentials of > > > >> the first connection. > > > >> > > > >> Adding "Pooling=False" to the Connection string resolves this > > issue, > > > >> however I fear we will run into performance issues when we > > deploy the > > > >> system to our live servers. > > > >> > > > >> Has anyone seen this issue before? Is this a bug in the Provider? I > > > >> have never seen this with other (SqlClient) .NET Data Providers. > > > >> > > > >> TIA > > > >> > > > >> > > > >> MC > > > > > > > > Currently you cannot change the user credentials on an already > > > > established database connection. > > > > This could change in the future, but currently it's not supported. > > > > Regards. > > > > > > > > > > _______________________________________________ > > > Informix-list mailing list > > > Informix-list@iiug.o