Posted by: Colin Smith
Database Administration, DBA, MSSQL Server
In my last post about the disabled sa account I mentioned that I would have more on why I could not use windows authentication to get to my SQL Server instance. Here is the short of it and I know many of you will be screaming at the screen as you read this. Again this is a server that I have walked into and I did not configure it but I think that now I will be able to resolve all the issues that we have. So my server stopped accepting logins from all domain accounts including mine. We did have one sql account that could connect to the instance but that account was limited in what it could see. Only a member of public on the server. This server is very critical and could not be brought down because after some application configuration changes users were able to work using sql authentication.
In our troubleshooting the error logs lead us down the path of Kerb auth failures. MS agreed with this assessment after looking at the logs and the behavior of the instance. We finally came to a point where the server had to be rebooted in order to continue troubleshooting. I thought I would take this time to also enable the sa account and see if we could get in with an elevated account and just check out some sql server configuration. Unfortunatley I was not able to get the account up during our small window. I brought the server up in single user mode using the -m switch in the startup parameters but when attempting to connect via sqlcmd I got errors saying only one admin was allowed to log in. My time ran out and we had to start back up in multi user mode. The reboot did not resolve the issue and I still have no sa account. After another day of troubleshooting we decide to backup the database after hours and restore to another server that is not having the authentication errors.
After we get that done I can now play with the srever in question and attempt to fix it. I still want to get sa up so I restart in single user mode but still get the connection error using sqlcmd. I disconnect the network and try again and I am in. I enable the account and start up in multi user mode. What I say next was shocking and something that NEVER should have happend. I open up Logins and I show that all of the domain acounts are gone. Some one must have removed them. I added my account back and I can now connect. OUCH talk about a lesson in security. Time to make some changes. More on that later.
But please do not let something like this happen to you. Follow the security best practices so you can prevent this from happening to you. To it from the start and do not back down. I have found that if someone tells you to just let them have sysadmin now and we will lock it down later, IT NEVER HAPPENS!! Be strong. As a DBA you are in charge of making sure that the data is safe, recoverable, and accessible. In order to do this job well, you must set up security accordingly.