Solved

SQL OLEDB Connection with provided windows service account credentials

Posted on 2013-05-14
13
825 Views
Last Modified: 2013-05-31
SQL Server 2008
SSBIDS 2008
SSIS 2008

I need to provide a specific windows service acccount to make a connection to a SQL OLEDB source. The source SQL Server is 2008 and set at Integrated Security. The service account has all the proper read credentials as I can "run as" SSMS and see the table we need to pull.

Package protection level is set to "Encrypt Sensitive with Password"

The problem is that when the package runs as the SQL Proxy, the agent is attempting to make the connection using the Proxy credentials, not the saved service account credentials.

We cannot have a service account with credentials to multiple datasources. This seems like it should be so easy, yet I'm having a bugger of a time trying to source data from a SQL Server.
0
Comment
Question by:STHOMP02169
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
0
 

Author Comment

by:STHOMP02169
Comment Utility
Thank you, but that isn't even remotely related to my issue.
The proxy is established on the target server and runs all the jobs. The issue is I need to create a connection in the SSIS package that uses as specific Windows account to connect to a SQL server source.
0
 
LVL 28

Expert Comment

by:Ryan McCauley
Comment Utility
If you've set up an agent job proxy account, it will be used to run all your jobs - if you want more control, you should be setting up multiple proxy accounts and assigning your package to run as one of the other accounts. If you're saying that you want most of your package to run as one account and only this single data source to connect using a different account, I don't believe that's possible.

In the case of what you're doing, you have two choices:

 - Set your connection to use SQL authentication and then provide the SQL login details manually in your package
 - Create a separate package that runs via your service account and stages the required data locally, and then connect to that data from your second package.

Your package will run as a single account, and all the connections (that user integrated authentication) will authenticate as that single user.
0
 

Accepted Solution

by:
STHOMP02169 earned 0 total points
Comment Utility
Thank you for your response. Again, I don't think I'm making myself clear on the issue. Please take the Proxy account out of the equation.
We need to get data from another group's SQL Server. There is a service account that uses Windows Authentication to connect to that server.
I need a connection in the package to use that specific windows account for ONLY that connection.

Per the bank's security policy, the source service account cannot have permissions to the target server. Nor can the target service/proxy account have permissions to the source server.

I think the only way this can be done is to get a SQL Login on the source server. But SQL Authentication is also against the bank's security policy.

I'm going to close this issue as I don't want to waste my own or anyone else's time with an issue for which there doesn't appear to be a viable solution.
0
 
LVL 20

Expert Comment

by:Marten Rune
Comment Utility
Same or different domains?
0
 
LVL 28

Expert Comment

by:Ryan McCauley
Comment Utility
I think you're correct - the requirements you're giving don't allow for a solution that meets them all. I wish you luck finding a solution that works, but I think you're out of luck with the problem as you currently face it.

The only way around it would be fetching the data in one package and staging it locally somewhere, and then reading it from a second package (or setting up some kind of recurring data export), but that would require the account you use to fetch the data (the remote server windows account) having rights to persist the data on your local server, and that sounds like it's not allowed.

Hopefully you can point out to somebody the lunacy of not letting the same account have permissions in more than one place, as it completely prevents any cross-server-data-sourcing like this.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 20

Expert Comment

by:Marten Rune
Comment Utility
You can run packages using the DTS command, and from windows scheduler.
Then the runas with the switch netonly might be what youre looking for.
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/a4243cb5-3b86-4a75-b745-e4c6665faed8/
look at posting 'Tuesday, March 23, 2010 12:13 PM'

You should be able to use cartificates as authentication between different domains. Im including a link that shows how to set up mirrors. Just look at the security part and this might be sufficient.

Regards Marten
0
 
LVL 20

Expert Comment

by:Marten Rune
Comment Utility
My comment above is NOT a out of the box ready solution for your problem.
Its meerely an attempt of 'thinking outside of the box', and hopefully it useful for you and might inspire into some acceptable way of solution.

Thought of Another, You could (given it's a Windows 2008r2 or better domain) let the bank put a RODC (Read Only Domain Controller) and let this one verify your Connection.

Just Another outside of the box thought.

Regards Marten
0
 

Author Comment

by:STHOMP02169
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 0 points for STHOMP02169's comment #a39171114

for the following reason:

Came up with my own solution.
0
 
LVL 20

Expert Comment

by:Marten Rune
Comment Utility
Why not share your solution?

Regards Marten
0
 
LVL 28

Expert Comment

by:Ryan McCauley
Comment Utility
If you came up with your own solution and are asking to close your question, please post the solution as part of your answer. If you don't want to post your solution, you should instead request that your question be deleted, because it won't provide value to future visitors without an attached solution.
0
 

Author Comment

by:STHOMP02169
Comment Utility
Once upon a time, Experts Exchange was a valuable blogspot where developers were able to get and share "real-life" solutions to issues they encountered. Now it has become a haven for do-nothings who just do key word searches on MSDN and post unrelated links to KB articles.

The source sql server now runs a job that extracts the data to an ftp site and the target server picks it up.

Please delete my question.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now