August 1, 2006 at 6:33 am
I am trying to use the DTS wizard to transfer data over a vpn connection. The source data uses windows authentication whereas the destination site requires SS authentication. The username and password are correct on the input tab for the SS authentication. But the operation fails. When I double click the error, the message indicates an unknown login for another name entirely, not the username I had entered. Where is it getting this? I'm not sure where to look or for what.
Sam
August 2, 2006 at 1:16 am
Sam,
Sounds like you are connecting to a network with VPN, then attempting to login to SQL Server using Windows authentication (SSPI), right?
The VPN connection authenticates using windows credentials, but never "logs into" the network (net start workstation). So your SSPI authentication will always fail.
You could add a Terminal Server (Remote Desktop) connection to the mix. I believe this will cure the connection problem as you would be connecting with SSPI after you login to the network as a Terminal Server user.
Your other alternative is to leave Windows authentication where it belongs IMHO, for LAN users and LAN applications. Always use SQL Authentication for anything with WAN requirements.
That is my story and I am sticking to it...
Andy
August 2, 2006 at 10:06 am
Can you connect to the remote SQL server from Query Analyzer using the SS user and password?
August 2, 2006 at 12:17 pm
thanks for the help. I can connect to SS over the vpn and can view and edit data directly and from QA. I can upload a table using the DTS "copy tables and views from source database" but cannot "copy objects and data between SS databases"
Sam
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply