We have clients that are using SQL Server 2008 R2, 2012, 2014, 2017 and 2019, and I'm facing a situation where we're going to need to ask them to manually create registry entries on their database servers to enable AdHoc query functionality for Linked servers for non-administrators. We believe this is the final step for getting them the functionality they need, but the lack of very clear information on where this missing registry key needs to be placed is disconcerting. Assistance in identifying the correct location(s) is requested. We will need a proven and authoritative answer that will work for the above servers if we're going to be successful in petitioning our clients for this modification. Thank you in advance.
History:
Although this question is similar to Query Extended LDAP Server from SQL Server, we encountered and resolved that one already (we ran the Linked Server creation script as an Administrator.) However, even if you manage to accomplish EXEC master.dbo.sp_addlinkedserver and EXEC master.dbo.sp_addlinkedsrvlogin, you will face another problem: you won't be able to actually use OPENROWSET or OPENDATASOURCE unless you're a database administrator.
You can create a registry setting on the database server that enables this AdHoc functionality. However, as this is on our client's database server, which also serves as testbeds for other software, I am very hesitant to request that they take steps that seem to be in place for improved security.
The instructions in the link indicate that if the registry keys are missing, they may need to be added. Here is where I encounter my biggest problem.
On neither of the two servers I have tested this on so far, have I been able to locate any registry settings for our newly-created Linked Server, and therefore, I have no clue exactly what registry settings to create. There is no way I am going to be asking a client to drop new keys in 3 or 4 locations, because one of them may work. When we have completed the testing of this, we will apply to have the same changes made on their Production servers, so we need to know exactly what is going to work.
The command used to successfully create the linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=@LDAPUser,@rmtpassword=@LDAPPassword
Searching for DisallowAdHocAccess in Google returns that the necessary key needs to be located in one of the following locations (from the above link:)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers<ProviderName>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\Providers\<ProviderName>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQLServer\MSSQL<major version>_<minor version>.<instance name>\Providers\<ProviderName>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQLServer\MSSQL_.\Providers\<ProviderName>
Our Windows Server 2012 R2 Standard Registry listing showing the HKLM\SOFTWARE\Microsoft\Microsoft SQL Server tree, cannot locate our Linked Server Provider.
