Learn how to a build a cloud-first strategyRegister Now


Windows Authentication in DTS package help

Posted on 2007-07-30
Medium Priority
Last Modified: 2013-11-30
I have a DTS package on a SQL server scheduled to run as a Job.  This package connects to a remote SQL server to grab data and update local tables.  In the past, we had a SQL login for the remote server, and the connection in the DTS was configured using "SQL Server Authentication" with that ID.

Due to security requirements, no more SQL logins are allowed, and all connections have to be made using Windows Authentication.  We created a new domain acccount and granted the necessary access on the remote and local servers.

The question is:  How can I setup my DTS or Job to connect to that remote server using that domain ID?  Changing the identity of my local SQL Agent would create all sorts of problems and require other changes that I prefer not to make.  Also, in the future, I might need a different ID to connect to other remote databases.  Is this possible?  Please help.

Thanks in advance.
Question by:cheluto2
  • 3
  • 2
LVL 13

Expert Comment

ID: 19596330
If the job is owned by a login belonging to the sysadmin fixed server role, the security context of the package defaults to the account used to start the local SQL Server Agent. If the server is registered using Windows Authentication, the owner of the job is the account of the SQL Server Agent. If the server is registered using SQL Server Authentication, the owner of the job is that SQL Server login.

If the job is owned by a login that is not a member of the sysadmin fixed server role, the package runs under the context of the job step proxy account, with the rights and permissions of that account.
LVL 13

Expert Comment

ID: 19596395
You can change security context from commandline as part of SQL Agent job:

Author Comment

ID: 19600899
Thanks for the explanation of ownership and security contexts.  I am not sure I know what the "job step proxy account" is, though.

So, in other words, there is no way to achieve what I am looking for without external tools like the ones referenced, or running the SQL Agent under this new domain account?

LVL 13

Accepted Solution

ispaleny earned 2000 total points
ID: 19602360
In SQL Server Enterprise Manager:

Go to Management branch
Right click on SQL Server Agent
Select Properties
Select "Job System" tab
Uncheck "Only users with Sysadmin..." option
Enter proxy credials - can be a domain account

Author Comment

ID: 19603416
That worked, and is a feature I did not know about.  I still cannot use different Windows Authentication ID's, but this will have to do, and it beats having to change the SQL Server Agent account for me.  Thanks!

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question