Sql-server – Shared connection pool in SQL Server and prevention of cross-database queries

connection-poolingsql server

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"?

Best Answer

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:

you are changing both the security credentials AND the "Database" / "Initial Catalog" across connections
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.

Related Solutions

Sql-server – Using View filters for tenants in SQL-Server 2008

I'd use SUSER_SID for this reason:

SUSER_ID is the principal_id column in sys.server_principals
SUSER_SID is the SID column in sys.server_principals and be generated by Active Directory (Windows login) or SQL Server

If you move the database to another server

then SUSER_ID will almost certainly be different.
SUSER_SID will be the same for Windows logins, but can be made the same for SQL logins by specifiying SID for CREATE LOGIN.

Sql-server – SQL Server ‘Create Database’ statement. How to inherit autogrowth settings

For SQL Server 2005+ You can use the following:

SELECT FILE_ID, type, type_desc, 
CAST( CAST( (CASE WHEN size < 128 THEN 128 ELSE size END) * 8. / 1024. AS INT ) AS VARCHAR(20) ) + 'MB' AS SIZE, 
CASE max_size WHEN -1 THEN 'UNLIMITED' ELSE CAST( CAST( max_size * 8. / 1024. AS INT ) AS VARCHAR(20) ) + 'MB' END  AS MAXSIZE,
CASE is_percent_growth
    WHEN 0 THEN CAST( CAST( growth * 8. / 1024. AS INT ) AS VARCHAR(20) ) + 'MB'
    WHEN 1 THEN CAST( growth AS VARCHAR(30) ) + '%'
END AS FILEGROWTH
FROM sys.master_files
WHERE database_id = DB_ID('model')
GO

This will retrieve File definitions for Model Database.
Later on You can use it either in CREATE DATABASE or ALTER DATABASE.

SSMS actually uses SMO to retrieve these parameters and then creates a Script which looks like:

CREATE DATABASE [aaa] ON  PRIMARY 
( NAME = N'aaa', FILENAME = N'D:\Data\aaa.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'aaa_log', FILENAME = N'D:\Logs\aaa_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

If Your Application is creating the Database and You can't modify the application -
then You have to use ALTER DATABASE after creation is done:

ALTER DATABASE [aaa] MODIFY FILE ( NAME = N'aaa', MAXSIZE = 102400KB )
GO
ALTER DATABASE [aaa] MODIFY FILE ( NAME = N'aaa_log', FILEGROWTH = 10240KB )
GO

This requires at least ALTER DATABASE Permission
(implied by ALTER ANY DATABASE Server Permission)
Obviously, You can generate that script using the first code sample (FROM sys.master_files).

I wouldn't use sysaltfiles for SQL Server 2008R2 because it is Deprecated.

Good luck,
Roi