Help and Support
 

powered byLive Search

SQL application role errors with OLE DB resource pooling

View products that this article applies to.
Article ID:229564
Last Review:February 15, 2007
Revision:4.3
This article was previously published under Q229564
On This Page

SYMPTOMS

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:
General network error

Back to the top

CAUSE

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.

Back to the top

WORKAROUND

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.

Back to the top

MORE INFORMATION

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

Back to the top

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:
http://msdn2.microsoft.com/en-us/library/ms810829.aspx (http://msdn2.microsoft.com/en-us/library/ms810829.aspx)
For more information about how to turn off OLE DB services, see the OLE DB Readme.txt file.

Back to the top

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

Back to the top

Keywords: 
kbfaq kbpending kbprb KB229564

Back to the top

Article Translations

 

Related Support Centers

Other Support Options

  • Need More Help?
    Contact a Support professional by Email, Online or Phone.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.