Re: Has anyone got delegation to work???
From: Jasper Smith (jasper_smith9_at_hotmail.com)
Date: 08/19/03
- Next message: Jasper Smith: "Re: SSL Encryption"
- Previous message: Sue Hoegemeier: "Re: new database user has no access rights to any table"
- In reply to: Kevin Brooks: "Re: Has anyone got delegation to work???"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Tue, 19 Aug 2003 20:13:11 +0100
I would use ADSI edit on the SQL service accounts to set the SPN rather than
setspn. Below is my "stock" answer for this but it should hopefully help
you. The kerberos logging is very useful if you can get it on (requires
reboot) the client and the servers as the error it gives is extremely useful
to pinpoint exactly where the problem is
It can be a serious PITA and yes the various docs are confusing :-)
First of all I wouldn't use setspn, I tend to use ADSI Edit
It's in the 2k support tools on any 2k server CD. However if you
want to use setspn then the syntax is :
setspn -A MSSQLSvc/SQLNLB02.DOMSQL.COM:1433 NLBSQL02Svc
This is for a server called SQLNLB02 in the domain DOMSQL.COM
with a SQL Service account of DOMSQL\NLBSQL02Svc
You must also be able from the client to resolve the FQDN of the servers
involved using ping -a servername i.e. it must return
Pinging SQLNLB02.DOMSQL.COM [xxx.xxx.xxx.xxx]
and not
Pinging SQLNLB02 [xxx.xxx.xxx.xxx]
Regardless of what anything else says, you just need to set up SPN's
for the service accounts of the two SQL Servers involved. e.g.
I have 2 servers and 2 service accounts as below
Server1 : SQLNLB01
ServiceAccount : NLBSQL01Svc
Server2 : SQLNLB02
ServiceAccount : NLBSQL02Svc
Using ADSI Edit right click on the NLBSQL01Svc in the Users
container and choose Properties.In the select a property to view
listbox choose servicePrincipalName and then add a SPN like so
MSSQLSvc/SQLNLB01.DOMSQL.COM:1433
(where the FQDN of the server is the server that uses the account
I'm editing as it's SQL Service account). Do the same for the second
server and you should be up and running.
What I find really useful is enabling Kerberos logging on all the
computers involved. This will write to the event log and you'll be
able to see exactly why it's failing. .
To enable Kerberos logging look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;262177
If you've got AD set up then it's generally a malformed SPN or
poor name resolution (make sure you can ping -a the server IP
addresses and get back a FQDN and not just a server name)
This article also has some good stuff about Kerberos and SSPI
http://support.microsoft.com/default.aspx?scid=kb;en-us;811889
and this one lists some of the kerberos errors you might see
http://support.microsoft.com/default.aspx?scid=kb;EN-US;230476
-- HTH Jasper Smith (SQL Server MVP) I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Kevin Brooks" <jeepnreb@yahoo.com> wrote in message news:uh1ddybZDHA.2352@TK2MSFTNGP12.phx.gbl... Yeah I am trying to set up and it is a PITA. I am still getting -- Server: Msg 18456, Level 14, State 1, Line 1 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Here is what I have done so far on test servers -- 1. Both servers are running only TCP/IP 2. My account has "Account is sensitive and cannot be delegated" cleared 3. Both servers have "Computer is trsuted for delegation" checked 4. SQL startup account has "Account is trusted for delegation" checked(same account on both servers) 5. DomainAdmin ran following -- setspn -A MSSQLSvc/backup1.<domain>.com <domain_here>/<account_here> setspn -A MSSQLSvc/backup2.<domain>.com <domain_here>/<account_here> --not sure if these are right. Should thet be MSSQLService or <domain_here>/<account_here> format setspn -A MSSQLSvc/backup1.<domain>.com:1433 MSSQLService setspn -A MSSQLSvc/backup2.<domain>.com:1433 MSSQLService 6. --on first server exec master..sp_addlinkedserver @server = 'backup2' , @srvproduct = 'SQL Server' exec master..sp_addlinkedsrvlogin @rmtsrvname = 'backup2' , @useself = 'true' --on second server exec master..sp_addlinkedserver @server = 'backup1' , @srvproduct = 'SQL Server' exec master..sp_addlinkedsrvlogin @rmtsrvname = 'backup1' , @useself = 'true' Thanks. "Jasper Smith" <jasper_smith9@hotmail.com> wrote in message news:#ECMnoaZDHA.2632@TK2MSFTNGP12.phx.gbl... > Yes, we use it as the standard for linked servers. It can be a PITA to set > up, seems harder than it needs to be :-) > It is very reliant on your domain and name resoloution being setup > correctly. What problems are you encountering ? > > -- > HTH > > Jasper Smith (SQL Server MVP) > > I support PASS - the definitive, global > community for SQL Server professionals - > http://www.sqlpass.org > > "Kevin Brooks" <kbrooks@sagetelecom.net> wrote in message > news:etSnBC2YDHA.2284@TK2MSFTNGP10.phx.gbl... > I have been tinkering the past couple of days, with little success. I was > wondering if it does work for anybody else in a production environment, not > a test. Thanks. > > Kevin > > >
- Next message: Jasper Smith: "Re: SSL Encryption"
- Previous message: Sue Hoegemeier: "Re: new database user has no access rights to any table"
- In reply to: Kevin Brooks: "Re: Has anyone got delegation to work???"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
