August 23, 2006 at 2:57 am
Hello,
I have SQL Server running with one windows account and I didn't have problem to connect with windows authentication. After a while I wanted to change that windodws account with different one. But now I cannot connect with windows authentication. Only with SQL Server authentication.
Both windows accounts are domain administrators. The second one is copy of first (only name is different)
When I change it to first windows account again everything running properly. But with second one .. no way.
What to do? I must change service account from time to time.
August 24, 2006 at 6:00 am
I had s similar problem when changing service accounts in an Windows 2003 AD domain. The problem had to do with an Service Principle Name (spn) being registered under the old service account, which prevented the new service account from registering the spn. There a couple of ways to fix this:
1. Obtain setspn from Windows 2003 Support Tools, its available on Windows 2003 media and I think you can download it from Microsoft
2. Run setspn mydomamin\oldserviceaccount
3. You should see a list of spn's for all SQL Servers using the old service account:
MSSQLSvc/myserver.mydomain.com:1433
4. Try deleting one spn on a test machine using setspn -D "MSSQLSvc/myserver.mydomain.com:1433" Mydomain\oldserviceaccount
5. Now try starting the SQL service under then new account
6. If this doesn't work try registering an spn under the new service account
If you prefer GUI's you can edit spn's with ADSIEdit:
1. Go to Start Run >> MMC >> Add >> ADSIEdit (I think this is available on any server or workstation with Windows 2003 Admin or Support Tools installed.
Needless to say running SQL Server under a domain admin account isn't a good practice after you get things working try scaling back the permissions to at least local admin on the box. Also I had grant the new service account permissions to register spns to avoid issues on new installs.
August 24, 2006 at 6:43 am
This is wonderful. Thank you for help. It works.
September 25, 2006 at 2:24 pm
What permissions did you have to give for regular users? I have removed all old SPN entries and still get the Cannot generate SSPI error.
September 25, 2006 at 5:25 pm
A domain admin or local administrator context will automatically register the SPN. If the SQL is started with an account with less rights, then the SPN will need to be created manually. I ended up deleting the SPN and creating it manually using ADSI edit on the account.
September 26, 2006 at 6:49 am
I created the SPN but still get the SSPI error. The only difference is that I am trying to run SQL through Citrix.
September 26, 2006 at 2:14 pm
Just to make sure we're both on the same page, you're trying to change the Windows SQL Server service account and we're receiving an SSPI error?
September 26, 2006 at 3:17 pm
The service account is working. The account has not been changed. I am just trying to connect to a SQL2000 server using enterprise manager through citrix. As a Domain admin I have no problems but I am trying to figure out a way to allow students to work on their assignments from home.
September 26, 2006 at 4:51 pm
The troubleshooting described in this thread is related to changing a SQL service account and SSPI errors. For client SSPI errors unrelated to the service account go through the troubleshooting steps described in this KB article
http://support.microsoft.com/kb/811889/en-us
I also wrote a short article about SSPI errors before the above KB article was originally published, which may help:
http://www.sqlservercentral.com/columnists/cmiller/cannotgeneratesspicontext.asp
May 3, 2007 at 11:20 am
found this on another site. seems pretty good.
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=60&threadid=84680&STARTPAGE=1
_________________________
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply