Friday, August 17, 2012

Checking VPN logs... CiscoSecure ACS v4.0

I was asked to create a "simple time clock" front end that integrates with our users HR data (downloaded nightly in a data pull I built from HRB). One of the potential pitfalls I pointed out to the person requesting the interface and data objects was that since most of our technical staff have VPN access turned on by default, they could potentially clock in or out from home.

So I started my search to determine if a user was on our network from their desk or via VPN. My network engineer pointed me to the interface showing the logs. I saw how simple the output was and believed it was just reading from a text file. Sure 'nuf, found the text file locally on the domain controllers. There are probably multiple ways to determine how someone is connected, but I couldn't come up with any off the top of my head after a brief pow-wow with my network engineer, so this is the direction I went.

I found the domain controllers that held the logs (.csv files) for passed authentications which was located in ProgramFiles\CiscoSecure ACS v4.0\Logs\Passed Authentications and the files were named: Passed Authentications Active.csv. Ahhh, data, data, data...

When a user logs into the simple time clock application, I check these log files for the user name and text "Remote Access (VPN)" to determine if it's been more than a certain amount of time, say an hour, to attempt to verify this web app is not being accessed by someone connected via VPN.

An over simplified schema of how I solved this problem:

If you need more details, let me know.

Tuesday, July 24, 2012

SSRS Prompting for login when deploying reports or data sources (SSRS 2008)

While migrating SSRS 2005 reports to a new SSRS 2008 server, I had to recreate the project in Visual Studio, add the data source and the .rdl files because my original project files became corrupted (long story).

I saved a few .rdl files to the new folder, created a new and updated .rds (shared data source) and attempted to deploy to the new server, to a subfolder from the main reportserver folder.

I was repeatedly prompted for my login. After some futzing around, I attempted to strip all the sub folder items out of the project properties and I found I could deploy to the main reportserver folder so I thought I'd take a screen shot of the correct configuration for future reference.

What I had been doing is adding after /reportserver/[name of sub folder], where in reality you need to add that to the "TargetReportFolder" and also if you want your data source to be under that subfolder, update the "TargetDataSourceFolder" to the subfolder/data sources url as shown here.

Hope it helps.

Thursday, March 15, 2012

SQL DBA Best Practices - Why separate tempdb?

I've had discussions with server/system architects attempting to justify not only separating the database and log files, but also the tempdb to separate disks. 

This is one of those that more applies to SQL Server 2008 and beyond.

Did you know that tempdb not only stores temporary tables, but SQL Server also utilizes it for grouping and sorting operations, cursors, the version store supporting snapshot isolation level, and overflow for table variables? 

Hopefully knowing these other objects that can be highly utilized even if you do not believe your databases utilize a large amount of temporary objects will arm you with ammunition to vote for more and different physical disks for tempdb.

Disk cost cannot dwarf the speed benefit you will have, especially in data warehouse situations where some BI solutions create very large temp tables to join on before returning results.