April 13, 2011 at 7:47 pm
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.
April 26, 2011 at 5:31 am
Install DB2 client on the SQL server machine and create the DSN of this DB2 machine and create the ODBC Linked Server then check this,RPC and RPC out should be True
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply