4

Microsoft recommends that a shared connection pool should be used when running e.g. a SaaS software with one database per customer but with a shared codebase to prevent pool fragmentation: https://msdn.microsoft.com/en-us/library/8xx3tyca%28v=vs.110%29.aspx

When taking out a connection from the pool the database to access should be first be changed by the command "USE Database_A".

However, as I see it, all the databases would then use the same login with let's say the db_datareader - role right.

Is there any easy way to prevent the user from making cross-database queries or directly querying other databases using a syntax like "SELECT * FROM Database_B.dbo.Person"?

  • The easy way to prevent Cross-Database queries would be to use contained databases :) – Spörri May 21 '15 at 9:33
  • Thanks for the tip, I've been thinking of checking out contained databases as a solution to the problem. – brunnsbe May 22 '15 at 13:27
  • @Spörri There are certainly pros (and cons) to Contained Databases, but I don't see how that addresses the issue of reducing the number of Connection Pools: you still need to alter the ConnectionString in some fashion to differentiate between the DBs or contained UserIDs. – Solomon Rutzky May 22 '15 at 16:43
  • Can you use EXECUTE AS to switch to some user (msdn.microsoft.com/en-us/library/ms181362.aspx)? Just an idea, I know nothing about it. – usr May 22 '15 at 20:09
  • @usr if you set it up so that a single Login can do EXECUTE AS to any of the other Logins that are specific to each DB, then a user can issue REVERT, query for other DBs and other Logins, then issue EXECUTE AS to one of the other Logins and USE the DB that they are allowed to connect to. It would only provide the appearance of security, but no real security. – Solomon Rutzky May 23 '15 at 6:32
2

How Connection Pooling works

The issue with connection pool fragmentation is that each connection pool is bound to the exact text of the Connection String, including the SID of the Windows Login when using a Trusted Connection / Integrated Security. The connection pool works because the connection is exactly the same in every way.

Hence, changing the database after connecting doesn't change anything from a security perspective. The advice to change the database after connecting (which can also be done via the SqlConnection.ChangeDatabase method), is to get around the fact that putting the "Initial Catalog" (or "Database") key/value pair into the Connection String means that those connections aren't using the exact same connection string.

With the above in mind, whether you are using Windows Logins (i.e. Trusted Connection / Integrated Security) or SQL Server Logins (specifying UserID & Password OR using a SqlCredential),

  • if the web app connects as the same Login for all requests, then as long as you don't specify the database in the ConnectionString (or at least always specify the same database), and only change the database context after making the connection, then you will get the most re-use out of connection pooling since all connections will be able to use the same pool. Else you will get a pool per Database.

  • if the web app connects with different Logins, then:

    • in the one-database-per-client model (assuming they are only allowed to connect to their database, hence using a different Login per client), you can either always specify the same database for that Login, or not specify any database in the ConnectionString and only change the database context after making the connection, and you will get a pool per Login (or per Database: they are kinda the same thing in this model).
    • in the multi-tenant model (assuming the app makes calls to multiple databases), as long as you don't specify the database in the ConnectionString (or at least always specify the same database), and only change the database context after making the connection, then you will get a pool per Login. Of course, if you specify the database in the ConnectionString, then you will get a pool per Login per Database!

What this all means in practical terms

No, you cannot have a single connection pool and "prevent the user from making cross-database queries". The very nature of a connection pool it is that it is the same connection because the connection is not closed. You cannot differentiate permissions when there is nothing to differentiate; the same Login is still logged in, just issuing another command.

Reducing the number of connection pools down to a single pool is at odds with the desire to have different permissions:

  • If you want to prevent a Login from accessing more than their database, or maybe confined to their database and any "shared" databases, that requires different Logins, and each one will have their own pool. If you go with Contained Databases, you still need to specify either the UserId & Password OR the Initial Catalog in the Connection String, and hence the result will still be one connection pool per each variation.

  • If you want to have just one connection pool, then you have no choice but to always use the same security credentials (regardless of them being based in Windows or SQL Server). And the same credentials means the same permissions.

The bottom line is, the wording of that MSDN article is a bit confusing / awkward, and you are over-estimating their advice. The issue of Connection Pool Fragmentation really shows up when:

  1. you are changing both the security credentials AND the "Database" / "Initial Catalog" across connections
  2. the app is multi-tenant (i.e. not one database per customer) such that each Login can connect to multiple Databases.

Having multiple Logins that connect to multiple Databases will give you more pools that are less frequently reused. Given that the unused connections linger for "approximately 4-8 minutes" (taken from the MSDN page you linked to, the "Removing Connections" section), this creates the inefficient situation of having too many idle connections.

But, having one Connection Pool per-Login OR per-Database is actually just fine.

Please note, however, that even with one Connection Pool per-Login OR per-Database, you can still experience too many idle connections if you have a lot of clients (e.g. Web Apps, back-end processes, etc.) hitting SQL Server from different servers.

  • Your answer explains how a shared connection pool works but doesn't answer the question how a cross database query could be prevented when a shared login is used (which was the original question). – brunnsbe May 22 '15 at 13:25
  • @brunnsbe I have updated my answer with more details. – Solomon Rutzky May 22 '15 at 16:20
0

You have to read it carefully. It means you want to have an sql user for each webapp but only one pool per user.

For example: if you have a forms authentication for webA(.. N) to the generic authentication database AuthDB and use userA(.. N) and(!) connect with userA(.. N) to the webA(.. N) DB you will get two pools for each webapp A..N because of the different connection strings.

  • On Microsoft's page: "The connection to the authentication database is pooled and used by everyone. However, there is a separate pool of connections to each database, which increase the number of connections to the server." They then suggest to have a single shared pool used for both authentication and for the separate databases. – brunnsbe May 22 '15 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.