2

I have an Azure SQL Database and some C# line of business applications that use it. I have been working to reduce the attack surface and understand the performance better.

In the SQL Azure contained database I have:
1) A SQL Authentication user with a login called 'ReaderUser', with a fixed long and crazy password.
2) Users created in the database without login using: CREATE USER [] WITHOUT LOGIN
3) Tables with data, secured by USER and Row Level Security. ReaderUser cannot access any of the data, it is merely a "Proxy" user.
4) ReaderUser can impersonate any other non-administrative user (of the variety created "without login"). Data is only ever read by any of them.

When our developed application/s are accessed by users, the database connection is made with ReaderUser. Applications use a .Net 4.6.2 ADO.net connection. The connection string is encrypted and fixed. I.e. it doesn’t change for any user, they all use the same connection string.

When an application user signs in, the logged in application identity (Active Directory UPN) is then passed to the database to set the correct user context using: 

EXECUTE AS USER = 'myuser@mydomain.com' WITH NO REVERT;

Users then access the application and only see their data, as constrained by the row level security.

My question is, how will the connections be pooled? Will they be pooled per user or per the ReaderUser login (connection string)? Users may connect from multiple applications simultaneously using the same connection string. I have surfed around a few articles but I can't get a clear explanation on this specific scenario. Can anyone shed some light?

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
Murray Foxcroft
  • 12,785
  • 7
  • 58
  • 86

1 Answers1

5

Applications that desire to use EXECUTE AS with connection pooling must use the special cookie syntax:

When the WITH NO REVERT COOKIE = @varbinary_variable clause is specified, the SQL Server Database Engine passes the cookie value to @varbinary_variable. The execution context set by that statement can only be reverted to the previous context if the calling REVERT WITH COOKIE = @varbinary_variable statement contains the same @varbinary_variable value.

This option is useful in an environment in which connection pooling is used. Connection pooling is the maintenance of a group of database connections for reuse by applications on an application server. Because the value passed to @varbinary_variable is known only to the caller of the EXECUTE AS statement, the caller can guarantee that the execution context they establish cannot be changed by anyone else.

So your app is responsible for reverting the execution context, using the special cookie, before returning the connection into the pool.

Community
  • 1
  • 1
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • So if I use my scenario, with "EXECUTE AS USER = 'myuser@mydomain.com' WITH NO REVERT;" will I get no connection pooling, or will I get connection pooling and muddled up users? – Murray Foxcroft Sep 09 '16 at 07:49
  • You *cannot use pooling* with EXECUTE AS .. NO REVERT, unless you use these cookies. What you'll get is unpredictability. Is not that .Net can second guess your actions. Is *you* that specify pooling (via the connection string). If you specify pooling *and* you use EXECUTE AS, then the results are undetermined. – Remus Rusanu Sep 09 '16 at 08:26
  • 1
    You have to understand that `EXECUTE AS ...` occurs long after you already opened the connection, so pooling is already in effect by then. Second, the .Net client library (which controls pooling) has no way of knowing that you issued an EXECUTE AS (think about it, it could be executed behind an opaque stored procedure invocation, how would the client library know it occurred?). So when you're done with the connection it will be returned to the pool and then attempt to be reused. This is when things will go haywire, in unpredictable ways. *You* are in control, you must do the right thing. – Remus Rusanu Sep 09 '16 at 08:30
  • Thanks - that unpredictability is what I wanted to confirm. Very helpful. So, to be predictable with EXECUTE AS WITH NO REVERT then one must specify Pooling=false in the connection string. Alternatively, one can EXECUTE AS (WITH REVERT) and ensure REVERT is called before closing the connection (which is flaky since exceptions or connectivity issues could prevent the REVERT always getting called). So, in short, for reliable EXECUTE AS, it is best to NOT pool and use the extra protection of NO REVERT. Agree? – Murray Foxcroft Sep 09 '16 at 09:17
  • 1
    Agree. Keep in mind though that is you encounter a connectivity issue, the connection will not return to the pool since it will be closed. The risk is if you have a bug in the code that calls `connection.Close()` w/o properly reverting. But I agree this is a fairly big risk. I would remove pooling, and only add it it back on critical call path, if proven to increase perf significantly, and only for well understood behavior where issuing the correct revert with cookie can be guaranteed (good luck meeting all the criteria! :) ) – Remus Rusanu Sep 09 '16 at 09:52
  • BTW, rather than specifying `Pooling=false` in the string, I personally much prefer to use a [`SqlConnectionStringBuilder`](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder(v=vs.110).aspx) – Remus Rusanu Sep 09 '16 at 09:54
  • Thanks - very helpful and the clarification I needed. I have accepted your answer. For this (my scenario), I will avoid pooling, at least until Microsoft sort out their federated auth (you may find this interesting: http://stackoverflow.com/questions/39367634/sql-azure-integrated-authentication-with-a-cloud-only-azure-active-directory-fai). – Murray Foxcroft Sep 09 '16 at 10:48