Running LogParser in Stored Proc

  • 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

  • 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.

  • 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

  • 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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply