Recently, one of my colleagues asked me an age-old question – “How to ensure that I am able to access my SQL Server instance using my Windows login even if I forget the ‘sa’ login?” They had a long un-used instance of SQL Server for which they had forgotten the “sa” password. They had attempted to use Windows authentication, but had failed to login.

This incident was fresh in my memory when I re-installed the SQL Server instance on my test environment at home. Hence, it was the perfect time to write this post.

Proactive step

The ideal risk management strategy is to mitigate all them before they arise. Keeping this in mind, a simple step from the IT team (or whoever is in-charge of installing the SQL Server instance) can help ensure that at least one user always has access to a SQL Server instance at any given point in time.

As we all know, SQL Server supports two login mechanisms:

• Windows Authentication (default)
• Mixed Mode – SQL Server Authentication & Windows Authentication

In the interest of security, SQL Server has been designed such that Windows Authentication can never be turned OFF.

In SQL Server 2005, all members of the BUILTINAdministrators group were automatically members of the sysadmin fixed server role, and were therefore SQL Server administrators. Starting SQL Server 2008 however, this was no longer the case. In the “Database Configuration” screen of the SQL Server installer, one now needs to explicitly specify windows logins (or groups) that need to be added as sysadmins on the new SQL Server instance being installed.

Database Configuration Screen

When running the SQL Server 2012 installer, this information is requested in the “Database Configuration” screen (step #13):

As shown in the screen-shot above, you can do one of the following:

1. Add Current User – adds the currently logged-in user (under whose context the installer is running)
2. Add – Choose the domain or local user to add
3. Remove – Choose to remove any users added accidentally

The screen-shot below shows that when installing my SQL Server 2012 instance, I choose Mixed mode authentication and added myself as an admin:

Typically what happens is that when IT teams install SQL Server instances for development and QA teams, they set Mixed Mode authentication to ON, but forget to add one windows or local user as a SQL sysadmin. If the team now forgets the “sa” password, they are left with a SQL Server instance to which the applications can connect using other roles and users but cannot administer it anymore.

Moral of the story: The SQL Server installation checklists being followed by your team must have a check to ensure that at least one login (either that of the configuration manager on the team, or a local login) be registered as an administrator on the SQL Server.

Word of caution: Do note that whoever has been added to the sysadmin fixed server role has unrestricted access to the Database Engine.

Reactive step

While managing a risk in a proactive way is the ideal way, one might always find exceptions. As in the case of my colleague, it is possible to be left with an instance that one cannot administer because of not having the correct login credentials.

In such cases, I refer to an excellent blog post by my friend, Chintak Chhapia (blog) on How to access a newly installed SQL Server 2008 R2 instance if you do not know the login and password. Thanks to his recommendations, the colleague of mine had access to their server in almost no time!

Until we meet next time,

Be courteous. Drive responsibly.