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