Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running LogParser in Stored Proc


Running LogParser in Stored Proc

Author
Message
rwiethorn
rwiethorn
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 14
Hi,
Not sure if this is correct Forum, if not, tell me where to ask.

Background:
2 Servers
Server 1 is a Production server. used for Web Hosting, and SQL Report Server.
Server 2 is will be used to execute the logParser.exe and read the data files into SQL 2008 R2

Goal: To run a Stored Proc on Server 2 and read the IIS log files found on Server 1 in the IIS Log folders. Read the content, insert into the SQL on Server 2 and then Delete files from Server 1

I am able to do this when the IIS log files are on the same Server.
Issue: I get Denied Access when the LogParser tries to access the file on a the distant Server.

I am testing by Creating a Job and executing the stored Proc and Runs it as "Executed as user: NT AUTHORITY\NETWORK SERVICE" account.

Sample Code that works when running on the same server:


LogParser "SELECT * FROM C:\inetpub\logs\LogFiles\W3SVC1\RMW\ex120601.log TO GlobalIISLog_2"
-i:IISW3C
-o:SQL
-server:Server2.domain.com
-database:IISLog
-createtable:ON
-ignoreIdCols:OFF

Sample Code that is Denied


LogParser "SELECT * FROM \\Server1.internal.domainY.com\G$\data\logs\W3SVC1\ex120603.log TO GlobalIISLog_2"
-i:IISW3C
-o:SQL
-server:Server2.DomanX.com
-database:IISLog
-createtable:ON
-ignoreIdCols:OFF

Question: What privileges should I change on the Source folders so the Account from Server2 can open/read the IIS Log files?

In the debugging, I found had to grant access for the Network Service account to the folders on Server2 . That's how I got my 'local' test to work.

All help is appreciated
Thank,
rwiethorn

rwiethorn At Gmail dot com



anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6102 Visits: 6078
What account is your SQL server running as? Network Service? Local System? Domain account?

If possible, change the service account to a domain account as thats best practise solution, and then assign that account read permissions to the drive in question. Also your using G$ which is an admin share, so the account will need administrator rights. To get around this, create the Data folder as a share and grant permissions on the share to prevent giving admin rights where they are not nessesary.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


rwiethorn
rwiethorn
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 14
Hello Anthony,
I tried looking at the Configuration Manager to see what accounts are being used, and I get an error.

PS I did not do the install. Some Network admin person who thinks she needs to be the Almighty did.

So, I also need to see if the latest SP has been installed.

Also, I understand what you mean by running under account, for example, that cuold be my account (Domain\rwiethorn). I am a Admin on both servers, but is that really a good idea?

I did make the Data folder a Share. I will continue tracking down the accounts, and making sure I have the latest service pack.
I will let you know more.

Thanks,
rwiethorn



anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6102 Visits: 6078
You want to go for the lowest level of access, so I would create 2 domain accounts one for the SQLServer and one for the SQLAgent, just basic bog standard accounts, no rights to anything, not even domain admin or administrators on the boxes. Then in SSCM you can change the accounts and SQL automatically grants the nessesary rights to the users for you, so you still maintain that lowest level of access needed.

Then ensure that the SQLEngine account has access to the web logs share for reading.

You could also find the version by doing SELECT @@VERSION, which should give you the build number to check the service pack level.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search