Someone posted a note recently that they had removed the BUILTIN/Administrators from SQL Server and then couldn't log in anymore. If this happens in Windows, and you lose the admin password, you're re-installing. I thought that would be the case in SQL Server. If you don't have a Windows login with sysadmin rights and you don't know the SA password (or you're in Windows mode), then you need to reinstall, right?
Apparently not. There's a back door in that you can start SQL Server in single user mode, and if you are an administrator, you can connect and you'll be a sysadmin. You can then fix your mistake and go happily on your way. That's pretty cool, and it's a good thing, right?
I'm not so sure. What if you've encrypted your salary information using SQL Server's encryption capabilities and the DBA doesn’t have rights to the key. You monitor and audit things, and so you're aware if anyone changes the security. Suppose we have a savvy Windows admin. They could restart the SQL instance in the middle of the night, log on in single user mode, and conceivably change the security, and give themselves, or maybe some generic account like "sa", rights to the encryption keys. Or rights to tables, or anything else. Any Windows administrator could get rights to anything in SQL Server.
And if they logged onto Windows as "administrator", you won't know who they are.
Admittedly this isn't a huge security hole. Windows administrator access is required, but I still think this is a problem. The access to SQL Server, and to data inside, should be secured, and audited. Allowing a generic account to access the instance, especially with sysadmin rights, is a security hole that needs to be plugged. This type of backdoor, while possibly handy, will come back to haunt us at some point in the future.
Steve Jones
The Voice of the DBA Podcasts
The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.
You can also follow Steve Jones on Twitter:
or now on iTunes!
- Windows Media Podcast - MB WMV
- iPod Video Podcast - MB MP4
- MP3 Audio Podcast - MB MP3
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
