Error while logging Performance Monitor counter to sql server

  • Daxesh Patel

    Hall of Fame

    Points: 3208

    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

    SSC Guru

    Points: 143327

    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.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Daxesh Patel

    Hall of Fame

    Points: 3208

    Thank you RBarryYoung

    No, there is no other sql instance, only default instance

  • Daxesh Patel

    Hall of Fame

    Points: 3208

    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

    SSC Guru

    Points: 442365

    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?

  • Daxesh Patel

    Hall of Fame

    Points: 3208

    Thanks Lynn,

    I have attached word doc with screen shots

  • Lynn Pettis

    SSC Guru

    Points: 442365

    Only thing I can suggest at this point is to double check your server properties.

  • Daxesh Patel

    Hall of Fame

    Points: 3208

    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

    Hall of Fame

    Points: 3208

    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

    SSC Enthusiast

    Points: 111

    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:-)

  • Daxesh Patel

    Hall of Fame

    Points: 3208

    craigvincent (11/14/2012)


    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:-)

    Hi Craig,

    Sorry I didnot update this post after I resolved the issue

    - I added entry for remote domain machine in hosts file (C:\WINDOWS\system32\drivers\etc\hosts)

    - then created a windows account in both the machin with same UID + Password (as local administrator)

    - created SystemDSN for this connection

    Edit: - and run Perfom under this account

    This worked for me

  • craigvincent

    SSC Enthusiast

    Points: 111

    Thanks greatly for the speedy reply! I'll experiment with that method later.

    Best wishes to you,

    Craig 🙂

  • craigvincent

    SSC Enthusiast

    Points: 111

    Thanks again Daxesh, got that setup and working! It's kind of a shame that it can't be done using SQL Server Authentication, but am just glad it's working!

    C

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply