none
Linkes Server - MSDASQL Access denied error

    Question

  • Hi,

    I'm using SQL 2005 - I'm trying to access Lotus Notes data, and am trying to do it using a linked server. So far:

    I have an ODBC connection set up on the SQL server which uses IBM's NotesSql driver. The connection works, and if I try to use eg MS access to view the tables I can.

    I've also used the ODBC connection to create a linked server - that all works (ie, I can "Test Connection" and it succeeds) but if I try to view catalogs either via the SSMS GUI or with sp_catalogs I get the following:


    Msg 7399, Level 16, State 1, Procedure sp_catalogs, Line 7
    The OLE DB provider "MSDASQL" for linked server "swilson_portland" reported an error. Access denied.
    Msg 7301, Level 16, State 2, Procedure sp_catalogs, Line 7
    Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "MSDASQL" for linked server "swilson_portland".
    If I try to run the following query I get the same errors:

    select * from openquery(odbc_conn,'select Blah from tb1')
    I can't use 4-part convention because the NotesSql driver doesn't expose the schema references.

    If I try to (same) query with an incorrect column name then it tells me the column doesn't exist, so it must be able to see the tables.

    Can anyone suggest a way to resolve?

    Sam
    Wednesday, February 03, 2010 4:28 PM

All replies

  • Check to see if the provider has the "Allow In Process" option enabled.
    Wednesday, February 03, 2010 9:09 PM
  • Hi,

    With "Allow in Process" enabled I get a different error message when running:

    sp_catalogs swilson_portland

    Error as follows:

    OLE DB provider "SQL Server" for linked server "(null)" returned message "The parameter is incorrect.".
    OLE DB provider "SQL Server" for linked server "(null)" returned message "[Lotus][ODBC Lotus Notes]Driver not capable".
    Msg 7399, Level 16, State 1, Procedure sp_catalogs, Line 7
    The OLE DB provider "SQL Server" for linked server "(null)" reported an error. One or more arguments were reported invalid by the provider.
    Msg 7311, Level 16, State 2, Procedure sp_catalogs, Line 7
    Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider "SQL Server" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used.
    Thursday, February 04, 2010 8:31 AM
  • Here's the errors from the trace:
    <hresult>-2147024809</hresult>
    <inputs>
    	<pUnkOuter>0x00000000</pUnkOuter>
    	<rguidSchema>{C8B52211-5CF3-11CE-ADE5-00AA0044773D}</rguidSchema>
    	<cRestrictions>1</cRestrictions>
    	<rgRestrictions>
    		<VARIANT>
    			<vt>VT_EMPTY</vt>
    		</VARIANT>
    	</rgRestrictions>
    	<riid>IID_IRowset</riid>
    	<cPropertySets>0</cPropertySets>
    </inputs>


    <hresult>-2147467262</hresult>

    Sam

    Thursday, February 04, 2010 8:40 AM
  • Hi,

    With "Allow in Process" enabled I get a different error message when running:

    sp_catalogs swilson_portland
    
    

    Error as follows:

    OLE DB provider "SQL Server" for linked server "(null)" returned message "The parameter is incorrect.".
    OLE DB provider "SQL Server" for linked server "(null)" returned message "[Lotus][ODBC Lotus Notes]Driver not capable".
    Msg 7399, Level 16, State 1, Procedure sp_catalogs, Line 7
    The OLE DB provider "SQL Server" for linked server "(null)" reported an error. One or more arguments were reported invalid by the provider.
    Msg 7311, Level 16, State 2, Procedure sp_catalogs, Line 7
    Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider "SQL Server" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used.

    Take a look at what I have put in bold font.  It appears as though you do not have a servername specified for this parameter.  The provider doesn't know what your linked server is called, therefore does not connect.
    Thursday, February 04, 2010 4:48 PM
  • OK - any idea how I would specify a server name for this parameter?

    I'm not in the least bit sure which parameter it's referring to!

    Sam
    Friday, February 05, 2010 9:07 AM
  • Anyone got any ideas?
    Monday, February 08, 2010 11:23 AM
  • What version of the NotesSQL driver do you have?  8.5?
    Monday, February 08, 2010 7:01 PM
  • Yes - 8.5

    Sam
    Tuesday, February 09, 2010 8:07 AM
  • Try running through the ODBC Lotus Notes setup again, making sure that it has the name of the Domino server and database that you are trying to connect to.

    I can't think of any other reason why your provider would have these as "null", except that maybe that step was skipped when running through the ODBC Lotus setup.

    Let me know when you have verified this.

    Tuesday, February 09, 2010 3:26 PM
  • Hi, and thanks for trying to help.

    I've been through the ODBC set up again - domino server & database are all present and correct.

    Sam
    Tuesday, February 09, 2010 5:07 PM
  • What does your sp_addlinkedserver script look like?  (Thanks)
    Wednesday, January 11, 2012 6:53 PM