Have the following scenario:
1. SQLSERVER1 with windows authentication.
2. SQLSERVER2 with mixed authentication.
Need to perform the following:
Select data from SQLSERVER1 and write to SQLSERVER2.
The volume is not at all big: around 5000 rows.
field1, field2, field3
'Data Source=SERVER IP,PORT;Trusted_Connection=yes;UID=WINDOWS ACCT;Password=PASSWORD-FOR-ACCT'
The Infrastructure group have created a service windows account for me to use in the above.
I have done similar sql via SP using sql authentication. But with windows, how to pass on the UID/PWD.
I tried to create a DTS on SQLSERVER1 but it uses my credentials: how to supply the service acct ?