Connection Pooling
-
anju wrote: To implement connection pooling the connection strings must be identical.(am I right?). Yes. That is correct. anju wrote: If this is the case,for different UserIds and Passwords the connection string is always different,then how connection pooling will be helpful?. Because you create a connection, use the connection and then close the connection. Next time you access the database you repeat the create, use, close routine again. The second time round the connection will be in the pool and you get it quickly.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
anju wrote: Do we need to compramise about security? No.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
anju wrote: Do we need to compramise about security? No.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
As I have said already: Connection pooling is just a way that a .NET application re-uses the same connection to the database in different parts of an application. The connection must be the same as an existing connection in the pool, if not then a new connection is created. It is NOT a way to manage security. It is a way to repeatedly use the same connection to the database.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
As I have said already: Connection pooling is just a way that a .NET application re-uses the same connection to the database in different parts of an application. The connection must be the same as an existing connection in the pool, if not then a new connection is created. It is NOT a way to manage security. It is a way to repeatedly use the same connection to the database.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
Thank you for your quick reply. What I am looking is .... To implement connection pooling the connection strings must be identical.(am I right?).If this is the case,for different UserIds and Passwords the connection string is always different,then how connection pooling will be helpful?. anju
anju wrote: for different UserIds and Passwords the connection string is always different Yes it is, but, then you don't use different user IDs/passwords in your application. If your application needs logins, you create a table in your database with the user name and passwords *for your application*. Then you create one single SQL Server user that only has access to run stored procedures on your application's database. This is the user ID you use everywhere in your application to access your database (of course, you must have stored procedures to access all tables.) When a user logs in your application, you find out in your database using the stored procedures what permissions that user has, and selectively enable/disable parts of your application. But you only should have one database user ID/password, and use that in your connection string (never
sa). This way, all the connection strings will be the same, and connection pooling will work fine. -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
-
anju wrote: for different UserIds and Passwords the connection string is always different Yes it is, but, then you don't use different user IDs/passwords in your application. If your application needs logins, you create a table in your database with the user name and passwords *for your application*. Then you create one single SQL Server user that only has access to run stored procedures on your application's database. This is the user ID you use everywhere in your application to access your database (of course, you must have stored procedures to access all tables.) When a user logs in your application, you find out in your database using the stored procedures what permissions that user has, and selectively enable/disable parts of your application. But you only should have one database user ID/password, and use that in your connection string (never
sa). This way, all the connection strings will be the same, and connection pooling will work fine. -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
Luis Alonso Ramos wrote: But you only should have one database user ID/password, and use that in your connection string (never sa). Actually, I'd use windows authentication so that the user and password aren't stored anywhere and don't have the possibility of being discovered.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
Luis Alonso Ramos wrote: But you only should have one database user ID/password, and use that in your connection string (never sa). Actually, I'd use windows authentication so that the user and password aren't stored anywhere and don't have the possibility of being discovered.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
I'd agree with that in general, but there are times that it's not practical, for example if you have a service that's running as SYSTEM with the database on another machine. Yes, I know that it's bad practice to have a service running as SYSTEM unless absolutely necessary - unfortunately this is a legacy service with UI and therefore has to run as an interactive service, hence it has to run as SYSTEM. It's useful to note that you don't need a domain to set this up, but if you don't have one, you'll need to create user accounts for the appropriate users on the server hosting SQL Server, and add them to SQL Server's logins. You then need to synchronize the passwords for those user accounts between the machine that the client runs on and the server hosting SQL. One thing you can do with Windows Authentication that you can't with SQL Server Authentication is to set up groups of users with particular permissions. Stability. What an interesting concept. -- Chris Maunder
-
I'd agree with that in general, but there are times that it's not practical, for example if you have a service that's running as SYSTEM with the database on another machine. Yes, I know that it's bad practice to have a service running as SYSTEM unless absolutely necessary - unfortunately this is a legacy service with UI and therefore has to run as an interactive service, hence it has to run as SYSTEM. It's useful to note that you don't need a domain to set this up, but if you don't have one, you'll need to create user accounts for the appropriate users on the server hosting SQL Server, and add them to SQL Server's logins. You then need to synchronize the passwords for those user accounts between the machine that the client runs on and the server hosting SQL. One thing you can do with Windows Authentication that you can't with SQL Server Authentication is to set up groups of users with particular permissions. Stability. What an interesting concept. -- Chris Maunder
Mike Dimmick wrote: I'd agree with that in general, but there are times that it's not practical Or if the SQL Server is being accessed from a Unix machine. Just to give another example. Maybe I should have said "where ever practical, use Windows Authentication". :-D
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
Luis Alonso Ramos wrote: But you only should have one database user ID/password, and use that in your connection string (never sa). Actually, I'd use windows authentication so that the user and password aren't stored anywhere and don't have the possibility of being discovered.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
Colin Angus Mackay wrote: Actually, I'd use windows authentication Well, yes. But if he were using Windows Authentication, then he wouldn't be having problems with different connection strings: it would always be the same. But still, Mike's reply shows some situations where it isn't practical. In my current application, there's Windows Server with a MSDE and several other machines accessing it. The network is not configured with a domain, so its easier to just use
sawith a blank password. Just kidding, I have a SQL server user with limited permissions and I use SQL login info and store the connection string encrypted. ;P -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!