Welcome to ISAserver.org
Forums |
Register |
Login |
My Profile |
Inbox |
RSS 
|
My Subscription |
My Forums |
Address Book |
Member List |
Search |
FAQ |
Ticket List |
Log Out
Unable to run query using SQL logging method
|
Users viewing this topic:
none
|
Logged in as: Guest
|
Login  | |
|
Unable to run query using SQL logging method - 13.Feb.2006 9:19:26 PM
|
|
|
pruittwh
Posts: 16
Joined: 15.Feb.2005
Status: offline
|
I followed the procedures to allow SQL logging using ISA 2004 EE to SQL 2000.
I finally have SQL configured and the logging is occurring. I can see the machine account logon and logoff in event viewer, see the machines connected as MachineName$ under process info, and I can even generate a report that pulls event data from previous days. My only problem is when I use the ISA Log viewer and select anything other than "Live" I receive the following error. "The query stopped because an error has occurred while it was running."
Everything I have read has suggested a permission error, but I have re-checked all the permissions and it seems that I would not be able to generate a report if I was having an issue with permissions. I have enable full auditing in SQL and can see no issues with failed logon attempts or any other security related events.
I captured the following info during the process. I used live viewing from my desktop via the remote management console while attempting to run the query from the CSS. The CSS is housing the SQL Server also. Here is what I am seeing from the logs:
10.96.20.204 10.96.19.10 53 DNS Initiated Connection Allow DNS from ISA Server to selected servers
10.96.20.204 10.96.20.255 137 NetBios Name Service Denied Connection FWX_E_POLICY_RULES_DENIED (repeats about 15 times)
222.222.222.3 222.222.222.255 137 NetBios Name Service Denied Connection FWX_E_POLICY_RULES_DENIED (repeats about 15 times)
10.96.20.204 is the IP address from one of the array members. 222.222.222.3 is the intra-array address for the same member.
My first thought was there was a DNS issue, then maybe something in the System policy was not configured correctly. I have checked the DNS records and everything seems to be in order.
Any suggestions?
< Message edited by pruittwh -- 13.Feb.2006 10:12:08 PM >
|
|
|
|
|
RE: Unable to run query using SQL logging method - 16.Feb.2006 1:08:55 AM
|
|
|
Jason Jones
Posts: 2121
Joined: 30.Jul.2002
From: United Kingdom
Status: offline
|
You need to enable both system policies for SQL logging - one of them is specifically for NetBIOS as ISA uses Windows authentication to connect to SQL (hence the computer$ accounts)
JJ
_____________________________
Jason Jones (MVP)
Silversands Limited http://www.silversands.co.uk
My Blog: http://blog.msfirewall.org.uk/
Get our NEW ISA 2006 Book!: http://tinyurl.com/2gpoo8
|
|
|
|
|
RE: Unable to run query using SQL logging method - 16.Feb.2006 4:32:41 AM
|
|
|
pruittwh
Posts: 16
Joined: 15.Feb.2005
Status: offline
|
The system policy is enabled for Netbios and SQL logging. The destination set includes the object Enterprise Configuration Storage Servers which contains the FQDN of both my primary and replicate CSS. Each array is pointing to SQL Server 2000 running on the respective CSS for each site. 2 arrays, 2 CSS-each using the other as an alternate. I assumed that the logging is working. My alert definition for logging is set to disable the firewall service if logging fails. I was seeing the logging fail initially as I was using a named instance for SQL and ISA seemed to have issues with that. I deleted the instance and used the default instance. After that, I never saw any future alerts stating the firewall service was disabled due to logging failing. Once every hour, I can see each array member contacting it's respective CSS/SQL Server over port 1433. As stated, I'm also seeing the MachineName$ in the SQL process for each database. I have monitored the database files and see them growing so I know that logging to the SQL server is occurring.
|
|
|
|
|
RE: Unable to run query using SQL logging method - 16.Feb.2006 6:29:39 PM
|
|
|
pruittwh
Posts: 16
Joined: 15.Feb.2005
Status: offline
|
Well, I contacted our Microsoft TAM and after 20 minutes of support with the MS technician, I was told that when logging to SQL it was not possible to query historical data. It was suggested that I use the newly released ISA Server 2004 Reporting Services Pack for SQL to build custom reports if I needed to query historical data. Thanks for the input.
|
|
|
|
|
RE: Unable to run query using SQL logging method - 8.Mar.2006 12:44:41 PM
|
|
|
MConabeare
Posts: 8
Joined: 12.Feb.2004
From: UK
Status: offline
|
Hi
Do you know of any install/config guides for configuring SRS and ISA ?
Thanks Mark
MConabeare@yahoo.co.uk
|
|
|
|
|
RE: Unable to run query using SQL logging method - 8.Mar.2006 8:37:32 PM
|
|
|
mmouser
Posts: 3
Joined: 8.Mar.2006
Status: offline
|
Hi Jason,
By this reply do you mean that when you set ISA to log to a SQL (ODBC) database that the ISA daily summaries will not generate properly and hence you will not be able to generate summary reports within ISA??
If so, is the documented somewhere by Microsoft??
Thanks,
Mike
|
|
|
|
RE: Unable to run query using SQL logging method - 13.Jun.2006 7:13:22 PM
|
|
|
RobJohn
Posts: 87
Joined: 28.Feb.2001
From: Montgomery, Al
Status: offline
|
I am having the same problem on ISA 2k4 SP2, only able to view live log data. Absolutley no indications given in the event logs. ISA is using MSDE.
_____________________________
Rob John
MCSE, CCNA
|
|
|
|
|
RE: Unable to run query using SQL logging method - 12.Jul.2006 4:07:59 AM
|
|
|
Merddyn
Posts: 3
Joined: 11.May2004
Status: offline
|
What the TAM told you is actually incorrect. I am running ISA 2k4 and logging to SQL server and am able to pull data. I previously experienced the same issue however. The issue ended up being two fold:
1. I was using ISA Server 2004 Standard with around 2000 outbound sessions and publishing 40 or so websites logging to SQL Server 2000
Why was this an issue? - ISA Server Standard can only pass so much data to SQL at a time because it is using an ODBC connection to transfer data. The Enterprise version of ISA uses a more direct connection, thereby removing many performance limitations provided by ODBC, as well as a stored procedure to assist with dumping mass numbers of records in a more efficient manner.
2. My SQL server was underpowered for the level of traffic coming through my connection.
Why was this an issue? - Even though my network bandwidth utilization was low, my CPU and RAM were through the roof on the DB server. Microsoft recommends using a direct connection via crossover on a private network for SQL logging. None of the documentation I have found thus far contains any mention of the stored procedure or the difference in DB connection types between the standard and enterprise versions...not even the 5 books on ISA 2k4 that I have mention it lol.
I now have my DB server running on better hardware and I am working to get the crossover connections online. I have been able to query without issue up until I turned my web publishing back on. Now I am back to having issues because ISA is too busy dumping data (it's number one priority) to take time away for a query.
My suggestion, though not extremely feasible in most cases due to cost, implement SQL 2k5 for your DB on a really beefy box and have it mirror during your lowest traffic period to a second SQL 2k5 DB Instance on the same box. You will likely always be 24 hours behind, but you shoud be able to query your data from the second instance with a custom report in Reporting Services.
Hope that helps!
_____________________________
Merddyn
|
|
|
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
 Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts |
|
Printable Version
