| Author | Messages | |
ken
Posts:58
 | | 09/24/2008 1:21 PM |
| Sp_dropserver and sp_addserver are SQL Server things - not related to Kerberos. Usually you use sp_addserver to add an alias (e.g. (local)) that points to the local SQL Server. This means you that you can login to the server as (local) without having to know the SQL Server's actual name. The alias mapping (local) -> SQL Server name is stored in SQL Server's master database. You use the sp_dropserver to drop the alias, and sp_addserver to add a new alias. You do this if you have changed the SQL Server's NetBIOS name.
The Kerberos error you are getting is usually caused because the service ticket that the client has received from the DC can not be decrypted by SQL Server. This is usually because it was encrypted using the wrong password. The wrong password is used when the SPN for SQL Server service is registered under the wrong account in AD. So, the DC encrypts the service ticket with the wrong password, the client gives that service ticket to SQL Server, and SQL Server says "sorry, this ticket can't be decrypted using my password so it's possibly been modified/tampered with"
So, things to check:
a) Are you running SQL Server under "Localsystem" or a custom domain account? If "Localsystem" then the SQL Server SPN must be registered under the machine account in AD. If a custom user account, then under that user account
b) Are you running a default instance of SQL Server? Or a named instance? If a named instance what TCP port is it running on? You need to include the TCP port in the SPN if it's not the standard 1433. In fact, I think you should include the port even if you are using 1433: MSSQLSvc/machinename:1433
c) Your SPN needs to use whatever hostname your application is using to connect. I note that you have the netbios name listed below (mssqlsvc\machinename). What about the FQDN? MSSQLSvc\machine.domain.local:1433
After changing/updating any SPNs, you'll need to wait for replication to occur. Then purge any Kerberos tickets that the application server might already have for SQL Server (so that it is forced to get an updated one from DC)
Cheers
Ken
From: ActiveDir-owner@mail.activedir.org [mailto:ActiveDir-owner@mail.activedir.org] On Behalf Of Ramon Linan
Sent: Wednesday, 10 September 2008 7:17 AM
To: ActiveDir@mail.activedir.org
Subject: RE: [ActiveDir] kerberos error.EVENT ID 4.KERBEVT_KRB_AP_ERR_MODIFIED
Hi Rajeev,
I was finally able to add those entries.
Now I have
mssqlsvc/machinename
Host/machine
Host/machine.dnsname
I still have an application that Is failing connecting to the MS sql server though
I don't understand the part about sp_dropserver and sp_addserver, what is this for? When I drop the server, will that creates any problem for the application that needs access to MS sql server?
Thanks sooo much
ramon
From: ActiveDir-owner@mail.activedir.org [mailto:ActiveDir-owner@mail.activedir.org] On Behalf Of Chauhan, Rajeev
Sent: Tuesday, September 09, 2008 11:58 AM
To: 'ActiveDir@mail.activedir.org'
Subject: RE: [ActiveDir] kerberos error.EVENT ID 4.KERBEVT_KRB_AP_ERR_MODIFIED
You can add spn entries using setspn -a
Reset is just like joining the machine back to domain.
There are few thing you should check. Duplicate DNS entries, multiple or missing spn Also check nltest and windows time services
From: ActiveDir-owner@mail.activedir.org [mailto:ActiveDir-owner@mail.activedir.org] On Behalf Of Ramon Linan
Sent: Tuesday, September 09, 2008 11:33 AM
To: ActiveDir@mail.activedir.org
Subject: RE: [ActiveDir] kerberos error.EVENT ID 4.KERBEVT_KRB_AP_ERR_MODIFIED
The only entry I got was mssqlsvc/machinename, did not get any of the host entries.
What are the consecuenses of reseting that machine in the domain? This DB server is being use, among other things to host the content DB of Sharepoint.
Ramon
From: ActiveDir-owner@mail.activedir.org [mailto:ActiveDir-owner@mail.activedir.org] On Behalf Of Chauhan, Rajeev
Sent: Tuesday, September 09, 2008 11:06 AM
To: 'ActiveDir@mail.activedir.org'
Subject: RE: [ActiveDir] kerberos error.EVENT ID 4.KERBEVT_KRB_AP_ERR_MODIFIED
You need to run setspn -l machinename
You should see entries like mssqlsvc/machinenam:1433
Host/machine
Host/machine.dnsname
Can u rejoin or reset the machine account back to domain
Netdom reset machinename /domain:domainname
From: ActiveDir-owner@mail.activedir.org [mailto:ActiveDir-owner@mail.activedir.org] On Behalf Of Ramon Linan
Sent: Tuesday, September 09, 2008 10:54 AM
To: ActiveDir@mail.activedir.org
Subject: RE: [ActiveDir] kerberos error.EVENT ID 4.KERBEVT_KRB_AP_ERR_MODIFIED
Ok, so I run
C:\Program Files\Support Tools>ldifde -f dumpfile.txt -t 3268 -d DC=domain,dc=com -
l serviceprincipalname -r "(servicePrincipalName=*/db1*)" -p subtree
and find that there is an entry for db1 like
MSSQLSvc/db1.gst.com:1433
Is anything missing?
Thanks again.
Ramon
From: ActiveDir-owner@mail.activedir.org [mailto:ActiveDir-owner@mail.activedir.org] On Behalf Of Chauhan, Rajeev
Sent: Tuesday, September 09, 2008 10:23 AM
To: 'ActiveDir@mail.activedir.org'
Subject: RE: [ActiveDir] kerberos error.EVENT ID 4.KERBEVT_KRB_AP_ERR_MODIFIED
Kerberos is more related with computer account. Rather then sql. Use setspn command to check the spn.
Also check with -p subtree optiomm on ldifde
>From sql perspective as sql is bound to name u need to run sp_dropserver and sp_addserver as there would register entries
From: ActiveDir-owner@mail.activedir.org [mailto:ActiveDir-owner@mail.activedir.org] On Behalf Of Ramon Linan
Sent: Tuesday, September 09, 2008 10:12 AM
To: ActiveDir@mail.activedir.org
Subject: RE: [ActiveDir] kerberos error.EVENT ID 4.KERBEVT_KRB_AP_ERR_MODIFIED
Rajeev,
Thanks for your answer.
How do I check the spn?
I did a ldifde -f dumpfile.txt -d DC=domain,dc=com -l serviceprincipalname and got
servicePrincipalName: MSSQLSvc/DB1.domain.com:1433
When you say I need to change the server within sql aswell, what do you mean by that?
Thanks
Ramon
From: ActiveDir-owner@mail.activedir.org [mailto:ActiveDir-owner@mail.activedir.org] On Behalf Of Chauhan, Rajeev
Sent: Tuesday, September 09, 2008 9:48 AM
To: 'ActiveDir@mail.activedir.org'
Subject: RE: [ActiveDir] kerberos error.EVENT ID 4.KERBEVT_KRB_AP_ERR_MODIFIED
Check the spn
Was sql already there on the server if yes then u need to change the server within sql aswell
From: ActiveDir-owner@mail.activedir.org [mailto:ActiveDir-owner@mail.activedir.org] On Behalf Of Ramon Linan
Sent: Tuesday, September 09, 2008 9:44 AM
To: ActiveDir@mail.activedir.org
Subject: [ActiveDir] kerberos error.EVENT ID 4.KERBEVT_KRB_AP_ERR_MODIFIED
Hi,
A MS SQL Server (db1) that was decommissioned was put back in the network. This server was not a DC and was replaced by a new server with same NetBIOS name (db1).
We remove the server as soon as we saw the errors.
But now we cannot connect to that DB server and we get this error.
Windows Operating System
ID:
4
Source:
Kerberos
Version:
5.2
Symbolic Name:
KERBEVT_KRB_AP_ERR_MODIFIED
The kerberos client received a KRB_AP_ERR_MODIFIED error from the server %1. The target name used was %3. This indicates that the password used to encrypt the kerberos service ticket is different than that on the target server. Commonly, this is due to identically named machine accounts in the target realm (%2), and the client realm. Please contact
your system administrator.
Thanks
| | | |
|
|