Linked Server to DB2 Security Issue

• SumOfDavid

  Ten Centuries

  Points: 1357

  More actions

  April 13, 2011 at 7:47 pm

  #389278

  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

• Syed Jahanzaib Bin hassan

  SSChampion

  Points: 11410

  More actions

  April 26, 2011 at 5:31 am

  #1315776

  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

  http://www.aureus-salah.com

  Regards,
  Syed Jahanzaib Bin Hassan
  BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

  My Blog
  www.aureus-salah.com