I have 2 test machines in the same domain on my local network. using SSIS
on machine A, I'm trying to import a database from machine B which is a sql
server 2k database. both sql servers are registered using windows
authentication. However, during the import process when prompted how to
connect to sql server on machine B, I've try every possible way I can think
of (windows auth, sql auth. using the sa account, etc.) and SSIS can't
connect to the 2k sql server. I also tried from with in SSIS to create a
sql server registration to the sql server on machine B and was able to do
that. From there I (in SSIS) I right clicked on the db in the machine B sql
2k registration and selected export. I thought this would work since SSIS
is already talking to the 2k server. But no. In the export wizard I was
prompted for authentication against the 2k server which fails every time.
What's the catch here? Remember I'm trying to import a 2k db from machine B
to a 05 db on machine A. As a test, I opened EM on machine A (which also
has sql 2k installed on it). Using DTS on machine A I successfully imported
a db from machine B. Why does SSIS have such a hard time talking to machine
B? how can I resolve this? Note, I know I can import the db from B to A
using sql 2k on both machines, then from machine A import from 2k to 05, but
this is all a test and I want to know how to import from a one machine
running 2k to another machine running SSIS.

Thanks.

moondaddy (AT) nospam (DOT) nospam

Hello,

When you choosing datasource in the wizard, do you choose "Microsoft OLE db
provider for SQL server"? If not, you may try this to see if it makes any
difference.

Also, please check on server B to make sure TCPIP/named pipes protocols are
enabled. On machine A, you may want to check if TCPIP/named pipes are
enabled in client protocols in SQL server configuration manager. You may
want to change named pipes to the first protocol on server B and restart
SQL service to test the situation.

I tested on my side and it works properly either via "Microsoft OLE db
provider for SQL server" or SQLNCLI.

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===

This posting is provided "AS IS" with no warranties, and confers no rights.

Thanks! Switching it from the native sql provider to Microsoft OLE db
provider for SQL server did the trick.


"Peter Yang [MSFT]" <petery (AT) online (DOT) microsoft.com> wrote

Welcome!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===

This posting is provided "AS IS" with no warranties, and confers no rights.