Solved

SQL OLEDB Connection with provided windows service account credentials

Posted on 2013-05-14
13
830 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
ID: 39168290
0
 

Author Comment

by:STHOMP02169
ID: 39168406
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
ID: 39169061
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
ID: 39171114
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
ID: 39171140
Same or different domains?
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39171760
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 20

Expert Comment

by:Marten Rune
ID: 39172178
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
ID: 39172199
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
ID: 39210907
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
ID: 39210482
Why not share your solution?

Regards Marten
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39210908
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
ID: 39210959
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

911 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

19 Experts available now in Live!

Get 1:1 Help Now