SPNs

Question

Is there a way to check if one or more SPNs are being used by the SQL Server, or not?

---

--- Best Regards, Igor Santos Twitter: @sqlsantos Blog: sqlsantos.wordpress.com

Answer 1

Hello,

As per: http://blogs.msdn.com/b/psssql/archive/2009/02/13/searching-for-duplicate-spn-s-got-a-little-easier.aspx

You can use: setspn [-L] computername

Answer 2

Hey Don,

Our Junior DBA created a bunch of SPNs, we didn't catch that, and now I want to clean them up, but I'm not sure which ones are being used and which one are in fact being used.

Do you know of a way to see or know if an SPN has been used?

Let me know if you can think of anything.

Best Regards,

-Igor Santos

---

--- Best Regards, Igor Santos Twitter: @sqlsantos Blog: sqlsantos.wordpress.com

Answer 3

SPN are used for authentication, but not necessary that SQL is using them.  You SQL server needs following SPNs...

Default/Named Instances with Static Port

MSSQLSvc/SQLServer.FQDN Domain\ServiceAccount

MSSQLSvc/SQLServer:FQDN:Port Domain\ServiceAccount

Named Instances with Dynamic Port

MSSQLSvc/SQLServer.FDQN Domain\ServiceAccount

MSSQLSvc/SQLServer.FDQN:InstanceName Domain\ServiceAccount

To get listing of active SPNs...

SETSPN -L Domain\ServiceAccount

Cheers!

---

Mohit K. Gupta --- MCTS, MCITP, MCC2011
My Blog OpsVault My MSDN Blog Twitter @SQLCAN

Answer 4

Just to add to the above...

setspn -x      will search all duplicate SPNs in  the entire domain

In order to avoid creating duplicate SPNs, please use setspn -S  

it will not create the SPN when a one already exist.

Thanks,
Noam