Linked Server to DB2 Security Issue

Question

Here's the situation.

I have a SQL 2008 box (with latest updates...ver 10.0.4279.0) where I've created a linked server to DB2 ver 9.5 on linux.

I have a local windows group of users who are sysadmins on the SQL Server, but not Administrators on Windows. Also, the SQL Server & Agent service are running as Windows Administrators. (this is a dev box).

Here's the simple code I used to create the linked server:

EXEC master.dbo.sp_addlinkedserver @server = N'DB2BOX', @srvproduct=N'MDASQL', @provider=N'MSDASQL', @datasrc=N'DB2MACHINENAME', @location=N'System', @provstr=N'Provider=MSDASQL.1;Password=xxxxxx;Persist Security Info=True;User ID=db2userid;Data Source=DB2MACHINENAME'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB2BOX',@useself=N'False',@locallogin=NULL,@rmtuser=N'db2userid',@rmtpassword='xxxxxx'
GO

If I or anybody else who is also a Windows Administrator run a simple query against this linked server it runs fine.

SELECT * FROM OPENQUERY(DB2BOX,'SELECT * FROM DB2SCHEMA.DB2TABLE')

But if one of the SQL Server sysadmins who is not a Windows Administrator runs the code, they get:
Error Message
7399: The OLE DB provider "MSDASQL" for linked server "CLMDBRD2" reported an error. Authentication failed.
7303: Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DB2BOX".
7412: OLE DB provider "MSDASQL" for linked server "DB2BOX" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
7412: OLE DB provider "MSDASQL" for linked server "DB2BOX" returned message "[IBM][CLI Driver] SQL1092N "windows.user" does not have the authority to perform the requested command.

I inserted windows.user instead of the actual active directory user name.

When I make that windows user an actual Windows Administrator the query works.

My question is: What permission issue am I running into here? It appears that somehow my non Windows Administrators don't have access to the what? DB2 drivers? ODBC DSN?

I'm confused. I would have thought everything would be using the SQL Server service accounts credentials to run and then using the remote server credentials to connect.

David

Answer 1

check below links they may help you

http://msdn.microsoft.com/en-us/library/ms175537.aspx

http://msdn.microsoft.com/en-us/library/ms188279.aspx

---

If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer. DVR

Answer 2

Thanks for the suggestions, but neither of those pages helped.

The account that all of the SQL Server services is running is a member of the local administrors group.  The only way I seem to be able to use the linked server is if the connected user is ALSO a member of the administrators group on the server. (which is unacceptable)

I've tried putting them into other groups such as Power Users, but to no avail.  I also tried to give the connected user Admin rights over the C:\Program Files\IBM directory...thinking it had something to do with that.  Also no go.

There's something definitely hinky going on here, because this is defeating the whole purpose of a linked server.  The credentials for the linked server are static and embedded within the linked server definition.  I know the linked server works, it's just that the client has to be a windows administrator also, which is fine for me, but not everybody else.

Could this have something to do with the user account that was logged in when the DB2 drivers and utilities were installed?  Or the user account that created the system ODBC DSN?

Answer 3

I thought that it might have something to do with the generic OLEDB Provider for ODBC, so I tried using the straight OLE DB Provider For IBM DB2.

When a user tries to connect, they get this:

OLE DB provider "IBMDADB2.DB2COPY1" for linked server "CLMDBRD2OLEDB" returned message " SQL1092N "windows.user" does not have the authority to perform the requested command.".

Msg 7303, Level 16, State 1, Line 1

 

Once again though, it works if you are also a Windows Administrator.

Answer 4

Hi sumOfDavid,

It seems an Authentication issue to remote connection. You can try to use the Process Monitor, which is a monitoring tool for Windows, to capture the registry between the windows Administrator and a SQL Server sysadmin user and Compare with them. Here is the location about How to capture a Process Monitor trace.

---

Best Regards,
Stephanie Lv

Answer 5

Did you get this to work?  I have the same situation, I am a dba so I have sysadmin in SQL and Local Admin rights on the SQL boxes and everything works fine for me.  When I have my users  try it with their windows accounts setup in SQL and no rights on the box they get the same error as above. 

Answer 6

No, I never pursued Stephanie's suggestion to do a low level tracking down of where the permission restriction was located.  I took the easy way out and told users that there was no solution.  Problem solved! :-)