Register a SPN for SQL Server Authentication with Kerberos

https://www.mssqltips.com/sqlservertip/2955/register-a-spn-for-sql-server-authentication-with-kerberos/

Register a SPN for SQL Server Authentication with Kerberos

SQL 서비스 시작 시 서비스 계정으로 SPN등록이 자동으로 이루워진다.

exec xp_readerrorlog 0,1,N'SPN'

아래는 정상적으로 등록이 안될 경우를 보여준다.

 

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/XXXXXX ] for the SQL Server service. Windows return code: 0x2098, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/XXXXXX :1433 ] for the SQL Server service. Windows return code: 0x2098, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

 

select session_id,net_transport,client_net_address,auth_scheme 
from sys.dm_exec_connections

위의 쿼리를 통해서 연결들이 어떤 인증을 사용하는지 확인이 가능하다.