Service Listing Manager Question For Remus

Hi Remus - Thank you for your Service Listing utility. I am very new to S.B. I had an intern working for us do some research work into S.B. and I gave him your utility to use for the work I gave him to do. Here is what I am trying to do. Very simply, I want to setup a remote (target) server that many other source (initiator) servers will send messages to via S.B. I had the intern learn how to have a single initiator server send messages to the remote server. He used your utility to create the SQL script that needs to be run on both servers to get things set up, and it works correctly.

The script below is what your utility created to be run on the remote server. The reference to 'SDDev2' below is a reference to the initiator (source) server my intern was working against. Not knowing much about S.B., my concern by looking at the T-SQL below is that when creating the required objects on the remote server, we are hard-coding a reference to a single initiator server ('SDDev2'). However, I need to be able to support messages coming from hundreds of initiator servers. What, if anything, do I need to do or change to support this?

Thanks very much - Amos

The TSQL script from your utility is below:

USE [master];
GO

CREATE LOGIN [SDDEV2] WITH PASSWORD = '6p}J4saGX=*b9Z';
ALTER LOGIN [SDDEV2] DISABLE;
CREATE USER [SDDEV2] FROM LOGIN [SDDEV2];
GO

declare @dbname sysname;
select @dbname = DB_NAME();
exec msdb.dbo.ssb_create_certificate_from_blob @dbname, 'SDDEV2','SDDEV2',0x308201BF30820128A003020102021089C68EF17EAA289B42BB56454CA195D7300D06092A864886F70D0101050500301E311C301A06035504031313506572736F6E4164647265737353656E646572301E170D3036303831343030303030305A170D3037303831353030303030305A301E311C301A06035504031313506572736F6E4164647265737353656E64657230819F300D06092A864886F70D010101050003818D0030818902818100BCFD0F7BEE09988F08B0AED98A750795755CB35207C2DA7A2F69618ECD8118CE885E7EBFE644263EE397B72ABB14DFA67E2C4518D6C37B021D1CDDBC1E9E7B8C33AA622FA2DE3474B3529A2F165ED791DFC31E930D6C7E65EB49B4F3162A2CD94B43D6BE4DBE792D11A4F7FFA7DE5DC82329CE683D18D90C6A8108F50D601BE10203010001300D06092A864886F70D01010505000381810041ED85516EF68F21CE0538F4A6D5F6BDF8AF76BFF509FE84FEC173D7EB19AA310E383285C501BDFF1F1A7AD69F404C6F8F7A9653B191062249ADAE85AD5F2ED073F197E4FB06DEA5B19BD80577F85518924640A40F0FE1481292C6E4F4F9ED5A52AB960A41A47D5501EC512BEF71135A1AA05BD29151466D29B41B921575B865;
GO

GRANT CONNECT ON ENDPOINT::[BROKER] TO [SDDEV2];
GO

USE [AdventureWorks_Tim];
GO

IF NOT EXISTS (SELECT * FROM sys.certificates WHERE thumbprint = 0xF6FD43253A7DD7AB1562FAA3F8BD03C0532E45EE)
BEGIN
CREATE USER [Proxy::PersonAddressSender] WITHOUT LOGIN;
declare @dbname sysname;
select @dbname = DB_NAME();
exec msdb.dbo.ssb_create_certificate_from_blob @dbname, 'CN=PersonAddressSender::7DD56F1CFAB41F9C4A46DA13368EB879','Proxy::PersonAddressSender',0x308201BF30820128A00302010202107DD56F1CFAB41F9C4A46DA13368EB879300D06092A864886F70D0101050500301E311C301A06035504031313506572736F6E4164647265737353656E646572301E170D3036303831363030303030305A170D3037303831373030303030305A301E311C301A06035504031313506572736F6E4164647265737353656E64657230819F300D06092A864886F70D010101050003818D0030818902818100B31EC8E14AF32003FBCEE27C201A8BAC9F34797DB1D6AB1405DEBE73807BEBB9A5536009B5A1A4A28D4461D51B488FBD7AF4D2F8C8A956F4186F05CA50C541B90B243C6CF4826A5DCE3A22E9FAC263D7407611E537031E67EC8318A90FE3789783C5F87179294E17CDE2C3DBED3F95353B089BA4FBC098FF36F2177F3627AE530203010001300D06092A864886F70D01010505000381810092C058712A17533D8A8C44D53863121D4108B982AE456093A1C95CDD37BE446122B117662785B5F151C21F70C948F686CB3CC5895000AE5D107EF0371373F9ED8EC43A227ECAFF79C57F740981D726E6511C3377BFE7B4CEE94CEF30EEDC4ACA43BCAD56A626A563F1B733666CB74A4122B7084C853E9F8D5EA0DBB224DC2DD3;
END
GO

DECLARE @certificateOwner sysname;
DECLARE @sql nvarchar(4000);
SELECT @certificateOwner = p.name
FROM sys.database_principals p
JOIN sys.certificates c ON c.principal_id = p.principal_id
WHERE c.thumbprint = 0xF6FD43253A7DD7AB1562FAA3F8BD03C0532E45EE;
SELECT @sql = N'GRANT SEND ON SERVICE::[PersonAddressReceiver] TO ' + QUOTENAME(@certificateOwner);
EXEC sp_executesql @sql;
GO

CREATE ROUTE [Route::PersonAddressSender,C73507DF-0914-4DF5-8074-14A8A00060D3] WITH
SERVICE_NAME = 'PersonAddressSender',
BROKER_INSTANCE = 'C73507DF-0914-4DF5-8074-14A8A00060D3',
ADDRESS = 'tcp://SDDEV2:4022';
GO