Having trouble connecting my SQL server to an Orac | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Having trouble connecting my SQL server to an Orac

Hello, I’m trying to setup a dts package to receive updated from tables in an oracle database. I keep getting the following error message when I select my data source and type in my credentials for the oracle server from the dts package wizard: Error Source: Microsoft OLE DB Provider for ODBC Drivers Error Description: [Microsoft] [ODBC Driver Manager] Driver’s SQLSetConnectAttr failed Context: Error during initialization of the provider. However, I can connect to this Oracle fine using Microsoft Access 2000. I am running SQL Server 7.0 with SP4, and have already loaded MDAC 2.7. I have Oracle 8i loaded, and I have tested my connection to the Oracle server, and it succeeds there. But it wont connect for me in the dts package wizard. I have tried this same process on a different machine, and the connection succeeds in the dts package wizard. So there is something incorrect on my server. My driver versions are: Oracle v8.01.07.00 sqora32.dll 5/11/2000
Microsoft ODBC for Oracle v2.573.9001.00 msorcl32.dll 2/19/02
SQL Server v2000.81.9001.00 sqlsrv32.dll 2/19/02. I already have a system dsn setup with the attributes to the oracle server in the odbc data sources section in the control panel. This is a Windows NT Server 4.0 SP6a machine. Does anyone have any suggestions that I have missed? Thanks.
As long as you’ve privilege to connect destination server from SQL server you can use DTS. The error represents your login doesn’t have permission to connect.# If you’re not sa or not having SYSADMIN privilege. So check the privileges for SQL Services account also on the Oracle system. _________
Satya SKJ
]]>