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 12»»

Error while logging Performance Monitor counter to sql server Expand / Collapse
Author
Message
Posted Monday, May 21, 2012 8:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:52 AM
Points: 114, Visits: 991
I am trying to do following task and I donot know it is possible in this setup or not?

I am trying to setup performance counter on a non-domain server (Server1)
All the performance counter values I want to capture in other sql server (Domain\Server2)

In this task I donot want to establish any kind of connectivity from Server2 to Server1 (sql or windows), but I want to push data from Server1 to Server2.

- I have a sql login (monitoruser) in Server2 with sysadmin role
- and created a ODBC connection (perfmon) in Server1 using monitoruser (successfully tested)
- created a datacollector set on Server1 running as local administrator (localMachine\sqlmon)

When I an trying to start this collector set, it throws "Error: Call to SQLAllocConnect failed with %1" on Server1

On Server2 Eventlog says,
Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: xx.xx.xx.xx]

I have both the server running in mixed mode (I also restarted sql services on both)

I can connect Server2 from Server1 using SSMS with monitoruser login

What else I am missing here?

Thanks in advance
Post #1303434
Posted Monday, May 21, 2012 9:39 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Are there any other SQL Server instances on these two servers? Is it possible that you are actually connecting (or trying) to one of them and that SQL instance has Windows-only authentication.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1303824
Posted Tuesday, May 22, 2012 4:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:52 AM
Points: 114, Visits: 991
Thank you RBarryYoung

No, there is no other sql instance, only default instance
Post #1304010
Posted Tuesday, May 22, 2012 2:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:52 AM
Points: 114, Visits: 991
OK I tried to setup on both the server in same domain, still it gives me same error.

Anybody has tried to store perfmon data into remote SQL server using odbc (and sql login)?
Post #1304489
Posted Tuesday, May 22, 2012 2:04 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 23,240, Visits: 31,937
Daxesh Patel (5/21/2012)
I am trying to do following task and I donot know it is possible in this setup or not?

I am trying to setup performance counter on a non-domain server (Server1)
All the performance counter values I want to capture in other sql server (Domain\Server2)

In this task I donot want to establish any kind of connectivity from Server2 to Server1 (sql or windows), but I want to push data from Server1 to Server2.

- I have a sql login (monitoruser) in Server2 with sysadmin role
- and created a ODBC connection (perfmon) in Server1 using monitoruser (successfully tested)
- created a datacollector set on Server1 running as local administrator (localMachine\sqlmon)

When I an trying to start this collector set, it throws "Error: Call to SQLAllocConnect failed with %1" on Server1

On Server2 Eventlog says,
Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: xx.xx.xx.xx]

I have both the server running in mixed mode (I also restarted sql services on both)

I can connect Server2 from Server1 using SSMS with monitoruser login

What else I am missing here?

Thanks in advance


How do you have the ODBC connection configured? Can you show it to us?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1304492
Posted Tuesday, May 22, 2012 2:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:52 AM
Points: 114, Visits: 991
Thanks Lynn,

I have attached word doc with screen shots


  Post Attachments 
odbcSteps.docx (20 views, 147.07 KB)
Post #1304522
Posted Tuesday, May 22, 2012 2:37 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 23,240, Visits: 31,937
Only thing I can suggest at this point is to double check your server properties.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1304532
Posted Tuesday, May 22, 2012 5:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:52 AM
Points: 114, Visits: 991
Server properties looks fine

It is single instance (default only) SQL server
Mixed mode (using sql logins since long time on this server)
Login which I am using in odbc is active and it works fine when I am connecting using SSMS

Only it does not work when perfmon tries to use odbc (remote sql)

That's why I asked, has anybody setup perfmon writing counter data to remote sql server? any special setting required for that?

And yes, I verified with our network admin, nothing is blocking at firewall side
Post #1304622
Posted Wednesday, May 23, 2012 2:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:52 AM
Points: 114, Visits: 991
I think I found the issue (not the solution)

odbc does not store sql login information (UID & PWD), and user needs to supply uid and pwd while using the odbc.

Only windows authentication works when PerfMon uses odbc to store data in SQL server

I think I cannot send Perfmon data to a different domain sql server since I cannot use windows authentication in this case.

I need to find out some workaround
Post #1305361
Posted Wednesday, November 14, 2012 10:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 29, 2013 4:22 AM
Points: 3, Visits: 11
Hi Daxesh,

I've been trying to do a similar thing to you - logging performance data to SQL for two separate machines, which were not part of a domain and have come to the same conclusion that you're unable to use SQL Server Authentication.

Was just wondering if you had come across a workaround in the meantime?

Thanks,

Craig
Post #1384765
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse