Hi Guys ,
A few days ago someone at client’s site removed the builtin administrator from SQL Server as it was one of the risks mentioned in the agreement .Everything was fine and no one realized what mistake has been done.
Things were fine till the time it was needed to login to SQL Server .No one saved SA password either and een though the SA login was enabled , we were not able to use it :)….awesome …
They were about to uninstall and reinstall SQL server when we finally could resolve the issue by following the steps below .It took us a lot of time but one of the options worked.Below are the steps to reproduce this issue and the solution .Make sure you have SA password saved before attempting this .
Repro of issue :
1) Delete the Builtin/administrator account .
2) Try to connect to SQL Server through sqlcmd or Management studio .You will get the error 18456 Level 14 State 1.
3) Assume that i have forgotten the SA password as well.
Solution:
1) Login to the server using administrator account .
2) Stop SQL Server service and start it with -m switch(single user mode) .
2) Type sqlcmd -E and hit enter .If its named instance then sqlcmd -S <Server\instance> -E and hit enter.
4) You will see > sign
5) Commands you need to use
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE= [master], DEFAULT_LANGUAGE=[us_english] go
6) Give sysadmin role to the login we just created
EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin' GO
7) You are done.Exit out of it .8) restart you SQL Server service without -m parameter.
You are done .Login again to SQL Server and reset the SA password .Save the SA password for the rainy day .
Happy learning !
Regards
Abhay Chaudhary
Like us on FaceBook | Follow us on Twitter
Follow me on FaceBook | Join the fastest growing SQL Server group on FaceBook
There’s a reason BUILTIN\Administrator was removed from the sysadmin role: It’s a security hole.
From Books Online topic “SQL Server 2008 Security Changes”
Beginning in SQL Server 2005, significant changes were implemented to make sure that SQL Server is more secure than earlier versions. Changes included a “secure by design, secure by default, and secure in deployment” strategy designed to help protect the server instance and its databases from security attacks.
SQL Server 2008 introduces additional security improvements. SQL Server 2008 also takes advantage of changes in the latest operating systems from Microsoft, such as the User Account Control (UAC) found in Windows Vista and Windows Server 2008. The following improvements in SQL Server 2008 decrease the surface and attack area for SQL Server and its databases by instituting a policy of “Least Privileged” and increase the separation between the Windows Administrators and the SQL Server administrators:
•By default, the local Windows Group BUILTIN\Administrator is no longer included in the SQL Server sysadmin fixed server role on new SQL Server 2008 installations.
Important:
If your processes or code depend on Windows BUILTIN\Administrator local group access, you must explicitly grant permission to log on to SQL Server. Accounts are no longer automatically granted access to SQL Server because of their membership in the Windows Administrator group. Failure to include at least one user in the sysadmin role will lock you out of the instance of SQL Server. For more information, see Database Engine Configuration – Account Provisioning and Analysis Services Configuration – Account Provisioning.
When provisioning a database instance, there are a number of things I’ll do:
Have an AD Group created with the name [server_name]_[instance_name]_[sysadmins]. The group should live in an OU administered by a small number of trusted people. If, for someon reason, an AD Group is not an option, I’ll create a group on the local server.
Add the group (AD or local) to the [sysadmins] Server Role on the instance.
Doing this makes the sa login (and password) irrelevant. Ideally, the sa login should be disabled anyway. It also means that an OU administrator or a local machine administrator can grant sysadmin-level access to the instance just by adding a Windows account to the group.
It seems like the application you are installing needs to be both a local machine administrator and a sysadmin on the database instance. This seems like it is not following the principle of least privilege. With this level of permissioning, the application could:
Install additional software on the server
Remove installed software on the server
Modify configuration settings
Drop and create databases, even those that have nothing to do with the application, but are hosted on the same instance
Marc ,
Thanks a lot .The scope of this blog was to help people come out of a situation where they are not able to log in to the instance .I have had many instances where people fall in this trap .I should have mentioned the BOL part that you have referred to in SQL 2008 BOL ..
Kind Regards
abhay