I have a linked server defined on a SQL Server 2005 SP2 standard edition server using the IBMDASQL OLEDB driver. The linked server has been defined and working for months when used from a SQL Server authenticated session. I started converting our developers to Windows Authentication and access to the linked server is denied when used from a Windows Authenticated session. Here are the error messages:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "IBMDASQL" for linked server "DB2ARUBA" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "IBMDASQL" for linked server "DB2ARUBA".
The linked server security is defined to login to the IBM iSeries using a fixed user and password regardless of the login using the linked server.
In order to analyze the problem, I created one sysadmin SQL Server authenticated login and one sysadmin Windows Authenticated login. The Windows Authenticated login refers to a domain login having administrator priviledges on the local SQL Server box.
So, when I connect to SQL Server using the SQL Authenticated login, a query against the linked server works. When I connect to SQL Server using the Windows Authenticated login, the exact same query against the linked server fails with the messages above.
Does anyone know why?
I had the same problem and here is how I fixed it:
Following the advice from another post, ensure "Allow inprocess" is selected for the linked server provider:
Can you make sure that you have allowed inproc access in SQL Server for the
MSOLAP provider?:
1.. In SQL Mgmt Studio, connect to the SQL Server Database Engine and go
to Server Objects->Linked Server->Providers in the Object Explorer.
2.. Right-click on MSOLAP and select Properties. Check the "Allow
inprocess" box in the Provider Options dialog that comes up
--
Ensure the ID has permission to use the linked server (try with sysadmin for starters)
Ensure the security tab of the linked server is using an AS400 ID and password
I also noticed that my default database as my login ID was tempdb. If I changed my context to master, it ran without giving me the permission error.
Hope this helps!
|||Lexner55 -
That fixed it for me. Thanks very much. FYI, I am using the IBMDASQL provider.
|||I am actually not using the IBMDASQL driver anymore for my servers as I noticed there was quite a difference in response time versus using ODBC (which as a side note is not available in 64 bit servers).
I tested the difference between the two drivers by doing a select * on a large table and compared the time it took to complete using the IBMDASQL driver versus the ODBC driver. It took the ODBC driver a little over 3 minutes to call the 91000 rows whereas it took the IBMDASQL driver over 7 minutes running the same query. With a 64 bit server, I am using the OLE for DB2 driver provided by Microsoft and it's comparable to the ODBC driver.
For the record, I am not saying IBMDASQL should not be used. If you do not need to call large data sets from the AS400, this driver will be fine (in production scenarios as we all know, it's not best practice to select * anyways). It could also be an issue with how our AS400 server is configured or some other setting I may be missing. Regardless, you might want to run some tests and compare the different drivers.
I'm glad to hear you got it working though. Thanks for letting me know.
|||Lexner55 -
We had similar performance issues when we converted from ODBC to OLE using the IBMDASQL driver. We got around the major performance issues by using OPENQUERY instead of direct SQL, although we are still not very happy with the performance. I believe the reason for the improvement using OPENQUERY is that OPENQUERY ships the query to DB2 and lets DB2 optimize and execute it whereas a direct query ( using 4 part names ) pulls all tables involved over to SQL Server and then optimizes and exeutes the query on SQL Server. I could be wrong about that but that's the way it looks to me.
In our 64 bit environment, since the ODBC driver is no longer available, we are using the IBMDASQL OLE driver. Where did you get the OLE for DB2 driver that you referred to? I don't see it in my list of providers when I look in Management Studio.
|||Lexner55 -
I found the DB2OLEDB driver in the feature pack. So, thank again.
No comments:
Post a Comment