non-sa users get access denied in OLE DB query

On SQL Server 2005, 64 bit, this query:
SELECT * FROM OPENROWSET('IBMDADB2', 'DSN=DB2T;', 'SELECT SCAC_CODE,
SCAC_NAME,SCAC_CITY, SCAC_ST, SCAC_STATUS FROM
MISDB2A.FASMSCAC_STD_CARRIER_CODE')
Gets this message:
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must access
this provider through a linked server.
I works if it is run by the sa login, or by a login that has been granted
the sysadmin Server Role.
How can I permission non-sa logins to access the OLE DB provider?Ad-hoc remote queries are disabled by default in SQL 2005. Check the
"Surface Area Configuration for Features (Ad Hoc Remote Queries) - Database
Engine " topic in BOL
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/fe754945-2cd2-4cc1-8ae1
-4de01907b382.htm).
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Stu" <Stu@.discussions.microsoft.com> wrote in message
news:0896B540-3262-42A8-8D4E-97D900C35A91@.microsoft.com...
> On SQL Server 2005, 64 bit, this query:
> SELECT * FROM OPENROWSET('IBMDADB2', 'DSN=DB2T;', 'SELECT SCAC_CODE,
> SCAC_NAME,SCAC_CITY, SCAC_ST, SCAC_STATUS FROM
> MISDB2A.FASMSCAC_STD_CARRIER_CODE')
> Gets this message:
> Msg 7415, Level 16, State 1, Line 1
> Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must
> access
> this provider through a linked server.
> I works if it is run by the sa login, or by a login that has been granted
> the sysadmin Server Role.
> How can I permission non-sa logins to access the OLE DB provider?|||It has been turned on!
With it left off, EVERYBODY gets this mssage:
Msg 15281, Level 16, State 1, Line 1
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 administrato
r
can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. Fo
r
more information about enabling 'Ad Hoc Distributed Queries', see "Surface
Area Configuration" in SQL Server Books Online.
But turned on, uness you have sysadmin privs, you get this message:
Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must access
this provider through a linked server.
So, with ad hoc turned on, how do you permission non-sa logins to access the
OLE DB provider?|||Stu (Stu@.discussions.microsoft.com) writes:
> But turned on, uness you have sysadmin privs, you get this message:
> Msg 7415, Level 16, State 1, Line 1
> Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. You must
> access this provider through a linked server.
> So, with ad hoc turned on, how do you permission non-sa logins to access
> the OLE DB provider?
Books Online gives us this little tidbit:
OPENROWSET can be used to access remote data from OLE DB data sources
only when the DisallowAdhocAccess registry option is explicitly set to
0 for the specified provider, and the Ad Hoc Distributed Queries
advanced configuration option is enabled. When these options are not
set, the default behavior does not allow for ad hoc access.
i search the registry for DisallowAdhocAccess and I found that under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL Server\MSSQL.1\Providers
there is a whole list of providers. Of these SQLNLCI has DisallowAdhocAccess
set to 0, but the others not. I did a test with a plain-test user, and
it was able to use OPENROWSET with SQLNCLI, but not with MSDASQL that
did not have DisallowAdhocAccess listed.
So that seems to be the key.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> i search the registry for DisallowAdhocAccess and I found that under
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
> Server\MSSQL.1\Providers
> there is a whole list of providers. Of these SQLNLCI has
> DisallowAdhocAccess
> set to 0, but the others not. I did a test with a plain-test user, and
> it was able to use OPENROWSET with SQLNCLI, but not with MSDASQL that
> did not have DisallowAdhocAccess listed.
Yes, you nailed it. In BOL, it also stands "This option controls the ability
of non-administrators to run ad hoc queries. Administrators are not affected
by this option." In addition, there is a funny thing: you can set this
option to 1 through SSMS (Server Objects - Linked Servers - Providers -
properties of a specific provider). When you check the checkbox, SSMS
creates the registry key. However, you cannot set it to 0 with SSMS. When
you uncheck the checkbox, SSMS deletes the key. So the only option to set it
to 0 is with registry editor (ok, or with SMO, I guess). Not very
recommendable.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||Dejan Sarka (dejan_please_reply_to_newsgroups.sarka@.avtenta.si) writes:
> Yes, you nailed it. In BOL, it also stands "This option controls the
> ability of non-administrators to run ad hoc queries. Administrators are
> not affected by this option." In addition, there is a funny thing: you
> can set this option to 1 through SSMS (Server Objects - Linked Servers -
> Providers - properties of a specific provider). When you check the
> checkbox, SSMS creates the registry key. However, you cannot set it to 0
> with SSMS. When you uncheck the checkbox, SSMS deletes the key. So the
> only option to set it to 0 is with registry editor (ok, or with SMO, I
> guess).
Well, Mgmt Studio uses SMO, so I would not put my bets on that one.
In my opinion this is a bug. Someone has apparently not understood how
this provider option works. I've submitted
https://connect.microsoft.com/SQLSe...=2574
94
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm working now.
Thanks guys, for your research and feedback!|||Voted:-)
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98D4B72B433ADYazorman@.127.0.0.1...
> Dejan Sarka (dejan_please_reply_to_newsgroups.sarka@.avtenta.si) writes:
> Well, Mgmt Studio uses SMO, so I would not put my bets on that one.
> In my opinion this is a bug. Someone has apparently not understood how
> this provider option works. I've submitted
> https://connect.microsoft.com/SQLSe...=25
7494
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx