Solved

SQL OLEDB Connection with provided windows service account credentials

Posted on 2013-05-14
13
852 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Where clause to fliter varchar with Characters 12 59
Need more granular date groupings 4 46
T-SQL: Wrong Result 7 39
T-SQL: need to reset a declared variable 4 34
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

710 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