none
SQL Server 2005 - Linked Server to Sybase gives Access Denied RRS feed

  • Question

  • We use a Sybase database and therefor also Linked Server.

    I have 5 x Windows Server 2003 SE SP2 installed with SQL Server 2005
    All have installed the same Sybase PC Client Components 12.5.4, which gives me a ASEOLEDB provider. On all ASEOLEDB provider, I have enabled "Allow inprocess".
    I got a script to add my Linked Server, to make sure they are setup the same way.

    On 3 of the servers the Linked Server work and I am able to expand it and look at my db table on Sybase.

    But on the last 2 servers I get this error:
    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    The OLE DB provider "ASEOLEDB.1" for linked server "LinkSybaseTyraTestDK" reported an error. Access denied.
    Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "ASEOLEDB.1" for linked server "LinkSybaseTyraTestDK". (Microsoft SQL Server, Error: 7399)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    I have read many other question about Linked Servers and Access Denied, but nothing have solved by problem yet.
    I have tried to locate a difference in the SQL Server's security, but with out luck.
    Even a re-installation of the SQL Server + Sybase driver and
    and Microsoft Update helps.

    Any one got a clue?
    I have run out :-)

    Regards
    Morten

    Friday, February 13, 2009 9:57 AM

Answers

All replies

  • Hi Morten,


    Please make sure all the linked servers use the provider "ASEOLEDB.1" . 
    The Steps are:

    1. Connect to SQL Server in SQL Server Management studio.
    2. Expand your instance name, then expand “Server Objects”, expand “Linked Servers”, expand providers.
    3. Right click on the “ASOLEDB” Provider and select “properties” - "Provider Options - Sybase OLEDB Provider" dialog box is displayed.
    4. Select you’re the linked server ("LinkSybaseTyraTestDK" ) in the "Linked servers using this provider".
    5. Check "Allow in process" and then click on "OK".


    If you have any more questions, please let me know.

    Thanks.

     


    ***Xiao Min Tan***Microsoft Online Community***
    Wednesday, February 18, 2009 10:59 AM
    Moderator
  • Hi Xiao-Min

    I doubled check the "Allow inprocess" was enabled and it already was.

    So I still have no clue....

    Do you ?

    Regards
    Morten

    Thursday, February 19, 2009 6:58 AM
  • Hi

    To fix the error, try to troubleshoot common problems based on the following KB article:
    http://support.microsoft.com/kb/280102

    In addition, make sure you have enabled network DTC access on both machines:
    http://support.microsoft.com/kb/817064


    If you have any more questions, please let me know.

    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Thursday, February 19, 2009 11:00 AM
    Moderator