November 4, 2011 at 1:41 pm
There is actually a very good reason for what I am about to ask, but in order not to get caught up in the why's I am just going to state we are building a tool to be used by a client that backs up a database from a prod sql server using a script that will be executed by a .net app. The script will need to backup to a network location. Then it will execute a restore with a script and restore it to a totally different sql server. I understand all the pure sql level things that must happen for that. I have done them a thousand times in my own environments.
The big possible problem in this scenario is that this tool is for many of our clients (business partners). The 2 sql servers will always be on the same network at a client site, and they will both belong to the same domain (or a trust etc) What i can't guarantee is if the sql service account will have permissions to the UNC path, because the windows account the service uses just some domain account that doesn't have permissions to the unc path. Remember this is a client site and we dont want them to have to make changes.
So the real questions is, can i authenticate to the sql server with windows auth ( i can guarantee a windows auth account will exist for us and that windows account will have permission to the unc path) then have sql server service write the backup file to the network using that account rather than whatever account the service itself is using? And also have the second server read from the network using the windows account that i log into it with (might be same account, might not, but doesnt matter just use what i tell it). And yes i can guarantee the windows auth account on the second server as well.
Jimmy
"I'm still learning the things i thought i knew!"November 4, 2011 at 1:49 pm
HI Jimmy,
If I correctly understand your problem, I'm pretty sure this is exactly the purpose of SQL Agent Proxies: http://msdn.microsoft.com/en-us/library/ms189064.aspx
November 4, 2011 at 1:56 pm
NJ-DBA (11/4/2011)
If I correctly understand your problem, I'm pretty sure this is exactly the purpose of SQL Agent Proxies: http://msdn.microsoft.com/en-us/library/ms189064.aspx%5B/quote%5D
I know about proxies. But they are executed from the agent. Our requirements are not to use the agent and not to create any new "configurations" on the sql server. The only change can be that a windows auth login can be added to the sql server and that windows auth login will have ntfs and share permissions to the unc location.
Jimmy
"I'm still learning the things i thought i knew!"November 4, 2011 at 2:11 pm
imSQrLy (11/4/2011)
NJ-DBA (11/4/2011)
HI Jimmy,If I correctly understand your problem, I'm pretty sure this is exactly the purpose of SQL Agent Proxies: http://msdn.microsoft.com/en-us/library/ms189064.aspx%5B/quote%5D
I know about proxies. But they are executed from the agent. Our requirements are not to use the agent and not to create any new "configurations" on the sql server. The only change can be that a windows auth login can be added to the sql server and that windows auth login will have ntfs and share permissions to the unc location.
Hmm, I believe you are outta luck if these are strict requirments. You can act as:
1.) the database service account
2.) the sql agent account
3.) a proxy account
would love to know about it if there is another option.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply