MSSQL Mistake: administration

Showing posts with label administration. Show all posts

Wednesday, March 28, 2012

Multi server administration broken in SQL Server 2005

Hello,
When I try to setup multiserver administration for SQL Server I
always get the error 'The enlist operation failed (reason:
SQLServerAgent Error: Unable to connect to MSX 'my master server name')
(Microsoft SQL Server, Error 22026). I have a fresh installation of the
two instances (the one who must become the master and the one who must
become target. The sql service and sql server agent accounts are a
domain accounts (both servers sit in the same domain) and have full
administrative rights. I have enabled the tcp and named pipes network
library on both sql servers and applied SP1 on both servers.
Frankly I have tried to get this working from the first CTP until now
and never succeed, either I'm doing something wrong our multiserver
administration does not work at all on Sql server 2005.
Has someone managed to get this working on SQL Server 2005 and if so
how did he get it to work.
To be honest this is the third time I ask this question on this
newsgroup but I have never had a reply with a solution, so I'm tempted
to thing that this is broken on SQL Server 2005. If so I have to start
telling my students that this is one thing which does not work (unless
at least someone reports that it is working for him.
Thx.
Marc Mertensconfiguration for tsx/msx is set (by default) to only allow certified
servers. Google search on "msx2005" and /or security. There is a registry
entry you need to change to allow servers w/o certificates in msx/tsx
configuration.
"Marc Mertens" wrote:

> Hello,
> When I try to setup multiserver administration for SQL Server I
> always get the error 'The enlist operation failed (reason:
> SQLServerAgent Error: Unable to connect to MSX 'my master server name')
> (Microsoft SQL Server, Error 22026). I have a fresh installation of the
> two instances (the one who must become the master and the one who must
> become target. The sql service and sql server agent accounts are a
> domain accounts (both servers sit in the same domain) and have full
> administrative rights. I have enabled the tcp and named pipes network
> library on both sql servers and applied SP1 on both servers.
> Frankly I have tried to get this working from the first CTP until now
> and never succeed, either I'm doing something wrong our multiserver
> administration does not work at all on Sql server 2005.
> Has someone managed to get this working on SQL Server 2005 and if so
> how did he get it to work.
> To be honest this is the third time I ask this question on this
> newsgroup but I have never had a reply with a solution, so I'm tempted
> to thing that this is broken on SQL Server 2005. If so I have to start
> telling my students that this is one thing which does not work (unless
> at least someone reports that it is working for him.
> Thx.
> Marc Mertens
>

Multi Server Administration (Error Enlist MSX)

With Sql-Server 2000 i am using since a long time the jobserver (MSX) with TSX-servers.

Now I installed Sql-Server 2005 Standard Edition (the same i have tested with developer edition) on NT-2003 SP1.

I have 2 Sql-Server instances installed.

Now i tryed to configure one instance as MSX-Server and the other one as TSX-Server.

No Chance. When i do it with Management-Studio the following error ist displayed:
The enlist operation failed (reason:SqlServerAgent Error: Unable to connect to 'MSX HG-ALV-012\I1'). (Microsoft Sql Server,Error:22026).

In the Agent-log i found after every try the following error-messages:

Date 06.12.2005 17:29:42
Log SQL Agent (Current - 06.12.2005 17:29:00)

Message
[298] SQLServer Error: 805, SSL Provider: Die Zertifikatkette wurde von einer nicht vertrauenswürdigen Zertifizierungsstelle ausgestellt. [SQLSTATE 08001]

[298] SQLServer Error: 805, Client unable to establish connection [SQLSTATE 08001]

Message in Eventlog:
Date 06.12.2005 17:29:42
Log Windows NT (System)

Source Schannel
Category (0)
Event 3221262354
Computer HG-ALV-012

Message
Das vom Remoteserver erhaltene Zertifikat wurde von einer nicht vertrauenswürdigen Zertifizierungsstelle
ausgestellt. Aus diesem Grund k?nnen keine der im Zertifikat enthalten Daten verifiziert werden. Die
SSL-Verbindungsanforderung ist fehlgeschlagen. Die angeh?ngten Daten enthalten das Serverzertifikat.
I don't know what this has to do with certificates? The Configuration for the connections on the server (tcp) is definitely set to "ForceEncrypten=No".

Both Sql Server instances are ru Tongue Tied nning under the same Domain-Account. (Also the Agents!)

Has anyone an idea how to solve that problem?

There is a good Webcast exactly on this topic recorded at Dec 15 2005.
Theme: Upgrading SQL Server Agent to SQL Server 2000.

EventID is 1032285816

Look at http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032285816&EventCategory=5&culture=en-US&CountryCode=US

Regards

Ralph Kemperdick