MSSQL2000 and Linked Servers
Sten Westerback (MVP SDK 2005-6 :)
On a production system we haven't yet had the chance to update from MS SQL
Server 2000 (v 8.00.2039 on Win2k3 SP2).
I have, as dbadmin, a long time been using Linked Servers (with fixed
account mapping) to a set of DB2 databases.
Now we for the first time got a need to let also non-admins use openquery()
and i have two questions:
Firstly (so noone need to complain this isn't a programming issue ;), is
there any way to use define use of openquery() as part of a View definition
or in a Stored procedure?
Secondly, is there a way to let normal DB users use it without getting the
folowing error message:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. Authentication failed.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [IBM][CLI Driver] SQL1092N "usersaccount"
does not have the authority to perform the requested command.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80040e4d: Authentication failed.].
I recon it would work if i gave those users admin rights but that must
ofcourse be avoided. And if it's only solution i wonder which roles and
permissions are needed?
I'm sure the authentication works for me. I know there is a bug in the
security mapping dialog in that it looses rows when you add new ones but now
everyone is mapped to a fixed account and password on the target.
One thing that may affect it is that the MSSQL authentication used by domain
accounts but so is the account i'm using.
I certainly have been searching the web but most responses doesn't help and
some just aren't possible (asking non-admin to run a trace command).
Hope someone have a solution so we can provide access before the lengthy
process of version update. Maybe there is a hotfix or so?
- Sten