January 2, 2014 at 9:11 am
I have a sql server (2012) running on MSServer2008. I created a new SSISW package (took me forever...newb) that references a network share drive. The package works fine running under my creds, but when I let it loose under the agent's creds, it fails (can't see the folder.) I assume it's an authentication issue, but now my quandry.... What account should I use for Agent so it can get the job done?
My client runs AD, the server is in a domain (WINNTDOM), as are my creds. I tried using an account called WINNTDOM\sqlserveragent, but I couldn't add that account to the AD group to give it access to the network share.
I'm so confused... What's the standard practice here? Is there a way to run the job under different creds?
Jim
January 2, 2014 at 10:05 am
You can either give the account that started sql agent permissions to the folder or use SQL Server Agent Proxies.
Is WINNTDOM\sqlserveragent the SQL agent account?
January 2, 2014 at 10:09 am
I have a sql server (2012) running on MSServer2008. I created a new SSISW package (took me forever...newb) that references a network share drive. The package works fine running under my creds, but when I let it loose under the agent's creds, it fails (can't see the folder.) I assume it's an authentication issue, but now my quandry.... What account should I use for Agent so it can get the job done?
It makes more sense that it's an authentication issue. You should use an agent account for the agent. If the server is on a domain, preferably one that has domain access.
My client runs AD, the server is in a domain (WINNTDOM), as are my creds. I tried using an account called WINNTDOM\sqlserveragent, but I couldn't add that account to the AD group to give it access to the network share.
If you're not familiar with AD permissions or the network share permissions setup, have the SA's make the account for "WINNTDOM\sqlserveragent" It may need to have permissions similar to what you have to work correctly. It's easier if they use groups for access.
Mirroring your account permissions may be the easiest way to get it going if they use groups, if not you'll need them to give the service account access to those same locations.
I'm so confused... What's the standard practice here? Is there a way to run the job under different creds?
You can run the job under what ever credentials you want, but they will need the access they need regardless. I would create a service account, give it the permissions it needs to the file share and anywhere else it needs to go, and work out from there.
My client runs AD
Are you a consultant? If you're there to fix a SQL problem, you can still get them to help you with network permissions. I would want my account name as far away from their data as possible moving forward. If they remove your account, you have to know it won't break anything.
.
January 2, 2014 at 10:42 am
Bill, thank you for your help. You seem to understand my quandry. I am a consultant, and NOT a SS expert. I work from home, and my SS db is not supported by the client's IT folks, though I do get advice from the guy who set it up for me (client owns the license and the server, I manage it day to day...)
My IT contact is off until Monday, I may just have to wait for him. The client outsources 100% of its sysadmin (AD, Windows support, etc.) to India, and while those guys are sometimes willing, it always takes up a lot of precious time.
What I was looking for was "standard" practice. If you set up a SS in an AD environment, and SQL Server Agent will need to access a network share, what account would one use? The account I did use works for non-network-share stuff (I'm not sure why...) but when I tried to add "WINNTDOM\sqlserveragent" to the group with access to the network share, it failed saying the account was not a user.... I also tried forward slash...
There is a "global" account called "sqlserveragent", but I don't know the password for it, etc. I guess I'll have to wait for my IT guy to come back on monday.
Jim
January 2, 2014 at 1:16 pm
Do you have access to the DC? Verify that the account exists, check what permissions it has. Hopefully something like a service accounts group or so. check the security of the network location. Verify that the service account or a group that the service account is in has access there. Read and write depending on what level of access is required.
Verify that both servers, the SQL server and the target network location, are connected to the domain controller. If they are not, you won't have a like account to use. Try and go that route if you can.
A few things need to be checked before you know exactly what to fix.
1) Is the SQL Server on the Domain Controller.
2) Is the network share / file share on the Domain Controller.
3) Can both servers talk to each other across 445 (can you move a file back and forth with your account)
3.5) If you can, is your account a domain account or a local account.
4) Verify the security section of the network share. See if what groups have access to it.
5) verify the SQL Service account, see what groups it has access too.
If the file share is not on the domain, you cannot use a domain account there. You may be able to use the same account name with the same password in both places as a local account....
Honestly, if you have too much trouble with the above, I would not attempt adding accounts. I would leave it to the SA's. If there's a huge pain getting them involved to fix their side, make the problem known as something you cannot resolve without more cooperation. There's a lot more that goes into getting this stuff working. Are you in the states? If so, I don't mind going through PM's or a quick call to see if I cannot help figure it out quicker. As long as you've got the information for 1-5 first that is 🙂
.
January 3, 2014 at 2:08 am
JimS-Indy (1/2/2014)
What I was looking for was "standard" practice. If you set up a SS in an AD environment, and SQL Server Agent will need to access a network share, what account would one use?
The standard practice in this scenario is to use a basic low priv domain user account.
JimS-Indy (1/2/2014)
The account I did use works for non-network-share stuff (I'm not sure why...)
Could it be a local admin on the sql box??
JimS-Indy (1/2/2014)
but when I tried to add "WINNTDOM\sqlserveragent" to the group with access to the network share, it failed saying the account was not a user.... I also tried forward slash...
Always a \ never a /
Are you saying you have access to the Active Directory catalog\domain controller?
What group are you adding the account to?
The simplest way to achieve what you need is to ensure the account that the agent service is running under has been granted Share and NTFS privileges on the network location
JimS-Indy (1/2/2014)
There is a "global" account called "sqlserveragent", but I don't know the password for it, etc. I guess I'll have to wait for my IT guy to come back on monday.
Global accounts are not usually advised
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 3, 2014 at 7:45 am
Sorry, I didn't know "global" had a specific meaning. I probably meant an AD account. No, I don't have access to the DC. I have access to an app that lets me manage group membership for groups I "own". I own the group that has permission for the specific network share. (Well, my client does, anyway....)
The standard practice in this scenario is to use a basic low priv domain user account.
That's what I needed. Sometimes it takes alot to get through to my thick skull. I think the "sqlserveragent" generic account controlled by corporate IT is likely the direction I need to go. Again, I have to wait until Monday when my contact returns. We should have 12" of snow and -13 degrees Fahrenheit by then, so I hope he comes to work (though he is in Virginia....)
Thanks, Perry and Will. If not for your advice, I might have assigned it to a generic account I controlled. As you said, if my account is deleted, the whole shebang breaks, so that was a good call. I'll post what I find out.
Jim
January 3, 2014 at 10:07 am
JimS-Indy (1/3/2014)
I have access to an app that lets me manage group membership for groups I "own".
What is the app called
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 3, 2014 at 10:09 am
"MTGroups" (MT is an abbreviation for the client's company name)
Jim
January 7, 2014 at 2:31 pm
OK, new situation. While waiting for my IT contact to return, I changed the sqlagent to start using my own personal creds. That can't be permanent for many reasons, but as a test (since I have full access to the network shared drive, the SQL Server machine, and the SQL Server, and I'm the author....) it should confirm the problem as permissions on the network shared folder....
The package still can't see the folder. I say this because the FOREACH loop never runs.... Zero files to process. When I simply run the package using Visual Studio, it runs fine, and processes the (1) file in the target folder.
My contact returned and added WINNTDOM\sqlserveragent to the group with permission to the folder (R/W.) Changed sqlagent back to use that account, and it continues to fail.
Is there a setting in SQL Server that would prevent SSIS (and not "me") from accessing a file outside the local computer?
My next step is to change the report generator (SSRS) to send the report file directly to a folder on my server (if I can figure that one out...) and see if SqlAgent can deal with that. If I can accomplish that, I may just give up on using the network shared drive altogether.
If you know of a setting on SQL Server that's blocking me, chime in!
Jim
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply