March 13, 2013 at 11:42 am
I have a 2008 R2 SQL Server that is not clustered. I changed the SQL Service Account username and password. All SQL Services start up but I am unable to connect using Windows Authentication. I can connect using a SQL Account with sysadmin rights. Admin rights are granted to a Domain Security Group (which I am a member). Is there a step I'm missing? I have changed the Service Account and passwords on other Servers without any errors.
March 13, 2013 at 12:29 pm
Did you change it through Configuration Manager or through the services control panel?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 13, 2013 at 12:31 pm
Let me see if I understand. You cannot login to SQL using windows auth. You can login when you login with the SQL service account. you're using that account through SQL auth. that specific account is also a domain account. You changed the Password on the service account and the services are up and running... but you cannot login with your account... which is gaining access to SQL via a group membership that the service is also using?
Did you change your account password?
Did you log out and back in after the change was made? (humor me on this one)
Are you using the service account by a SQL only account possibly?
You restarted the SQL Services and the Services come back up, but you cannot login personally with your account?
Does the group your in have SA in SQL?
//edit// When was the last time you successfully logged into SSMS on this server?
.
March 13, 2013 at 12:33 pm
What is the error you are getting?
I am going to guess that you are getting an error similar to SSPI cannot generate context. If so, then your problem is related to the SPN record that was created with the original account (probably a domain admin - or local system account). When you changed from that account, to a domain service account - the old SPN record was not removed and a new SPN record was not created.
Download and use the SetSPN.exe utility to inspect the SPN records. Remove the bad record and add valid records for the new domain service account.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 13, 2013 at 12:35 pm
I am using a completly different service account that is a local administrator on the windows server and is a member of the SQL Administrators group that has sysadmin on the SQL Server. I am also a member of that group. The SQL Services start up fine with no errors. But, when I try to connect using Windows Authentication I cannot connect. If I connect with the SQL Server sa account and password I can connect. The server is running in Mixed Mode.
March 13, 2013 at 12:35 pm
My guess was also the SPN being registered. I do have our System Admin looking into that option.
March 13, 2013 at 12:38 pm
The services start and run, SQL is up, you cannot login?
I would use the SA account and verify your account permissions. If SQL is up and running, I cannot think of any issue with the Service account. I would look more at your own account.
.
March 13, 2013 at 9:44 pm
cathy.baker (3/13/2013)
My guess was also the SPN being registered. I do have our System Admin looking into that option.
Look in the SQL Error Log for an entry that says whether the service was able to register an SPN for itself. If that changed after the service restart following the password change from the time before you can look into that possibility further. Also, by default failed logins are also logged to the SQL Error Log. Sometimes the State of those failed login errors tell a story. If you can attempt a login with your account that fails and then correlate that to the entry in the Error Log post the results here.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 13, 2013 at 10:00 pm
Please post the error message.
Regards,
Ram,
MCTS,MCITP
March 14, 2013 at 7:02 am
Thanks to everyone who replied. Our system administrators are looking into the SPN issue. The SPN is not registering correctly.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply