May 21, 2012 at 8:59 am
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
May 21, 2012 at 9:39 pm
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]
May 22, 2012 at 4:06 am
Thank you RBarryYoung
No, there is no other sql instance, only default instance
May 22, 2012 at 2:02 pm
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)?
May 22, 2012 at 2:04 pm
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?
May 22, 2012 at 2:30 pm
Thanks Lynn,
I have attached word doc with screen shots
May 22, 2012 at 2:37 pm
Only thing I can suggest at this point is to double check your server properties.
May 22, 2012 at 5:06 pm
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
May 23, 2012 at 2:23 pm
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
November 14, 2012 at 10:32 am
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:-)
November 14, 2012 at 1:05 pm
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
November 14, 2012 at 3:03 pm
Thanks greatly for the speedy reply! I'll experiment with that method later.
Best wishes to you,
Craig 🙂
November 15, 2012 at 12:42 pm
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 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply