none
Configuring Service Broker between SQL Server 2008 and 2012 on Intranet

    Question

  • Hello, I would need help in configuring Service broker. As both servers are on the intranet, I wanted to remain the most simple so I used no certificates and allowed anonymous access but still, using SSBDiagnose, I can see errors.

    I would like to paste here my configuration and my usage of SSBDiagnose, I already asked a question about SSBDiagnose usage but this new question is rather on the usage of certificates and the configuration of SSB, for me to know if I am doing this in the best possible way.

    Reading on the web, I have read in few places that certificates are not mandatory and that Windows Authentication only can be used. Then, I read that even if endpoints don't request certificates, the communication between two servers will still requires certificates so I am wondering where is the truth... 

    I have two servers:

    • EmployeesSvr (SQL Server 2012 Enterprise Edition with Always On, EmployeesSvr is the listener name in front of two virtual servers)
    	CREATE MESSAGE TYPE [//E/S/ETChanged] VALIDATION = WELL_FORMED_XML
    	CREATE CONTRACT [//E/S/ECContract] ([//E/S/ETChanged] SENT BY INITIATOR)
    	CREATE QUEUE [dbo].[ECQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION (  STATUS = ON , PROCEDURE_NAME = [dbo].[SSB_ECQueueProc] , MAX_QUEUE_READERS = 1 , EXECUTE AS N'dbo'  )
    	CREATE SERVICE [//E/S/ECService]  ON QUEUE [dbo].[ECQueue] ([//E/S/ECContract])
    
    	CREATE ROUTE [RouteToSECService]   WITH  SERVICE_NAME  = N'//S/S/ECService' ,  BROKER_INSTANCE  = N'F...' ,  ADDRESS  = N'TCP://SoftwaresSrv.test.com:4022' 
    	CREATE REMOTE SERVICE BINDING [SECServiceBinding]  TO SERVICE N'//S/S/ECService'  WITH USER = [domain\SvcBrokerTestUser] ,  ANONYMOUS = ON 
    	CREATE ENDPOINT [ESBEndpoint] STATE=STARTED AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL) FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED, MESSAGE_FORWARD_SIZE = 10, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = DISABLED)
    
    • SoftwaresSvr (SQL Server 2008 R2)
    	CREATE MESSAGE TYPE [//E/S/ETChanged] VALIDATION = WELL_FORMED_XML
    	CREATE CONTRACT [//E/S/ECContract] ([//E/S/ETChanged] SENT BY INITIATOR)
    	CREATE QUEUE [dbo].[ECQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION (  STATUS = ON , PROCEDURE_NAME = [dbo].[SSB_ECQueueProc] , MAX_QUEUE_READERS = 1 , EXECUTE AS N'dbo'  )
    	CREATE SERVICE [//S/S/ECService]  ON QUEUE [dbo].[ECQueue] ([//E/S/ECContract])
    	CREATE ROUTE [RouteToECService]   WITH  SERVICE_NAME  = N'//E/S/ECService' ,  BROKER_INSTANCE  = N'2...' ,  ADDRESS  = N'TCP://EmployeesSvr.test.com:4022' 
    	CREATE REMOTE SERVICE BINDING [EECServiceBinding]  TO SERVICE N'//E/S/ECService'  WITH USER = [domain\SvcBrokerTestUser] ,  ANONYMOUS = ON 
    	CREATE ENDPOINT [SSBEndpoint] STATE=STARTED AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL) FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED, MESSAGE_FORWARD_SIZE = 10, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = DISABLED)
    
    • My SSBDiagnose command :
    ssbdiagnose -E CONFIGURATION 
    	FROM SERVICE //E/S/ECService 
    	-S EmployersSvr 
    	-d EmployersDB 
    TO SERVICE //S/S/ECService 
    	-S SoftwaresSvr 
    	-d SoftwaresDB 
    ON CONTRACT //E/S/ECContract
    • The result :
    Microsoft SQL Server 10.50.1600.1
    Service Broker Diagnostic Utility
    D  29978 EmployersSvr     EmployersDB             
    	No valid certificate was found for user domain\SvcBrokerTestUser
    
    D  29977 SoftwaresSvr  SoftwaresDB 
    	The user domain\SvcBrokerTestUser from database EmployersDB on EmployersSvr cannot be mapped into this database using certificates
    
    D  29933 SoftwaresSvr  SoftwaresDB 
    	The routing address TCP://EmployeesSvr.test.com:4022 for service //E/S/ECService does not match any of the IP addresses for EmployersSvr 
    
    	An internal exception occurred: An exception occurred while executing a Transact-SQL statement or batch.
    
    

    Thank you for any help, I am searching for several answers :

    1. Can I use the setup as I defined, with no certificate ?  Is it risky ?
    2. Is there too many objects defined ?  Is it mandatory to have a Route and a Remote Service Binding ?  I don't understand how those two are working togheter...
    3. Is it ok to use the same windows account on each side, do they only need an 'Open' access rigth or do they need to be db_owner ?

    Best regards,

    Claude

    Monday, June 23, 2014 3:01 PM

All replies