Our SQL admin / DBA guy suddenly quit with no notice. We use SQL 2012. We discovered he documented nothing. So now we don't know the sa password. No other SQL users have the sysadmin role so we have no way of signing in as admin. I went directly to the server where our databases are and tried to login using Windows Authentication but got this error:
TITLE: Connect to Server
Cannot connect to 198.154.251.167.
ADDITIONAL INFORMATION:
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476
BUTTONS:
OK
I am afraid we will have to reinstall SQL but it's not something I usually do as I'm a developer. Is there any other way to find the sa password or at least be able to change it? Thanks in advance.
isn;t there a route if you are logged into the server as part of the Administrators group that you can then connect to SQL (i.e. without having to start SQL in single user mode etc.)
I've never done it, so its only something I vaguely remember reading, so I'm sorry but it may only be worth exactly what you paid for it!!
Did he get his permissions from an AD role? If so, add someone else to the DBA role to allow them to get sysadmin authority.
You need to configure SQL Server in Mixed Authentication Mode to resolve this issue
- Open SQL Server Management Studio
- Right-click the server, and then click Properties.
- Under Server authentication, click on Security, click the SQL Server and Windows Authentication mode option button, and then click OK.
- In the SQL Server Management Studio dialog box, click OK to restart SQL Server.
If this solution doesn’t help then your only option is to recover SQL Server SA password. For this you can take the help of SQL Server Password Reset Tool which allows you to recover lost SQL Server SA and User password