How to tell if SQL 2005 registers an SPN upon Startup

• Steve-443559

  SSCrazy

  Points: 2307

  More actions

  April 2, 2012 at 9:12 am

  #271272

  In SQL 2008 and up, I can see the following messages in the SQL Error log to tell if it's successful or not

  The SQL Server Network Interface library successfully registered the Service Principal Name

  OR

  The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service

  In SQL 2005, I can see if it's not successful in the Windows Event Log, but not if it is successful. I'd like to see something specific, rather than not seeing something to tell if it's successfully registered or not. Is there a way to do it? I heard that I could look at auth_scheme in sys.dm_exec_connections, but there's columns with KERBEROS even though the SPN is not successfully registered.

  How can I tell without a doubt that the SPN is registered in SQL 2005?

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  More actions

  April 2, 2012 at 4:24 pm

  #1467944

  You could interrogate the results of setspn for your hostname:

  setspn -L hostname

  You should see an entry for MSSQLSvc/<FQDN>:<tcpport> if it was successfully registered.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato