Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 229564 - Last Review: February 15, 2007 - Revision: 4.3
SQL application role errors with OLE DB resource pooling
This article was previously published under Q229564
When you enable a SQL Server application role on a
Microsoft ActiveX Data Objects (ADO) connection to SQL Server, you may receive
the following error message when you connect to SQL Server 7.0:
sp_setapprole was not invoked correctly. Refer to the
documentation for more information.
When you connect to SQL Server
2000, the error may appear as follows:
[DBNETLIB][ConnectionRead (WrapperRead()).]General network error. Check your
network documentation
This error occurs when sp_setapprole is called
on an ADO connection that has been allocated from the OLE DB resource pool.
This error occurs with either the SQLOLEDB provider or the SQL Server ODBC
driver.
The limitation that is described in this article also exists
in the SQL Server .Net Data Provider, and in the OLE DB .Net Data Provider when
used with the SQLOLEDB OLE DB Provider. When you try to set an application role
on a connection that is drawn from the connection pool, you receive the
following error message:
In the current design, after an application role is enabled
on a client connection to SQL Server, you cannot reset the security context of
that connection. Therefore, when the user ends the SQL Server session and
disconnects from the server, the session is not reusable. However, OLE DB
resource pooling returns the closed connection to the pool, and the error
occurs when that connection is reused and the client application tries to reset
the connection's security context by calling sp_setapprole again.
The only available workaround is to disable OLE DB Resource
Pooling, which ADO uses by default. You can do this by adding "OLE DB Services
= -2" to the ADO Connection string, as shown here:
'For SQLOLEDB provider
'strConnect = "Provider=SQLOLEDB;server=SQL7Web;OLE DB Services = -2;uid=AppUser;pwd=AppUser;initial catalog=northwind"
' For MSDASQL provider
'strConnect = "DSN=SQLNWind;UID=Test;PWD=Test; OLE DB Services= -2"
Pooling can be disabled for the SQL Server .Net Data
Provider by adding "Pooling=False" to the connection string.
The following code reproduces the error:
Private Sub Command2_Click()
Dim adoCn1 As ADODB.Connection
Dim adoCn2 As ADODB.Connection
Set adoCn1 = GetConnection
Set adoCn2 = GetConnection
Set adoCn2 = Nothing
Set adoCn2 = GetConnection
Set adoCn2 = Nothing
Set adoCn2 = GetConnection 'Gives errors for both SQLOLEDB and ODBC
Set adoCn2 = Nothing
End Sub
Private Function GetConnection() As ADODB.Connection
Dim cn As ADODB.Connection
Dim sSQL As String
Dim strConnect As String
Set cn = New ADODB.Connection
'For OLE DB provider
strConnect = "Provider=SQLOLEDB;server=myServer;uid=AppUser;pwd=AppUser;initial catalog=northwind"
'Turn off Pooling ( all other services are enabled )
'strConnect = "Provider=SQLOLEDB;server=myServer;OLE DB Services= -2;uid=AppUser;pwd=AppUser;initial catalog=northwind"
'For ODBC driver
'strConnect = "DSN=SQLNWind;UID=Test;PWD=Test; "
'Turn off Pooling
'strConnect = "DSN=SQLNWind;UID=Test;PWD=Test; OLE DB Services= -2"
cn.ConnectionString = strConnect
cn.Open
sSQL = "sp_setapprole 'order_entry', 'password'"
cn.Execute sSQL
Set GetConnection = cn
End Function
REFERENCES
SQL Books Online; topic: "Application Security and Application
Roles"
For more information about disabling OLE DB services, see the
technical article "Pooling in the Microsoft Data Access Components," available
on MSDN Online:
For more information about how to turn off OLE DB services, see
the OLE DB Readme.txt file.
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft Data Access Components 2.0
- Microsoft Data Access Components 2.1
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
- Microsoft Data Access Components 2.8
| kbfaq kbpending kbprb KB229564 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate