SQL job is not writing on remote text flle
-
Alert Me | Edit | Delete | Change Type
0
I am trying to crate a job, that writes the result set on text file and export to location like "\\abc\xyz.txt"
job succeeds but i cant see any thing written on the file and i have given the same path in the job path option.
-
Two questions:
Does the service account for SQL Agent have write access to the UNC path?
Does the file get created but is empty or does it not even get created?
-
i am admin on remote location folder permission
and it does not even create new file
-
The file is created by either the SQL Server service of the SQL Agent service depending on how the job was set up. Are both of those services running under your domain account as well? IF so, then this should work. If not, you have to give the service accounts access to that network location.
-
huum (7/8/2013)
i am admin on remote location folder permissionand it does not even create new file
it does not matter if you are admin or not.
this is a common security misconception/"gotcha".
The problem is that when you access any resource OUTSIDE of SQL server,SQL server does not use a security context you intuitively expect it to use.
When accessing things like network shares, local hard drives and folders(like some users myDocuments or Desktop) ,xp_cmdshell,bcp with a "trusted" connection, sp_OA type functions etc.
it doesn't matter what YOUR credentials are. Whether you are Domain Admin,Local Admin , logged in as sa, administrative login on a laptop, etc, because SQL will not carry those credentials to the "outside of SQL" security context.
SQL WILL pass your credentials to a linked server, but anything else is using an account you did not intuitively expect it to use.
SQL Server uses either the account set up as the proxy account, or if that is left blank(the default) it uses account it starts with to try and access the resource:
if you are logged in as a sysadmin, it will use the startup account, regardless of what the proxy account is set up with.
or if the above was blank, the account in services:
That account is often an account which has never logged into the domain, and was never assigned permissions to get to the local disk or network share.
As a result, you usually need to create a domain account in Active Directory, specifically grant it share access if it doesn't inherit it from Domain\Users or Domain\AuthenticatedUsers and change the account SQL Server starts with to that account.
Once that is done, and you stop and start the SQL service to make it use that account instead of old running values, your linked server/xp_cmdshell would work.
you can prove this is the issue by simply putting in your credentials, with your domain account and password, and confirm the external object you were trying to access/use works when SQL is run your credentials, so you'd know you need a domain account to access the resource.
[/quote]Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply