Re: SPN Requirement

Shouldn't SQL Server 2005 automaticaly register a SPN for its service
"MSSQLSvc"?

Well in my latest installation it didn't. And I get Event 26037 everytime
SQL Server starts.

So I read all the articles Sue Hoegemeier links to (and several more ...)
without finding indepth documentation on the setspn.exe utillity.

I created a domain account SQLSrvRunAs under which SQL Server is running.
I raised my Domain to "native Windows 2003".

To get a "Delegation" tab on the SQLSrvRunAs-account in AD Users and
Computers I (had to) run
setspn -A DUMMYSvc/ServerName DomainName\SQLSrvRunAs

(setspn said: "updated"; but the SPN was not listed in setspn -L
Domain/ServerName)

On the Delegation tab I would've granted delegation to MSSQLSvc on
ServerName to the SQLSrvRunAS-account - but there were no such service in the
list for my SQL Server ?!

So I ran:
setspn -A MSSQLSvc/ServerName DomainName\ServerName
setspn -A MSSQLSvc/ServerName.DomainName.local DomainName\ServerName.

Then I could grant delegation to MSSQLSvc on ServerName to the
SQLSrvRunAs-account

But still No Cigar!

I haven't to date found any information on verifying the SPN-settings?

I have tried
setspn -A MSSQLSvc/ServerName DomainName\SQLSrvRunAs
setspn -A MSSQLSvc/ServerName.DomainName.local DomainName\SQLSrvRunAs

and all sort of other maneuvres - but yet without much luck

- Bo

--
No signature


"Sue Hoegemeier" wrote:

Kerberos needs an SPN so if you want to use Kerberos, you'd
need to have an SPN. Kerberos is more secure than NTLM.
Additionally, some OS features such as delegation require
Kerberos. In the SQL world, delegation is needed when using
linked servers with Windows Authentication. You can pass
Kerberos tickets to the other server for authentication.
Without this you would authenticate with NTLM where the
credentials can't be passed from server to server and you
run into the "double hop" issue.
You register an SPN using the setspn utility. You can find
information on the utility at:
http://technet2.microsoft.com/WindowsServer/en/Library/b3a029a1-7ff0-4f6f-87d2-f2e70294a5761033.mspx?mfr=true

You can find a good overview of SQL Server and Kerberos at:
http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx

-Sue

On Thu, 28 Sep 2006 12:11:01 -0700, Brett S.
<BrettS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I need to know how and why to create an SPN for a default instance onf
SQl2005. Does it have anything to do with Active Directory? I want to use
kerberos or NTLM authentication.

Thanks


.

Relevant Pages

  • Re: Using NT Authentication with Linked Server
    ... You are running into a double hop (or delegation) scenario. ... User trying to connect to SQL Server is not sensitive and can be ... how to register SPNs for your SQL Service account). ... Use sp_addlinkedsrvlogin on the first linked server (server B in your ...
    (microsoft.public.sqlserver.security)
  • RE: SBS 2003 Unable to connect to database STS_Config
    ... Uninstall the SQL server from the SBS 2k3 server from add/remove programs ... Uninstall Microsoft SQL Server Desktop Engine (SHAREPOINT) ... If AV software install any extra IIS virtual directory, ...
    (microsoft.public.windows.server.sbs)
  • Re: Memory issues with 64-bit SQL Server 2005 on 64-bit Win 2003 C
    ... I also checked the individual patch levels for the .NET drivers, SQL Server ... The SQL Server is fully patched, however Windows Update reported that the OS ... Lock pages in memory -- I guess you might have taken care of it as well. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Linked Servers - setspn - domain account
    ... First of all I wouldn't use setspn, I tend to use ADSI Edit ... This is for a server called SQLNLB02 in the domain DOMSQL.COM ... I'm editing as it's SQL Service account). ... Jasper Smith (SQL Server MVP) ...
    (microsoft.public.sqlserver.security)
  • RE: migrating from wmsde to sql server
    ... Click Start, point to All Programs\Microsoft SQL Server, and then click ... then click New SQL Server Registration. ... Microsoft CSS Online Newsgroup Support ... This newsgroup only focuses on SBS technical issues. ...
    (microsoft.public.windows.server.sbs)