Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error while logging Performance Monitor counter to sql server


Error while logging Performance Monitor counter to sql server

Author
Message
Daxesh Patel
Daxesh Patel
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 1047
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
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
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."
Daxesh Patel
Daxesh Patel
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 1047
Thank you RBarryYoung

No, there is no other sql instance, only default instance
Daxesh Patel
Daxesh Patel
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 1047
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)?
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
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?

Cool
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)
Daxesh Patel
Daxesh Patel
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 1047
Thanks Lynn,

I have attached word doc with screen shots
Attachments
odbcSteps.docx (52 views, 147.00 KB)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
Only thing I can suggest at this point is to double check your server properties.

Cool
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)
Daxesh Patel
Daxesh Patel
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 1047
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
Daxesh Patel
Daxesh Patel
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 1047
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
craigvincent
craigvincent
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search