November 20, 2006 at 2:02 pm
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.
select
field1, field2, field3
from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=SERVER IP,PORT;Trusted_Connection=yes;UID=WINDOWS ACCT;Password=PASSWORD-FOR-ACCT'
 .DBname.dbo.sqlView
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 ?
November 20, 2006 at 2:56 pm
When using windos authentication You will either have to have your server enabled for account delegation through kerberos or use a linked server with account mappings. SQL Authentication is less secure but not as demanding in terms of configurations.
Search for "Account Delegation Through Impersonation" to find all needed to make it work under Kerberos.
Cheers,
* Noel
November 21, 2006 at 9:03 am
When I use integrated security=SSPI, there is no need for uid/pwd. does this read my credentials ?
The following works in SQL QA:
select
field1, field2.....
from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=SERVER IP,PORT;Integrated Security=SSPI;'
 .Database.dbo.View
I get the results if I supply the service account/pwd also. Not sure if this is the solution.
November 21, 2006 at 1:01 pm
If you are going to be doing this on a regular basis, create a Linked Server to use.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply