July 18, 2007 at 9:29 am
I am fairly new to a small IT group and getting my feet wet with DB admin. I have been tightening the security and have hit a snag. We have a Db2 DB set up as a linked server from SQL 2000. I don't see any problem with the set up if I log onto the server as admin or connect to the SQL instance as SA, but if I use MY login (with sysadmin rights) I get an error. Specifically: "Error 7302: Could not create an instance of OLE DB provider 'IBMDADB2'"
Any thought as to why I can access it via SA/local admin, but not as sysadmin?
Thanks - J
July 18, 2007 at 11:02 am
You need to map your login to the one used to connect to DB2 in the linked Server properties!
Cheers,
* Noel
July 18, 2007 at 11:28 am
Thanks, but I actually have all SQL users mapped to a single login on the DB2 side. Under 'security' I did not add any Local server login's and entered login info 'For login not defined above'. I assume that this means that anyone trying to use the linked server will then appear the same on the DB2 side. I did also try using SQL 2005 client tools to look at the problem and got a little more info in the error message (pasted below). I tried to articles listed - but they don't seem to exist (very helpful!!!). I assume that since it works via SA that this is not a security issue on the DB2 side or a communication issue. It seems that it has to be either in SQL or Server. Again - it works when I am local admin on the box or connected using SA, but not as myself (sysadmin)?
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not create an instance of OLE DB provider 'IBMDADB2'. (Microsoft SQL Server, Error: 7302)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=7302&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
July 18, 2007 at 11:41 am
are you using "windows" authentication when you sad that you connect as "myself" ?
* Noel
July 18, 2007 at 11:44 am
Yes... I have connected using query analyzer on my PC.
July 18, 2007 at 11:50 am
Well Try connecting using an SQL Server Login (other than SA of course).
Windows Authentication prevent account delegation ( or impersonation) unless you have setup Kerberos and The remote server is also SQL Server.
* Noel
July 18, 2007 at 12:09 pm
I don't think that I am being clear...
What I have is:
server1 - with SQL instance companySQL
server2 - with DB2 instance companyDB2
I have set up a linked server pointing from companySQL to companyDB2. In the definition of my linked server setup I did not attempt map any logins from companySQL to companyDB2. I just entered a known login on companyDB2 in the security portion of the properties and check the 'Be made with this security context:'.
When I either log onto the server as administrator or connect using the client tools to SQL using SA I can expand the companyDB2 to see tables and I can query it. If I use window auth to connect via the client tools I get the error. My login has the System Administrator role associated with in on companySQL.
What has me completely confused is why can SA connect, but a sys admin can not? I don't pretend to be a guru - but it does not seem to have anything to do with the set up of the DB2 security in the linked server. It gives an error that seems to me to imply that a process failed.
Thanks for all of the replys - J.
July 18, 2007 at 12:19 pm
I did actually create a test login as well and connected via QA using Test using SQL login. It still failed when trying to expand the linked server node... grrr...
July 18, 2007 at 12:26 pm
If what you want is to "EXPAND the SERVER NODE" you do need SA.
If what you want is to "query" the remote server then that should be enough!
* Noel
July 18, 2007 at 12:34 pm
OK - I was able to query it under the test login. Thanks for the lesson and sticking with me through several posts.
J.
July 18, 2007 at 12:39 pm
Happy to help
* Noel
October 11, 2007 at 2:15 pm
Have you ever resolved the problem> I have the same issue.
October 16, 2007 at 3:10 pm
I encountered exactly the same problem and can add that if you switch from IBM OLEDB provider for DB2 to Microsoft OLEDB Provider for ODBC on on Linked Server configuration it would work with windous authentication too.
October 17, 2007 at 7:51 pm
Actually Microsoft OLEDB Provider worked because it was setup as "InProcess" by default. As soon as I setup IBM DB2 Provider as "InProcess" it started to work too.
This is how you do it :
• DB2OLEDB provider needs to run in-proc. To enable this setting:1. Start the Microsoft SQL Server Enterprise Manager.
2. In the Console tree, find the Linked Servers node (under the Security folder). Right-click on the linked server created above, and in the Properties dialog box, click the General tab, then click on Options, and click to enable the Allow InProcess setting. This is the only way to enable this setting, and after it has been enabled for a given provider, the setting is used for every subsequent linked server created using that provider, including the ones created with T-SQL script.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply