Monday, March 7, 2016

How do I get access to SQL Server instance when no other access is possible

Recently I had a situation where no one knows any level of credentials to SQL a Server instance. The instance I tried was a SQL Server 2008. By default SQL Server 2008 does not provide admin access to built in Windows local admin group.

I was able to get access to it by following below steps;

  1. Stop SQL Server service using SQL Server configuration Manager.
  2. Right click on SQL Server service and get properties.
  3. In Log On tab, use This account option to provide a windows domain account credentials which you already know. I my case I provided my credentials for the specific domain.
  4. Use Advanced tab to add “-m;” startup parameter to the beginning of the parameter list. The –m startup parameter is use to start SQL Server service in single user mode.
  5. Click on Apply and then Ok.
  6. Start SQL Server service.
  7. Open CMD shell in administrator mode and type the following sqlcmd statement; This will test the access to the server and if it successful, it returns the SQL Server name. This is just a verification.
    1. sqlcmd -E -S <server name> -q "select @@servername"
  8. Use the below sqlcmd statement to create a new user called “recovery”.
    1. CREATE LOGIN recovery WITH PASSWORD = '1qaz2wsx@'
  9. Use the below sqlcmd statement to grant admin privilege to the user we just created.
    1. SP_ADDSRVROLEMEMBER 'recovery',SYSADMIN
  10. Stop the SQL Server service using Configuration Manager.
  11. Get SQL Server service properties and remove the startup parameter “-m” and click Ok.
  12. Start the SQL Server service.
  13. Open SSMS and try to connect to the server using the user name and password we created in above steps.

Hope this helps. Cheers!

Reference:

http://blogs.technet.com/b/sqlman/archive/2011/06/14/tips-amp-tricks-you-have-lost-access-to-sql-server-now-what.aspx

1 comment:

  1. Have you tried to use PSExec to get access? I can't always shutdown the server to try to get access. If the system user has SA access (and I think that was default until SQL Server 2012) then you can use it to gain access. And you need to be a local admin on the box.

    You can get PSExec here https://technet.microsoft.com/en-us/sysinternals/bb842062.aspx

    This is the command you would use, you will need to update the path to ssms.exe - PsExec -s -i "D:\MSSQL2K8 (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

    Thanks!

    David

    This has worked for me about 75% of the time.

    ReplyDelete

Transitioning of SQLOS to SQLPAL

​ The SQLOS​ Prior to SQL Server 2005, SQL Server runs as a user mode process like any other Windows application and it depends on...