February 22, 2007 at 10:06 am
I am implementing an application role for an app written in VB6 talking to a SQL2000 database. The app opens a connection under ADODB 2.5 (version required by other apps in the environment) and runs sp_SetAppRole.
Dim cmd As New ADODB.Command
With cmd
Set .ActiveConnection = cnn
.CommandType = adCmdText
.CommandText = "EXEC sp_SetAppRole <RoleName>', '<Password>'"
.Execute
End With
Set cmd = Nothing
The connecton string for testing (using Windows security) is :
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog==<not telling>;Data Source=<not telling>;
This works fine - the first time. The app closes the connection and sets it to nothing. Later, it opens a new connection. Does the same thing ... and so on. On one of these subsequent occassions, I get a 'Connection Failure' error.
I have established that the problem is connection pooling. If the pool tries to reuse a connection where the approle already has been set, SQLServer throws an exception and that connection is trashed. The solution is to switch off connection pooling. This works but is a rather drastic workaround. The modified connection string is :
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog==<not telling>;Data Source=<not telling>;OLE DB Services=-2;
Since all data access is through stored procedures, a reasonable alternative is to use a very restricted SQL Server account. I would be interested in any comments or suggestions as to these or any alternative approaches.
February 23, 2007 at 3:32 pm
Switching off pooling is drastic. The first tip is to add something unique, such as an ApplicationName attribute, to the connection string so the app role connections won't be pooled with other connections to the same server.
Open a connection, then execute "SELECT USER_NAME()" to see if the connection is already in the app role. If so, fine, if not, execute sp_setapprole.
This code is in a Try-Catch block because I found sometimes just executing "SELECT USER_NAME()" would die with a "General network error" exception and close the connection. So there is a "Do ... Loop Until cnn.State > ConnectionState.Closed" loop around the try-catch block. The Catch part of the try-catch is empty, if the General network error closes the connection it just loops back and tries again. When I step through this code, if the error occurs it successfully connects to the pooled connection in the app role on the second attempt, so I haven't added any more elaborate error checking or limited the number of connection retries. Feel free to add more exception handling if you want something more robust, but this has been working for us for a few years now.
February 23, 2007 at 3:52 pm
After posting I noticed you were using VB6. I may be a little rusty, but I think this is what it would look like.
Dim nRetries as Integer
nRetries = 5
Do
On Error Goto ConnectError
cnn.Open sConnectionstring
If cnn.Execute("SELECT USER_NAME()")(0) <> "approle" Then
cnn.Execute("sp_setapprole 'approle','apppwd'",,adCmdText + adExecuteNoRecords)
End If
TryAgain:
On Error Goto 0
nRetries = nRetries - 1
Loop Until cnn.State > adStateClosed Or nRetries <= 0
Exit Sub
ConnectError:
Resume TryAgain
February 26, 2007 at 1:59 am
[ I found sometimes just executing "SELECT USER_NAME()" would die with a "General network error" exception and close the connection ]
I tried this and got the same problem. I haven't tried your loop but would be concerned at the overhead of all those retries. As all access is through stored procedures anyway, we've decided to go for a highly restricted SQL server login instead.
It looks like there may be a solution in SQL 2005 though :
http://solidqualitylearning.com/Blogs/dejan/archive/2006/11/10/3487.aspx
Thanks for your response; much appreciated.
February 26, 2007 at 7:39 am
What overhead? In my testing I only saw it require one retry at most.
I coded it with no retry limit, if there was a serious issue it would be an infinite loop. We have not seen that occur once in several years of use.
February 26, 2007 at 7:48 am
Just a concern - quite possibly unfounded - just that I always like to minimise network traffic. You've obviously come up with an effective workaround. However, given the application, we feel we have adequate security utlising a highly restricted SQL Server login and keeping things simple. Thanks for your time and help though - and we may well use your approach in future for a more critical scenario.
February 26, 2007 at 8:03 am
Our application is batch oriented, so the connection time and network traffic is miniscule compared to the work performed. In a transactional application, web application, or something else involving frequent connections, I would probably be more concerned about connection overhead.
February 26, 2007 at 11:58 am
Scott, that is interesting. I'll have to think more about the implications before commenting more!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply