Impersonation and connection pooling

Question

Impersonation and connection pooling

spaghettidba

SSC Guru

Points: 105732
More actions
July 25, 2013 at 4:04 am

#277939

Hi,
I'm working on a project to improve the application and SQL Server security.
We have some applications that access the server using SQL Server authentication and each application uses credentials that are stored in a table (OMG!) in clear text.
A first connection is issued to retrieve the credentials (each application has its own credentials) and then a second connection is opened using those credentials.
We can implement Windows Authentication, but I wanted to let people write on the database tables only when using the application.
Application roles are not an option: some applications have to read and write data on multiple databases and I don't want to mark any database as trustworthy.
The alternative I was planning to implement relied on the same SQL Server logins is use today, but not used directly. Basically, I wanted to grant IMPERSONATE permissions on specific Windows Groups and then issue a EXECUTE AS LOGIN as soon as a connection is open.
This works great in SSMS, but it breaks in the application when connection pooling is used.
As soon as I close the connection and return it to the pool, the connection gets dropped.
The error I get is:
The connection has been dropped because the principal that opened it subsequently assumes a new security context, and then tried to rest the connection under its impersonated security context. This scenario is note supported. See "Impersonation Overview" in Books Online.
There used to be a property in the connection string to avoid resetting the connection (Connection Reset=false) but now it seems to be obsolete and removed: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.connectionreset.aspx
I'm running out of options, any help would be greatly appreciated.
-- Gianluca Sartori

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

If you think about, it has to work that way or impersonation would then have a huge security hole in it.

You should consider using a key server scenario (tier) to store your credentials as well as encryption keys , certificates and so forth.

The probability of survival is inversely proportional to the angle of arrival.

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Thanks for your help.

I understand the reason of this restriction, but it makes using impersonation a bit impractical.

I think that my only option is to keep using SQL Authentication and set up something to encrypt and secure my credentials.

-- Gianluca Sartori

sturner SSC-Insane Points: 22411 More actions · Answer 3

spaghettidba (7/25/2013)
I think that my only option is to keep using SQL Authentication and set up something to encrypt and secure my credentials.

Exactly. You could even use a stripped down instance of SQL server with a clean SP interface for clients that issues credentials and keys based on request code and also looking at the IP address etc. as well as logging requests.

But a dedicated key server (authentication tier) would be faster and have lower overhead on the connections than a SQL server instance and could scale out better.

The probability of survival is inversely proportional to the angle of arrival.

rageshpayyan Valued Member Points: 70 More actions · Answer 4

Hi,

Have you fixed this issue. I am facing the same issue.

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

There's no "fix" in this case. That's how impersonation works.

I ended up using SQL Server Authentication. Would it work for you?

-- Gianluca Sartori

rageshpayyan Valued Member Points: 70 More actions · Answer 6

I will explain to what i am doing hre.

i have implemented multitenent , using single database connection string. Each client has its own user(without loin) and user. to access client records we switch user context using SQL command "Execute As user=" . But this is causing the error message . Here is the implementation.

Public Sub Open()

If m_Conn Is Nothing Then

m_Conn = New OdbcConnection

End If

m_Conn.Open()

ClientName = "foo_user"

ExecuteNonSQL("Execute as user = '" & ClientName & "'")

End Sub

Public Sub Close()

ExecuteNonSQL("Revert;")

m_Conn.Close()

m_Conn = Nothing

End Sub

i can see the above error in event viewer and can see "exec sp_reset_connection" in profile.

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

You probably have some code paths that allow connections to be closed before reverting to the original login.

Not reliable IMHO.

-- Gianluca Sartori