Windows Server 2008 Perfmon logging to database?

[Stoo]

Has anyone ever managed to get this working?

I've created my performance counters, set up a System DSN (which tests okay), and changed the data collector from binary to SQL, and pointed it at the SQL server, but I get a rather cryptic error message:

"When attempting to start the Data Collector Set the following system error occured: Call to SQLAllocConnect failed with %1"

Googling the error is virtually no help as it just gives you a bunch of cryptic performance monitor ODBC programmer references..

I could just have them log to files and then run a parser, but it's rather long-winded process..

Any perfmon geniuses around?

[Moby-Dick]

not a perfmon genius but I'll see what I can do - anything come up on SQL profiler if you run a trace ?

[Stoo]

Not a sausage :/

I'm wondering if it's because I'm telling it to use a sql account rather than Windows Authentication (which won't work as SQL is on a totally different box..)?

[Moby-Dick]

as long as its in the same forest you can use windows auth.

[Stoo]

They're not in a domain, just three dedicated servers at our hosting company..

[Moby-Dick]

handy..... not sure if it would be possible to accounts form other machines...

[Stoo]

Yeah, not ideal.. I've a hunch that the dsn isn't having the credentials passed to it from perfmon, hence why the error is coming out with an ODBC programming error?

[Stoo]

Bingo..

It seems as though perfmon's SQL logging was never set up with anything other then trusted connections in mind - it simply doesn't support sql user accounts, combined with a slight issue where Server 2008's System DSN's weren't showing up in the expected place in the registry meant that it just kept trying to connect with no credentials, and I'm not even sure that the ODBC was ever called properly..

fixed it by having the same user account on all three machines, adding the user account into SQL, creating the DSN's with SQL accounts on both webservers, frigging about with the registry to make sure the the DSN's actually ended up in the right place (HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI, HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources), then changing the DSN's to use windows auth..

Then once I set the collectors up again in perfmon, changing the user to the windows account which I'd previously created on all three machines, but making sure that the machine names where left off the usernames when prompted..

Gave it a bit of a kick and it finally coughed into life!

What a sack of brown stuff..

[skbergam]

This was an easy workaround for me (no registry hack required). I just had to create the same local user on both machines with the same password. Gave it permissions to SQL on the SQL server, and then used it for Run As in perfmon and it worked great.