Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Running LogParser in Stored Proc Expand / Collapse
Author
Message
Posted Friday, June 8, 2012 8:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 12, 2012 1:59 PM
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



Post #1313148
Posted Friday, June 8, 2012 8:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
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
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

Post #1313152
Posted Tuesday, June 12, 2012 2:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 12, 2012 1:59 PM
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



Post #1314721
Posted Wednesday, June 13, 2012 1:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
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
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

Post #1314889
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse