August 13, 2016 at 6:20 am
Hello, I am trying to configure Multi Server Administration so I can run a SQL ob across multiple server. When I run through the Master Server Wizard and try to add target servers I get the following errors. I have added the SQL Agent account as a local administrator to the target server. Any ideas?
Enlist TSX Progress
- Create MSXOperator (Success)
Checking for an existing MSXOperator.
Updating existing MSXOperator.
Successfully updated MSXOperator.
- Make sure the Agent service for 'srv-95-sqlmon' is running (Error)
Messages
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlib)
- Ensure the agent startup account for 'srv-95-sqlmon' has rights to login as a target server (Error)
Checking to see if the startup account for 'srv-95-sqlmon' already exists.
Messages
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlib)
- Enlist 'srv-95-sqlmon' into 'srv-95-sqlmon' (Error)
Enlisting target server 'srv-95-sqlmon' with master server 'srv-95-sqlmon'.
Using new enlistment method.
Messages
MSX enlist failed for JobServer 'srv-95-sqlmon'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.6020.0+((SQL11_PCU_Main).151020-1526+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot enlist into the local machine. (Microsoft SQL Server, Error: 14297)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2000&EvtSrc=MSSQLServer&EvtID=14297&LinkId=20476
August 17, 2016 at 8:21 am
Target instance agent service needs to have access to your master, so the error you're getting is most likely caused by the target not being able to connect to your MSX instance (master).
Please ensure that:
1. Target instance agent service is running as AD user (can't be Network service or Local System account)
2. Add target instance agent service account into following role on master msdb: TargetServersRole
You can enlist a target from master as well as from target. I find the enlistment from target more reliable.
I'm assuming you already changed the encryption settings for the target, if not, you need to run that first:
--CHANGE ENCRYPTION
DECLARE @encryptvalue int
DECLARE @keyinput varchar(200)
SET @keyinput = 'SOFTWARE\Microsoft\MSSQLSERVER\SQLServerAgent'
EXECUTE xp_instance_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@keyinput,@value_name='MsxEncryptChannelOptions',@value =@encryptvalue OUTPUT
SELECT @encryptvalue
IF @encryptvalue = 2
BEGIN
PRINT 'SQL agent encryption level set to '+CONVERT(VARCHAR(1),@encryptvalue)+'. This will be changed to 1 for multi-server administration.'
EXECUTE xp_instance_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key=@keyinput, @value_name='MsxEncryptChannelOptions',@type='REG_DWORD', @value=1
END
ELSE
BEGIN
PRINT 'SQL agent encryption level already set to '+CONVERT(VARCHAR(1),@encryptvalue)+'. Ready for multi-server administration.'
END
--ENLIST TARGET SERVER
USE msdb
GO
sp_msx_enlist @msx_server_name = 'yourmasterinstance'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply