Submit My Question - Recent Questions - Recent Answer

Msdn Tech >> SQL Server >> SQL Service Broker

what is the meaning of "64(error not found)" in sys.transmission_queue

as Christopher Yager say in "Need distributed service broker sample", I also test sending messages between two SQL Server 2005 instances,and after I setup the test environment with instance1 and instance2,I find queue [q2] in ssb2 can't receive message from ssb1. when I query by "select * from sys.transmission_queue",I get some message records that transmission_status is "64(error not found)".
I don't know what this mean,succeed or fail? can you help me ? thanks a lot.

also,I use sqlserver 2005 June CTP.

Anthony

[530 byte] By [Ant19768341] at [2008-1-12]

«« Where have the samples gone?
»» Help.ShowHelp from inside of a SYSTEM process

# 1

Transmission status should be blank in case of success, and the message should quickly dissapear from there (as soon as the target sends back an acklowledgment).

The error you see is an indication that the two machines can't connect and authenticate at the network level. Most likely this is a security authentication issue (the target does not autheticate or authorize the initiator, apparently). The best way to investigate such issues is to use the SQL Server Profiler. Start a new trace for each of the two machines, select the blank template and add the 'Broker/Broker Connection' and 'Security Audit/Audit Broker Login' event. The events should point toward what the problem is.

BTW, I'm surprised you get "error not found" for 64, this is a standard OS error: ERROR_NETNAME_DELETED - "The specified network name is no longer available."
What OS version are you running? Is it a localized version? Can you tell the version of the file wmerror.dll in your system32 directory?

HTH,
~ Remus

RemusRusanu at 2007-9-9 >

top of Msdn Tech,SQL Server,SQL Service Broker...

# 2

I use Profile to trace following your tips,it is no any response as expected.

I use Virtual Server trial 2005 EE and install win2003 server standard edition with sp1 to test,host is win2003 server standard edition(no sp1) also.
two sql2005 June CTP instances are installed on two Virtual Server and can conmunicate each other.

version of wmerror.dll is 10.0.0.3700.

Ant19768341 at 2007-9-9 >

# 3

Does the errorlog of the two SQL Servers contain anything about exceptions or login failures? (the errorlog is normaly located in "\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG")

Can the TCP connection be established between the two machines? To test this, use telnet.exe and try to connect, from each machine, to the other's machine listenning port (assumming the broker endpoints are configured on both machines for port 4022):
- on inititator, run this from a command prompt: telnet target_machine_name 4022
- on the target, run this from a command prompt: telnet initiator_machine_name 4022
If telnet succeeds, the TCP connection can be opened fine.

HTH,
~ Remus

RemusRusanu at 2007-9-9 >

# 4

thx for your reply.

but no any error is found in LOG,and telnet is successful each other.

I find when I run the query "select * from sys.transmission_queue" in initiator,transmission_status is not same every time,sometime it is blank,sometime is "64(error not found)",but [q2] in target is always empty.
I am sure I have set the route to each other, any idea? I have puzzled for many days.
Anthony

Ant19768341 at 2007-9-9 >

# 5

Oh,when I use sql profiler to trace broker,I get the response:

in Initiator:
Audit Broker Login 21 2005-07-14 20:05:07.347 1 1404 1 - Login Success
1 0X01 tcp://192.168.60.246:4022 ZLVM2\SQL2005
CERTIFICATE Microsoft Unified Security Protocol Provider Initiator 1

in Target:
Broker:Connection The network connection was aborted by the local system.
23 2005-07-14 19:48:43.743 1 998 8 - Receive IO Error
1 0X01 ZLVM3\SQL2005 1236 0ef4dcb6-30af-4b34-bfd6-54909473001e
tcp://192.168.60.245:0
Broker:Connection An operation was attempted on something that is not a socket.
23 2005-07-14 19:48:43.773 1 999 8 - Receive IO Error
1 0X01 ZLVM3\SQL2005 10038 0ef4dcb6-30af-4b34-bfd6-54909473001e
tcp://192.168.60.245:0

I don't know why the column "ObjectName" in Target profiler show "tcp://192.168.60.245:0",the port should be 4022 also.
How can I do ?

Ant19768341 at 2007-9-9 >

# 6

The port 0 is OK for the target side. What happens is that the target does not know (cannot know) that is connected to an initiator host that listens on the port 4022. Later in the connection handshake, if the authentication succeeds on both directions, the initiator may send to the target the port it listens on, and the target will know that this connection is to be used when sending back replies to the address 'tcp://192.168.60.245:4022'.

Based on the traces you put here, I believe that there is a problem on the initiator that it cannot access the certificate associated with the endpoint needed for authentication. the certificate must be available for the broker in the background, that means it must be encrypted using the database master key and the database master key must be encrypted using the service master key.
Can you run again the steps that create the database master key, the certificate and the broker endpoint on the initiator? Please make sure you drop these objects first:

DROP ENDPOINT <endpointname>;
DROP CERTIFICATE <certificateName>
DROP MASTER KEY;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master key password>';
CREATE CERTIFICATE <certificatename>
WITH SUBJECT 'ZLVM2\SQL2005 Identity';

BACKUP CERTIFICATE <certificatename>
TO FILE '<ZLVM2\SQL2005 cert location>';

-- Now import again the public key part on the target machine
CREATE ENDPOINT <brokerendpointname>
STATE = STARTED
AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE <certificatename>);
GO

Let me know if this helped.

HTH,
~ Remus

RemusRusanu at 2007-9-9 >

# 7

BTW, my suggestion on the initiator certificate issue is based on the hypothesis that you posted all the event seen in the profiler. Did you?

RemusRusanu at 2007-9-9 >

# 8

YEAH. All are wonderful after I re-initialize the Initiator following your tips,now the service broker can run well.thank you for your help, I think SQL server will get more and more users because of its power and easiness.
BTW,can I run the service broker without using certificate ?

Ant19768341 at 2007-9-9 >

# 9

Yes, you can use 'Windows' authentication. When using Windows authentication the Windows account running the SQL Server must be authenticated and authorized by the peer SQL Sever. The settings depend on how the SQL Server was installed and domain membership of the two machines involved.

Windows authentication is the recommended way for scenarios when the two SQL Servers are memebers of the same domain and belong to the same organisation. Certificate authentication is the recommended way for scenarios when the two SQL Servers are in distinct domains and belong to separate organisations, like when two businesses communicate over the internet using Service Broker.

You can also configure an enpoint to use both authentication modes (Windows and Certificate).

In order to configure Windows authentication you'll have to change the way you create the endpoint:

CREATE ENDPOINT <endpointname>
STATE = STARTED
AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS);

Also, on each machine you have to grant AUTHENTICATE permission on the endpoint to the Windows user that runs the peer SQL Server service. E.g. if the peer instance is running as <domainname>\<username>:

CREATE LOGIN [<domainname>\<username>] FROM WINDOWS;
GRANT CONNECT ON ENDPOINT::[<endpointname>] TO [<domainname>\<username>];

Altough this sounds simple, in practice there are always some problems. Usually, the two instances are installed as LocalSystem or NETWORK SERVICE. In this case, the instances will authenticate on the network as the 'machine' account. That is, for every computer that joins a domain, an account called '<domainname>\<machinename>$' is created. The peer has to create a login for this account and grant authenticate permission to it. A second issue is that using the machine account work only if Kerberos is properly configured. This requires the Service Principal Name used by service broker to be registered in the domain active directory. One can do this easily using the SETSPN.EXE program:

SETSPN -A MSSqlSvc/<machinename>:4022 <machinename>

However, this command might fail, depending on the security policies of the domain.

Given all these complications, you understand why almost always examples use CERTIFICATE authentication. However, for big enterprises there are advantages when using Windows authentication. For instance they can use domain groups and grant the AUTHENTICATE perrmission to groups rather than individual machines. Then simply adding the machine to the given group will allow it to connect.

HTH,
~ Remus

RemusRusanu at 2007-9-9 >

# 10

If the two machines are in a network without domain,can service broker run using windows authentication ?

For example,create a login [ZLVM2\Test] and user [ZLVM2\Test] in ZLVM2,and create a login [ZLVM3\Test] and user [ZLVM3\Test] in ZLVM3, run sql profiler on target machine ZLVM3,I will receive a message "the message could not be delivered because the security context could not be retrieved". but Audit Broker Login shows "1 - login succeed".

Ant19768341 at 2007-9-9 >

# 11

Yes, it can. If the two SQL Server instances are running as a local user with the same name and having the same password, Windows authentication will succeed.

The Broker Login events show that the two instances successfully authenticate each other and are able to exchange messages. This authentication refers only to the fact that the two instances are able to establish a network connection.

The Message Drop event shows that the messages are not accepted by the target database. You are now talking about dialog security, a separate topic from endpoint authentication. Dialog security is based solely on certificates. BOL has two 'How To's on this topic:
- Configure Target Service for Full Dialog Security
- Configure Initiating Service for Full Dialog Security

I recommend you turn on the 'Security Audit/Audit Broker Conversation' event in the profiler to see why the 'security context could not be retrieved'.

BTW, I recommend you start a new thread about this topic, the original title of this thread no longer reflects the topic being discussed :)

HTH,
~ Remus

RemusRusanu at 2007-9-9 >

SQL Server

SQL Service Broker

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech