message lost

Hi...

My service broker was working...all of a sudden it stopped working...so i figured out destination queue was disabled and I set it as

ALTER QUEUE SERVERQUEUE

WITH STATUS = ON, RETENTION = ON, ACTIVATION (

STATUS = ON, PROCEDURE_NAME = usp_OnReceiveMessage,

MAX_QUEUE_READERS = 5, EXECUTE AS OWNER )

Also altered my database for trustworthy after going through some posts previously.

Now the message is sent...i dont see any message in my tranmission queue but i dont receive the message in destination server.

When i checked endpoints...select * from sys.conversation_endpoints I see the last message with st_desc as conversing...

how do I solve this...?

Thanks,

Pramod

The queue must have been disabled because you must have rolled back a transaction that performed a RECEIVE on the queue 5 or more times consecutively. Read about poised message handling here.

If the message is neither in transmission queue nor in the destination queue, chances are that the dialog was errored. Look in the initiator queue as well. I don't get what you mean by "the last message" in sys.conversation_endpoints. The conversation endpoints view does not contain any messages. The st_desc column describes the state of the conversation endpoint.

|||

Rushi:

I reconfigured the service broker...this time...in transmission queue its showing this error....

Connection handshake failed. The certificate used by the peer is invalid due to the following reason: The database principal has no mapping to a server principal.
State 89.

What does this mean ?

Thanks,

|||

You are using certificates for adjacent (i.e. transport) security. The certificate used by the peer belongs to a user in master who is not mapped to a login. This could happen either because:

i) You created a user in master without login.

ii) You created a user in master with a login, but later dropped that login, thus orphaning that user.

The fix would be to create a SQL login, then create a user in master for this login and alter authorization of the peers certificate to be owned by this user. Next grant connection permission on the service broker endpoint to the newly created login.

Hope that helps.

|||

Rushi:

I want to confirm few things here...before proceeding...

Lets take a login sbuser(created to login with sql authentication). This is the login i would be using to create endpoints, certificates, service etc for server service broker.

Should I have to login as sa to create those or I am fine with sbuser ?

Next step...in master database I create a user lets say proxysbuser which will be used to connect endpoints on both sides ie.initiator and target service brokers.

Then I i use proxysbuser to alter authorization of the peers certificate.

Am I in the right track ? Let me know.

Thanks,

Pramod

|||

Configuring dialog security (i.e. between services) is independent of configuring adjacent security (i.e. between instances) and should not be confused. For configuring adjacent security the steps are as follows:

1> Instance A: Create certificate owned by the dbo of master.
2> Instance A: Create SSB endpoint using the above certificate for authentication.
3> Instance A: Export certificate to \\shared\A.crt

4> Instance B: Create certificate owned by the dbo of master.
5> Instance B: Create SSB endpoint using the above certificate for authentication.
6> Instance B: Export certificate to \\shared\B.crt

7> Instance A: Create login for instance B, let's say InstanceB.
8> Instance A: Create user in master for login InstanceB.
9> Instance A: Import certificate \\shared\B.crt into master with owner InstanceB.
10> Instance A: Grant connect permission on SSB endpoint to login InstanceB.

11> Instance B: Create login for instance A, let's say InstanceA.
12> Instance B: Create user in master for login InstanceA.
13> Instance B: Import certificate \\shared\A.crt into master with owner InstanceA.
14> Instance B: Grant connect permission on SSB endpoint to login InstanceA.

The steps for securing dialogs are different from this. Refer to the docs for that.

If you find this tedious, you can use Remus' Service Listings Manager which automates this process for you.

Rushi

|||

Rushi:

I have been trying to use Remus Service Listing I have been refering to this link...https://blogs.msdn.com/remusrusanu/archive/2006/04/07/571066.aspx

In this step The administrator of the SQL Server instance hosting service ‘A’

In import mode the created script throws this error....

Msg 6522, Level 16, State 1, Procedure ssb_create_certificate_from_blob, Line 0 A .NET Framework error occurred during execution of user defined routine or aggregate 'ssb_create_certificate_from_blob': System.Data.SqlClient.SqlException: A certificate with name 'COSCOSB' already exists or this certificate already has been added to the database.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServiceBroker.Samples.CertificatesBlob.CertificatesBlob.ssb_create_certificate_from_blob(SqlString databaseName, SqlString certificateName, SqlString authorizationUser, SqlBinary certificateBody)

The script which is throwing error is IF EXISTS(SELECT * FROM sys.certificates WHERE name = 'COSCOSB')
DROP CERTIFICATE [COSCOSB];
declare @.dbname sysname;
select @.dbname = DB_NAME();
print @.dbname -- this prints master
exec msdb.dbo.ssb_create_certificate_from_blob @.dbname, 'COSCOSB','COSCOSB',0x308201ED30820156A00302010202104B7566A1DEA72E844040F300FC947B6C300D06092A864886F70D010105050030353133303106035504031E2A0043004F00530043004F005F00430045005200540049004600490043004100540045005F005300550042301E170D3036303832373030303030305A170D3037303832383030303030305A30353133303106035504031E2A0043004F00530043004F005F00430045005200540049004600490043004100540045005F00530055004230819F300D06092A864886F70D010101050003818D0030818902818100D5DDA3F09C737A7F39E9E262C6B6C62524C3C8368750DC014AEE582FCDF97E29760C0452513414520FD8DB0A8CD3A389E15D0C5C5B006228145E405ADC2344ABF0DC08BF7647BEE413525C5E29B39107424479D70F25CD32F4FB95B43974E785C81410EF506EEC59E2C6CC6135F009300F5B496FF837E50AF8EB02F806CA99870203010001300D06092A864886F70D01010505000381810038C506E25FB74C59D08C58E56D9ED505A01AE0C33E5F28E5135D9E492F5E6B33703550E14042FF87EE83E8F8C43522EAEE9EDA417E7178BFB8BF7B5B27EA05926D4C52C9CAE9B7915A890422FCE67A4ED473E29154E1205A3CAF3245B0477A469192D409A0C5EB52BC307217CF7CA3C675F7305FC11503ECA17503EB1B493A4D;
GO

I dont have any certifcate in sys.certificates....why is it throwing error ? Could you pls tell me.

Thanks,
Pramod

.

|||

I think this is a known bug. I'm not sure if Remus has fixed it yet. You can try using the latest version and see if that works. Presently, Remus is on vacation, but I'll ask him to follow up on this bug when he gets back.

In the mean time, look at sys.certificates for any other certificate which may have been created (possibly with a different name).

Rushi

|||

Rushi:

Thanks for quick response...but I am sure i am using the latest version Service Lsiting Manager v1.1.3 which says that bug is fixed...sys.certifcates is empty. But will keep working on it.

Thanks,

Pramod

|||Are you in the right database?|||

Yup....I am in master....

|||

Rushi...my client db is standard sql2005 and server is developer sql2005 do u think that might be causing problem ?

|||No. The error is unrelated to licensing.|||On the correct instance? Service Listing Manager is simply running the SELECT * FROM sys.certificates query and finding a certificate but you are not.|||

Rushi:

Service Listing Manager is working fine in developer edition...I tried with 2 instances of standard edition on 2 different m/c it fails at this step exec msdb.dbo.ssb_create_certificate_from_blob @.dbname .......