Solved

SQL OLEDB Connection with provided windows service account credentials

Posted on 2013-05-14
13
841 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

756 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