How to use Windows Authentication for remote server

Question

How to use Windows Authentication for remote server

xparmanand

SSC Eights!

Points: 957
More actions
June 10, 2010 at 6:11 am

#143580

HI Experts,
I am designing a SSIS package, my requirement is that I need to take data from remote server & load into local server, I can use windows Authentication for local server no problem, but for remote server they provided a domain ID (windows authentication) so I am not able to make a connection with this (with sql login we can make easily), Is there any way we can set it up using windows authentication?
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 1

You will need to set up a SQL Agent Proxy account on the local server using the domain user they set up on the remote server. From SSMS you would:

1 - Add the Credential using that remote domain user account (Security folder)

2 - Add the Proxy (SQl Agent > Proxies > SSIS package execution)

3 - Create a SQL job to run the SSIS package. In the SSIS job step, select the Proxy in the Run As dropdown.

Here is a link that addresses this also http://www.codeproject.com/KB/aspnet/Schedule__Run__SSIS__DTS.aspx

Hopefully they have given you an account without password expiration, otherwise, this might be a pain to maintain.

xparmanand SSC Eights! Points: 957 More actions · Answer 2

HI

Thanks for reply!

I have created credentials with windows Id what they provided, but I am confused with password like which password we need to enter, weather it should be windows Id password or can we give any password?

In proxy account, what pricipals login do we need to provide?

What should be the Job owner?

I got the error Login failure for windows id.

Thanks

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 3

You would use the Windows password for the domain account.

I am not sure about the setting up the Principals tab. In my case, the proxy account I have used is a sysadmin, so I did not have to assign any specific permissions. I suppose you would have to use a login or role that had the permissions to the necessary sql objects.

I generally use SA as the job owner.

xparmanand SSC Eights! Points: 957 More actions · Answer 4

Hi,

I have setup everything like Cerdentials, proxy account but still it is failing, there is something related to security which I am not able to strike, please check the error messgae in Job

Message

Unable to start execution of step 1 (reason: Error authenticating proxy mtbankd01\ermappt, system error: Logon failure: the user has not been granted the requested logon type at this computer. ('Access this computer from network')). The step failed.

Thanks,

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 5

A little progress...now the other system is recognizing the correct login account, but apparently this account does not have the necessary permissions to log on to that system. I do not know what permissions are needed. This is something that you would have to speak with your network admins who take care of Active Directory in your organization.

sqlfriend SSC Guru Points: 52781 More actions · Answer 6

Have you got this fixed, I have same problem now.

Thanks