Post reply

Using OPENDATASOURCE as an ad hoc Linked Server

  • December 30, 2021 at 9:59 pm

    #3968545

     

    I'm not sure if this should be posted here or not, but I will try.

    We have a configuration where multiple customer databases are distributed across several servers. On occasion (not frequently), it is necessary to compare data between two databases on the same table utilized by the application.  Because there are several Servers I don't want to create a Linked Server each time this is necessary. Each Server would need 10 Linked Servers configured each so that they were able to connect to each other. Also, for security purposes, I do not want to have a catalog of databases available all the time.

    I will start with a simple query, because none of them seem to work. When I run this:

    SELECT * FROM OPENDATASOURCE('MSOLEDBSQL', 'Server=SERVER.domain.local;Database=My_DB;TrustServerCertificate=Yes;Trusted_Connection=True').[dbo].

    I get the error:

    Msg 208, Level 16, State 1, Line 3
    Invalid object name 'dbo.Table'.

    If I run:

    SELECT * FROM OPENDATASOURCE('SQLNCLI','Data Source=SERVER.domain.local;Integrated Security=SSPI').My_DB.dbo.table

    Then I get:

    Msg 15281, Level 16, State 1, Line 5
    SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

    I figured that maybe something was not set correctly. After a bit of digging I found that I should ensure that "ad hoc distributed queries" needs to be enabled. Did that by running:

    EXEC sp_configure 'show advanced options', 1
    RECONFIGURE
    GO
    EXEC sp_configure 'ad hoc distributed queries', 1
    RECONFIGURE
    GO

    Also, as recommended here I found that I also need to ensure that "Disallow adhoc access" needs to set correctly. In this case, disabled so that it is allowed. And the Windows Registry needed to be updated to ensure that the "DisallowAdhocAccess" key is set to 0, which it already was for the Providers that I attempted to use.

    In my mind it seems like MSOLEDBSQL is getting a connection, but is unable to select from the object? For SQLCLI the Server configuration seems correct, but this still does not work and I'd prefer to use MSOLEDBSQL since Microsoft indicated that SQLCLI is deprecated.

    I know that a Linked Server would work. However, I am trying to avoid that if possible in favor of building these connections at run time.

    Things that I have tried:

    • Adding User ID= ;Password= to the connection string (with SQL Login credentials)
    • Changing all of the ad hoc access settings on the Server where the Database that I am attempting to query resides.
    • Ensuring that DTC is running. (MSDTC is not there as this is not a Cluster)

    In an ideal world this would work with the Windows Credentials a user has already authenticated within SSMS. I think that I've got the syntax correct based on what I have read.

    Oh, before I forget, all of the databases are running on SQL Server 2019 CU09 with compatibility level 100 (SQL Server 2008).

    Thanks in advance.

    Regards, Irish 

  • December 31, 2021 at 10:10 pm

    #3968824

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • January 4, 2022 at 8:04 pm

    #3969877

    Giving this one a bump.

    Using a Linked Server is the last option that I want to employ for a variety of reasons. Likewise, we could copy the source database to the destination SQL Server so that any of the data comparisons could be completed there, but then the data is "stale".

    I'm hoping for any other ideas.

    Regards, Irish 

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply