Impersonation and connection pooling

Question

Impersonation and connection pooling

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

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

sturner SSC-Insane Points: 22471 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: 22471 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