sql instance sending machine name instead of login name to remote instance

Question

 

I am doing a SQL Service broker basic programs from msdn tutorial.

I created a endpoint with windows authentication in sql server( machine1 ).

When I try to connect endpoint from another SQL server instance ( machine 2) . The instance sends the machine_name instead of my windows user name to the endpoint on machine1. So I getting the following error

Service Broker login attempt by user 'mahinename$.' failed with error: 'Connection handshake failed. The login 'domain\machinename$' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 10.xxx.10.xxx]

I want to make the sql server to send my windows credentials.

thanks in advance,

Vytheese

Answer 1

The two SQL Server instances will authenticate using the service account irrelevant of your login account. You must grant connect permission to the service account. If the service account is 'localsystem' or 'network service' then Kerberos/NTLM authentication will authenticate the machine domain account ('domain\machine$') and this is the account needed to be granted connect permission.

Answer 2

Thanks Remus,

 

When I changed the endpoint authentication to

 

Windows[kerberos]  -  result:  source system unable to find the destination pc.

Windows[NTLM] -       result:  the same error "'domain\machine$' is not authenticated.

 

 

ALTER ENDPOINT InstTargetEndpoint

STATE = STARTED

AS TCP ( LISTENER_PORT = 4022 )

FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS [KERBEROS] );

GO

 

 

Any inisght will be very helpfull.

 

Grazie

 

Answer 3

How to GRANT authenticate  domain/machineName$  in SQL server.

 

I used

 

grant connect on InstTargetEndpoint to N'domain\machineName$'

 

 

 Since I don't have the mahcine Name account in security. The above query throws error.

 

Incorrect syntax near 'domain\machineName$'

 

 

Thanks

Answer 4

Check books online first for syntax errors. The GRANT takes a principal name, not a string. You will obviously have to create the principal in the first place, using CREATE LOGIN.

Answer 5

 

I used

 

exec sp_grantlogin [domain\machineName]

 

 [OR]

 

create LOGIN [domain\machineName]  FROM WINDOWS;

GO

 

and allowed permission for the created login to CONNECT the endpoint.

 

use master;

grant connect on endpoint::InstInitiatorEndpoint to [domain\machineName];

 

The above procedure has to be done on both instance of the SQL Server.

 

Working fine.

 

Thanks a lot for your guidance.