Service broker connection using windows auth fails unless service account is administrator at both ends

0

Topology:

Machine A - Windows Server 2008 - SQL 2008 R2 Dev SQL Server is running as mydomain\user1

Machine B -Windows 7 -SQL 2008 R2 Dev SQL Server is running as mydomain\user1

I have granted the service account at either end connect permissions to the endpoints on either SQL server. If I add the mydomain\user1 into the machine administrators group at either end, connections are made without a problem. If I remove it from these groups I receive the following message:

Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed). State 67.

Any help is appreciated. We are really unsure of the necessary minimum rights required. We are planning to eventually migrate this to production and I would not prefer the sql service to run as a local machine administrator. BTW I have gotten my server admins in the loop and the SPN's appear to be set up correctly to the best of their knowledge.

I asked the same question on the MSDN forums here.

sql-server
sql-server-2008
service-broker
asked on Stack Overflow Jul 14, 2011 by doug_w

1 Answer

0

If both machines are running as the domain user service account and you granted the necessary permission (CONNECT on the SSB endpoints) there is nothing else in the SSB configuration side you need to do. The error is an OS error and the best way to investigate is to use the Kerberos troubleshooting document: Troubleshooting Kerberos Errors.

The most likely cause though is a bad formatted SPN. Service Broker asks for and SPN like MSSQLSvc\<hostname>:<port>. It will format the requested SPN (DsMakeSpn) from the route address, so if you use FQDN in route then the SPN must be FQDN, if you use just hostname then SPN must be hostname based. The part also comes from the route, if omitted then is the default 4022.

answered on Stack Overflow Jul 14, 2011 by Remus Rusanu

User contributions licensed under CC BY-SA 3.0