SQL/DTS with windows authentication
-
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.sqlViewThe 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 ?
-
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 -
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.ViewI get the results if I supply the service account/pwd also. Not sure if this is the solution.
-
If you are going to be doing this on a regular basis, create a Linked Server to use.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply