Got a question from a reader yesterday:
“I just joined a new company and was given a VM on which my domain account was part of the Administrators group but the SQL Server 2008 R2 install was done by the IT team and even by using my Windows account, I cannot login to the instance. I tried using the “sa” account thinking that they might have left the password as blank but keep getting errors. How can I resolve it? My understanding was/is that if my Windows account is part of the Administrators group on the local machine/VM, I should be able to log in since the BUILTIN\Administrators group is always by default assigned sysadmin rights on a newly created instance. Is that no longer the case in SQL Server 2008 R2?”
Yes, as part of the new security features in SQL Server 2008, by default, the local Windows group “BUILTIN\Administrators” is no longer made part of the sysadmin fixed server role. You have to explicitly add it if you want that to be the case. It is very well documented in BOL and MSDN and we would recommend that you go through this article to familiarize yourself with the security changes in SQL Server 2008 R2:
While helping the user above to resolve the issue, it turned out that IT Admin had used a domain group that only IT team members were a part of and a) Installed in Windows Authentication mode, and b) Only made that group a part of the sysadmin role. The user above tried to get in by making changes to the registry to change the authentication to mixed mode by changing the value for this registry setting to 2:
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\LoginMode
Or in the case of a named instance:
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.n\MSSQLServer\LoginMode (n: Instance nbr of the named instance)
Post re-starting the service, it did change the authentication mode to mixed mode, however due to un-successful attempts and the password policy in place, the “sa” account was locked out/disabled. One can easily enable it but first one needs to be able to get in with sysadmin privs. and then make the changes. Best option was to get one of the IT team members to login and post it, add additional logins, enable the “sa” account, set it’s password and then make whatever changes needed for the logins/their access levels etc. The user did that and was on his way to managing the newly created instance. One can enable a disabled/locked SQL Server account through the GUI or by using the “ALTER LOGIN” statement as we had shown in a previous post here.