PRB: SQL Application Role Errors with OLE DB Resource Pooling

ID: Q229564

The information in this article applies to:

Microsoft SQL Server version 7.0
Microsoft Data Access Components versions 2.0, 2.1

SYMPTOMS

When enabling Application Role for a SQL Server connection, you may get the following error:

"sp_setapprole was not invoked correctly. Refer to the documentation for more information."

The error occurs when sp_setapprole was called on a connection that was allocated from the resource pool. The same error occurs with the SQLOLEDB provider and SQL ODBC driver.

WORKAROUND

Turn off resource pooling, which ActiveX Data Objects (ADO) invokes by default. For example:


'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"

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 OLEDB 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 on how to turn off OLEDB services, refer to the OLE DB Readme.txt file.

(c) Microsoft Corporation 1999, All Rights Reserved. Contributions by Syed Yousuf, Microsoft Corporation.

Additional query words:

Keywords : kbMDAC kbOLEDB210 kbSQLServ kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbMDAC210SP2
Version : WINDOWS:2.0,2.1; winnt:7.0
Platform : WINDOWS winnt
Issue type : kbprb